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