1 PACKAGE BODY CHV_CUM_PERIODS_S2 as
2 /* $Header: CHVCUMPB.pls 120.0.12000000.2 2007/04/28 09:01:28 vdurbhak ship $ */
3
4 /*========================= CHV_CUM_PERIODS =================================*/
5
6 /*=============================================================================
7
8 PROCEDURE NAME: get_cum_info()
9
10 =============================================================================*/
11 PROCEDURE get_cum_info (x_organization_id IN NUMBER,
12 x_vendor_id IN NUMBER,
13 x_vendor_site_id IN NUMBER,
14 x_item_id IN NUMBER,
15 x_horizon_start_date IN DATE,
16 x_horizon_end_date IN DATE,
17 x_purchasing_unit_of_measure IN VARCHAR2,
18 x_primary_unit_of_measure IN VARCHAR2,
19 x_last_receipt_transaction_id IN OUT NOCOPY NUMBER,
20 x_cum_quantity_received IN OUT NOCOPY NUMBER,
21 x_cum_quantity_received_prim IN OUT NOCOPY NUMBER,
22 x_cum_period_end_date IN OUT NOCOPY DATE) IS
23
24
25
26 x_progress VARCHAR2(3) := NULL;
27 x_enable_cum_flag VARCHAR2(1) := NULL;
28 x_cum_period_id NUMBER := 0;
29 x_cum_period_start_date DATE;
30 x_rtv_update_cum_flag VARCHAR2(1) := '';
31 x_number_records_cpi NUMBER := 0;
32
33 x_user_id NUMBER;
34 x_login_id NUMBER;
35 x_max_trans_date DATE;
36
37 --Bug5674055 commented the existing code and implemented the same using cursors.
38 cursor c_trxn_date is
39 SELECT /*+ FIRST_ROWS */ transaction_date
40 FROM rcv_transactions rct2,
41 rcv_shipment_lines rsl2
42 WHERE rct2.transaction_type = 'RECEIVE'
43 AND rct2.transaction_date between
44 x_cum_period_start_date - 1
45 and
46 nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
47 AND rsl2.item_id = x_item_id
48 AND rct2.vendor_id = x_vendor_id
49 AND rct2.vendor_site_id = x_vendor_site_id
50 AND rct2.organization_id = x_organization_id
51 AND rct2.shipment_line_id = rsl2.shipment_line_id
52 ORDER BY transaction_date desc;
53
54 CURSOR c_txn_id IS
55 SELECT transaction_id
56 FROM rcv_transactions rct,
57 rcv_shipment_lines rsl,
58 po_headers poh
59 WHERE transaction_date = x_max_trans_date
60 AND rct.transaction_type = 'RECEIVE'
61 AND rct.transaction_date between x_cum_period_start_date - 1
62 and nvl(x_cum_period_end_date,rct.transaction_date+1) + 1
63 AND rsl.item_id = x_item_id
64 AND rct.vendor_id = x_vendor_id
65 AND poh.vendor_site_id = x_vendor_site_id
66 AND rct.organization_id = x_organization_id
67 AND poh.po_header_id = rct.po_header_id
68 AND rct.shipment_line_id = rsl.shipment_line_id
69 AND EXISTS (select '1'
70 from po_asl_attributes_val_v paa,
71 po_asl_documents pad
72 WHERE paa.vendor_id = x_vendor_id
73 AND paa.vendor_site_id = x_vendor_site_id
74 AND paa.item_id = x_item_id
75 AND paa.using_organization_id =
76 (SELECT MAX(paa2.using_organization_id)
77 FROM po_asl_attributes_val_v paa2
78 WHERE decode(paa2.using_organization_id, -1,
79 x_organization_id,
80 paa2.using_organization_id) =
81 x_organization_id
82 AND paa2.vendor_id = x_vendor_id
83 AND paa2.vendor_site_id = x_vendor_site_id
84 AND paa2.item_id = x_item_id)
85 AND paa.asl_id = pad.asl_id
86 AND pad.document_header_id = poh.po_header_id)
87 ORDER BY transaction_id DESC;
88
89
90 BEGIN
91
92 --Initialize quantities to 0.
93 x_cum_quantity_received := 0;
94 x_cum_quantity_received_prim := 0;
95
96 -- Get x_user_id and x_login_id from the global variable set.
97 x_user_id := NVL(fnd_global.user_id, 0);
98 x_login_id := NVL(fnd_global.login_id, 0);
99
100 --dbms_output.put_line('Get Cum Info: user id, login'||x_user_id||x_login_id);
101
102 -- Get the open cum period for the organization.
103 x_progress := '005';
104 /* Bug 2251090 fixed. In the where clause of the below sql, added
105 the nvl() statement for cum_period_end_date to take care of null
106 condition.
107 */
108
109 SELECT cum_period_id,
110 cum_period_start_date,
111 cum_period_end_date
112 INTO x_cum_period_id,
113 x_cum_period_start_date,
114 x_cum_period_end_date
115 FROM chv_cum_periods
116 WHERE organization_id = x_organization_id
117 AND x_horizon_start_date BETWEEN cum_period_start_date
118 AND nvl(cum_period_end_date,x_horizon_start_date+1);
119
120 --dbms_output.put_line('Get Cum Info: end date'||x_cum_period_end_date);
121 --dbms_output.put_line('Get Cum Info: start date'||x_cum_period_start_date);
122 /* Bug 2251090 fixed. Aded the nvl() condition to the below if condition
123 to take care of null condition.
124 */
125
126 IF (x_horizon_end_date < nvl(x_cum_period_end_date,x_horizon_end_date+1)) THEN
127 x_cum_period_end_date := x_horizon_end_date;
128 END IF;
129
130 x_progress := '010';
131
132 SELECT rtv_update_cum_flag
133 INTO x_rtv_update_cum_flag
134 FROM chv_org_options
135 WHERE organization_id = x_organization_id;
136
137 --dbms_output.put_line('Get Cum Info'||x_rtv_update_cum_flag);
138
139 -- Get the last receipt transaction date and receipt transaction
140 -- during the cum period.
141 -- Note: That we do not store the vendor site id in rcv_transactions
142 -- when we create the receipt. So we must join back to po headers
143 -- to verify that we are pointing to the correct vendor site.
144 x_progress := '020';
145
146 BEGIN
147 /* Bug 2251090 fixed. In the where clause of the below sql, added
148 the nvl() statement for x_cum_period_end_date to take care of null
149 condition.
150 */
151
152 -- Bug 3656241(forward fix of 3549677)
153 -- Following SQL split into 2 sqls for performance improvement.
154 -- Also driving off of po_headers by making poh.vendor_id = x_vendor_id
155
156
157 /* Bug 5674055
158 SELECT max(transaction_date)
159 INTO x_max_trans_date
160 FROM rcv_transactions rct2,
161 po_headers poh2,
162 rcv_shipment_lines rsl2
163 WHERE rct2.transaction_type = 'RECEIVE'
164 AND rct2.transaction_date between
165 x_cum_period_start_date - 1
166 and
167 nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
168 AND rsl2.item_id = x_item_id
169 AND poh2.vendor_id = x_vendor_id
170 AND poh2.vendor_site_id = x_vendor_site_id
171 AND rct2.organization_id = x_organization_id
172 AND poh2.po_header_id = rct2.po_header_id
173 AND rct2.shipment_line_id = rsl2.shipment_line_id;*/
174
175 open c_trxn_date;
176 fetch c_trxn_date into x_max_trans_date;
177 close c_trxn_date;
178
179 -- Bug 5674055 commented the following code.
180 /* SELECT max(transaction_id)
181 INTO x_last_receipt_transaction_id
182 FROM rcv_transactions rct,
183 rcv_shipment_lines rsl,
184 po_headers poh
185 WHERE transaction_date = x_max_trans_date
186 (
187 SELECT max(transaction_date)
188 FROM rcv_transactions rct2,
189 po_headers poh2,
190 rcv_shipment_lines rsl2
191 WHERE rct2.transaction_type = 'RECEIVE'
192 AND rct2.transaction_date between
193 x_cum_period_start_date - 1
194 and
195 nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
196 AND rsl2.item_id = x_item_id
197 AND rct2.vendor_id = x_vendor_id
198 AND poh2.vendor_site_id = x_vendor_site_id
199 AND rct2.organization_id = x_organization_id
200 AND poh2.po_header_id = rct2.po_header_id
201 AND rct2.shipment_line_id = rsl2.shipment_line_id)
202 AND rct.transaction_type = 'RECEIVE'
203 AND rct.transaction_date between x_cum_period_start_date - 1
204 and nvl(x_cum_period_end_date,rct.transaction_date+1) + 1
205 AND rsl.item_id = x_item_id
206 AND rct.vendor_id = x_vendor_id
207 AND poh.vendor_site_id = x_vendor_site_id
208 AND rct.organization_id = x_organization_id
209 AND poh.po_header_id = rct.po_header_id
210 AND rct.shipment_line_id = rsl.shipment_line_id
211
212 Bug#3067808 Added the following retrictive condition to the SQL so that
213 ** the correct value for transaction_id is retrived from receiving tables
214 ** only for which the ASL entries exists.
215 */
216 /* AND EXISTS (select '1'
217 from po_asl_attributes_val_v paa,
218 po_asl_documents pad
219 WHERE paa.vendor_id = x_vendor_id
220 AND paa.vendor_site_id = x_vendor_site_id
221 AND paa.item_id = x_item_id
222 AND paa.using_organization_id =
223 (SELECT MAX(paa2.using_organization_id)
224 FROM po_asl_attributes_val_v paa2
225 WHERE decode(paa2.using_organization_id, -1,
226 x_organization_id,
227 paa2.using_organization_id) =
228 x_organization_id
229 AND paa2.vendor_id = x_vendor_id
230 AND paa2.vendor_site_id = x_vendor_site_id
231 AND paa2.item_id = x_item_id)
232 AND paa.asl_id = pad.asl_id
233 AND pad.document_header_id = poh.po_header_id);*/
234 /* Bug#3067808 END */
235
236 open c_txn_id;
237 fetch c_txn_id into x_last_receipt_transaction_id;
238 close c_txn_id;
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN null;
242 WHEN OTHERS THEN raise;
243
244 END;
245
246 --dbms_output.put_line('Get Cum Info: trx id'||x_last_receipt_transaction_id);
247
248 -- Get the CUM quantity received for the item.
249 x_progress := '030';
250
251 chv_cum_periods_s1.get_cum_qty_received(x_vendor_id,
252 x_vendor_site_id,
253 x_item_id,
254 x_organization_id,
255 x_rtv_update_cum_flag,
256 x_cum_period_start_date,
257 x_cum_period_end_date,
258 x_purchasing_unit_of_measure,
259 x_cum_quantity_received_prim,
260 x_cum_quantity_received);
261
262 --dbms_output.put_line('Get Cum Info: cum qty'||x_cum_quantity_received);
263
264 -- If there are no records in chv_cum_period_items, then we must
265 -- insert a record. The first time we build a schedule for an item
266 -- we must insert a record into chv_cum_period_items.
267 x_progress := '040';
268
269 SELECT count(*)
270 INTO x_number_records_cpi
271 FROM chv_cum_period_items cpi
272 WHERE cpi.cum_period_id = x_cum_period_id
273 AND cpi.vendor_id = x_vendor_id
274 AND cpi.vendor_site_id = x_vendor_site_id
275 AND cpi.organization_id = x_organization_id
276 AND cpi.item_id = x_item_id;
277
278 IF (x_number_records_cpi > 0) THEN
279
280 --dbms_output.put_line('Get Cum Info: record exists in cum period items');
281 null;
282 ELSE
283 x_progress := '050';
284
285 --dbms_output.put_line('Get Cum Info: insert record into cum period items');
286 --dbms_output.put_line('Get Cum Info: period'||x_cum_period_id);
287 --dbms_output.put_line('Get Cum info: vendor'||x_vendor_id);
288 --dbms_output.put_line('Get Cum info: site'||x_vendor_site_id);
289 --dbms_output.put_line('Get Cum info: item'||x_item_id);
290 --dbms_output.put_line('Get Cum info: purchasing'||x_purchasing_unit_of_measure);
291 --dbms_output.put_line('Get Cum info: primary'||x_primary_unit_of_measure);
292
293 INSERT INTO chv_cum_period_items (cum_period_item_id,
294 cum_period_id,
295 organization_id,
296 vendor_id,
297 vendor_site_id,
298 item_id,
299 last_update_date,
300 last_updated_by,
301 creation_date,
302 created_by,
303 last_update_login)
304 VALUES (chv_cum_period_items_s.NEXTVAL,
305 x_cum_period_id,
306 x_organization_id,
307 x_vendor_id,
308 x_vendor_site_id,
309 x_item_id,
310 SYSDATE,
311 x_user_id,
312 SYSDATE,
313 x_user_id,
314 x_login_id);
315
316 END IF;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 po_message_s.sql_error('get_cum_info', x_progress, sqlcode);
321 RAISE;
322
323 END get_cum_info;
324
325 END CHV_CUM_PERIODS_S2;
[Home] [Help]