[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_MISCELLANEOUS
Source
1 PACKAGE BODY MSC_CL_MISCELLANEOUS AS -- body
2 /* $Header: MSCCLGAB.pls 120.0 2005/05/25 17:59:40 appldev noship $ */
3 TYPE number_arr IS TABLE OF NUMBER;
4 TYPE date_arr IS TABLE OF DATE;
5
6
7 t_inv_item_id number_arr;
8 t_sr_inv_item_id number_arr;
9 t_org_id number_arr;
10 t_sr_org_id number_arr;
11 t_supplier_id number_arr;
12 t_sr_supplier_id number_arr;
13 t_supp_site_id number_arr;
14 t_sr_supp_site_id number_arr;
15 t_sr_rule_id number_arr;
16 t_sr_level number_arr;
17 t_assignment_set_id number_arr;
18
19 t_start_date date_arr;
20
21 t_ins_inv_item_id number_arr;
22 t_ins_org_id number_arr;
23 t_ins_trx_date date_arr;
24 t_trx_qty number_arr;
25 t_trx_type number_arr;
26 t_ins_sr_org_id number_arr;
27 t_ins_supp_id number_arr;
28 t_ins_supp_site_id number_arr;
29
30 lv_pbs number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
31 c_count number := 0;
32 G_START_SH number := nvl(TO_NUMBER(FND_PROFILE.VALUE('MSC_START_SOURCING_HISTORY')),-1);
33
34 PROCEDURE load_sourcing_history
35 ( arg_instance_id IN NUMBER,
36 arg_refresh_number IN NUMBER,
37 arg_current_date IN DATE,
38 arg_current_user IN NUMBER,
39 arg_request_id IN NUMBER )
40 IS
41
42 lv_assignment_set_id NUMBER;
43 lv_task_start_time DATE;
44
45 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
46 cur_c1 CurTyp;
47 LV_SQL_STMT_NEW varchar2(4000);
48
49
50 lv_sql_stmt varchar2(2000);
51 lv_sql_stmt_app varchar2(200);
52 lv_starting_date date;
53 BEGIN
54
55 v_instance_id := arg_instance_id;
56 v_refresh_number := arg_refresh_number;
57 v_current_date := arg_current_date;
58 v_current_user := arg_current_user;
62 NULL, ' ',
59 v_request_id := arg_request_id;
60
61 SELECT DECODE( M2A_DBLINK,
63 '@'||M2A_DBLINK)
64 INTO v_dblink
65 FROM MSC_APPS_INSTANCES
66 WHERE INSTANCE_ID= arg_instance_id;
67
68 lv_task_start_time:= SYSDATE;
69
70 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
71 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING_HISTORY');
72 LOG_MESSAGE(FND_MESSAGE.GET);
73
74 if (G_START_SH >= 0) then
75 --lv_starting_date := sysdate - G_START_SH;
76 lv_sql_stmt_app := ' and trunc(x.transaction_date) >= trunc(add_months(sysdate,-('||G_START_SH||')))';
77 else
78 lv_sql_stmt_app := ' ';
79 end if;
80
81 lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
82 ||' INVENTORY_ITEM_ID, '
83 ||' ORGANIZATION_ID, '
84 ||' TRANSACTION_DATE, '
85 ||' TRANSACTION_QTY, '
86 ||' TRANSACTION_TYPE) '
87 ||' SELECT inventory_item_id, organization_id, '
88 ||' transaction_date, transaction_qty , 1 '
89 ||' FROM MRP_AP_INNER_ORG_TRXS_V'||v_dblink||' x'
90 ||' where x.inventory_item_id is not null '
91 || lv_sql_stmt_app ;
92
93 execute immediate lv_sql_stmt_new;
94 commit;
95
96 lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
97 ||' INVENTORY_ITEM_ID, '
98 ||' ORGANIZATION_ID, '
99 ||' SOURCE_ORG_ID, '
100 ||' TRANSACTION_DATE, '
101 ||' TRANSACTION_QTY, '
102 ||' TRANSACTION_TYPE) '
103 ||' SELECT inventory_item_id, organization_id, '
104 ||' source_org_id, transaction_date, transaction_qty ,2 '
105 ||' FROM MRP_AP_INTER_ORG_TRXS_V'||v_dblink||' x'
106 ||' where x.inventory_item_id is not null '
107 || lv_sql_stmt_app ;
108
109 execute immediate lv_sql_stmt_new;
110 commit;
111
112 lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
113 ||' INVENTORY_ITEM_ID, '
114 ||' SUPPLIER_ID, '
115 ||' SUPPLIER_SITE_ID, '
116 ||' TRANSACTION_DATE, '
117 ||' TRANSACTION_QTY, '
118 ||' TRANSACTION_TYPE) '
119 ||' SELECT inventory_item_id, SUPPLIER_ID,nvl(SUPPLIER_SITE_ID,-1), '
120 ||' transaction_date, transaction_qty ,3 '
121 ||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x'
122 ||' where x.inventory_item_id is not null '
123 || lv_sql_stmt_app ;
124
125 execute immediate lv_sql_stmt_new;
126 commit;
127
128 load_sourcing_history_sub1( lv_assignment_set_id);
129
130 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
131 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
132 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
133 LOG_MESSAGE(FND_MESSAGE.GET);
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 RAISE;
138
139 END load_sourcing_history;
140
141 PROCEDURE load_po_receipts
142 ( arg_instance_id IN NUMBER,
143 arg_org_sub_str IN VARCHAR2:= NULL,
144 arg_refresh_number IN NUMBER,
145 arg_current_date IN DATE,
146 arg_current_user IN NUMBER,
147 arg_request_id IN NUMBER )
148 IS
149
150 lv_assignment_set_id NUMBER;
151 lv_task_start_time DATE;
152
153 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
154 cur_c1 CurTyp;
155 LV_SQL_STMT_NEW varchar2(4000);
156
157
158 lv_sql_stmt varchar2(2000);
159 lv_sql_stmt_app varchar2(200);
160 lv_starting_date date;
161 BEGIN
162
163 v_instance_id := arg_instance_id;
164 v_refresh_number := arg_refresh_number;
165 v_current_date := arg_current_date;
166 v_current_user := arg_current_user;
167 v_request_id := arg_request_id;
168
169 SELECT DECODE( M2A_DBLINK,
170 NULL, ' ',
171 '@'||M2A_DBLINK)
172 INTO v_dblink
173 FROM MSC_APPS_INSTANCES
174 WHERE INSTANCE_ID= arg_instance_id;
175
176 lv_task_start_time:= SYSDATE;
177
178 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
179 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PO_RECEIPTS');
180 LOG_MESSAGE(FND_MESSAGE.GET);
181
182 if (G_START_SH >= 0) then
183 --lv_starting_date := sysdate - G_START_SH;
184 lv_sql_stmt_app := ' and trunc(x.transaction_date) >= trunc(add_months(sysdate,-('||G_START_SH||')))';
185 else
186 lv_sql_stmt_app := ' ';
187 end if;
188 log_message(lv_sql_stmt_app);
189 lv_sql_stmt_new := ' insert into MSC_PO_RECEIPTS( '
190 ||' RECEIPT_ID, '
191 ||' SR_INSTANCE_ID, '
192 ||' INVENTORY_ITEM_ID, '
193 ||' ORGANIZATION_ID, '
194 ||' SUPPLIER_ID, '
195 ||' SUPPLIER_SITE_ID, '
196 ||' TRANSACTION_DATE, '
197 ||' TRANSACTION_QTY, '
198 ||' LAST_UPDATE_DATE, '
199 ||' LAST_UPDATED_BY, '
200 ||' CREATION_DATE, '
201 ||' CREATED_BY) '
202 ||' SELECT x.receipt_id,:v_instance_id,item.inventory_item_id,x.organization_id, TP.TP_ID,nvl(TPS.TP_SITE_ID,-1), '
203 ||' x.transaction_date, x.transaction_qty ,:v_current_date, :v_current_user, '
204 ||' :v_current_date, :v_current_user'
208 ||' x.inventory_item_id = item.sr_inventory_item_id and '
205 ||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x, MSC_TP_ID_LID tp, MSC_ITEM_ID_LID item, '
206 ||' MSC_TP_SITE_ID_LID tps '
207 ||' where x.inventory_item_id is not null and '
209 ||' item.sr_instance_id= :v_instance_id and '
210 ||' x.supplier_id = tp.sr_tp_id and '
211 ||' tp.sr_instance_id= :v_instance_id and '
212 ||' x.supplier_site_id(+) = tps.sr_tp_site_id and '
213 ||' tps.partner_type = 1 and '
214 ||' tp.partner_type = 1 and '
215 ||' tps.sr_instance_id= :v_instance_id and '
216 ||' x.organization_id '|| arg_org_sub_str
217 || lv_sql_stmt_app ;
218 log_message(lv_sql_stmt_new);
219 EXECUTE IMMEDIATE lv_sql_stmt_new
220 USING v_instance_id, v_current_date, v_current_user,v_current_date, v_current_user, v_instance_id, v_instance_id, v_instance_id;
221
222 commit;
223
224 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
225 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
226 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
227 LOG_MESSAGE(FND_MESSAGE.GET);
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 RAISE;
232
233 END Load_PO_Receipts;
234
235 PROCEDURE load_sourcing_history_sub1
236 ( arg_assignment_set_id IN NUMBER )
237 IS
238 lv_source_type NUMBER;
239 lv_sr_inventory_item_id NUMBER;
240 lv_inventory_item_id NUMBER;
241 lv_organization_id NUMBER;
242 lv_source_org_id NUMBER;
243 lv_sr_supplier_id NUMBER;
244 lv_supplier_id NUMBER;
245 lv_sr_supplier_site_id NUMBER;
246 lv_supplier_site_id NUMBER;
247 lv_start_date DATE;
248 lv_end_date DATE;
249 lv_historical_allocation NUMBER;
250 lv_sourcing_rule_id NUMBER;
251 lv_sourcing_level NUMBER;
252
253 lv_assignment_set_id number;
254 lv_p_assignment_set_id number;
255
256 lv_p_source_type NUMBER;
257 lv_p_inventory_item_id NUMBER;
258 lv_p_organization_id NUMBER;
259 lv_p_source_org_id NUMBER;
260 lv_p_supplier_id NUMBER;
261 lv_p_supplier_site_id NUMBER;
262 lv_p_start_date DATE;
263 lv_p_sourcing_level NUMBER;
264
265 lv_total_alloc_qty NUMBER;
266
267 start_date_new DATE;
268 start_date_offset NUMBER;
269
270 CURSOR cur_item_sourcing_history IS
271 SELECT
272 iil.sr_inventory_item_id,
273 iil.inventory_item_id,
274 sr_view.organization_id,
275 sr_view.source_org_id,
276 til.sr_tp_id,
277 til.tp_id,
278 nvl(tsil.sr_tp_site_id,-1) sr_tp_site_id,
279 tsil.tp_site_id,
280 sr_view.effective_date,
281 sr_view.sourcing_rule_id,
282 sr_view.sourcing_level,
283 msa.assignment_set_id
284 FROM msc_assignment_sets msa,
285 MSC_BOD_SOURCING_RULES_V sr_view,
286 MSC_ITEM_ID_LID iil,
287 MSC_TP_ID_LID til,
288 MSC_TP_SITE_ID_LID tsil
289 WHERE iil.inventory_item_id= sr_view.inventory_item_id
290 AND iil.sr_instance_id= sr_view.sr_instance_id
291 AND til.tp_id(+)= sr_view.supplier_id
292 AND til.sr_instance_id(+)= sr_view.sr_instance_id
293 AND til.partner_type(+)= 1
294 AND tsil.tp_site_id(+)= sr_view.supplier_site_id
295 AND tsil.sr_instance_id(+)= sr_view.sr_instance_id
296 AND tsil.partner_type(+)= 1
297 AND sr_view.effective_date <= v_current_date
298 AND NVL(sr_view.disable_date, TRUNC(v_current_date) + 1)
299 > TRUNC(v_current_date)
300 AND sr_view.assignment_set_id= msa.assignment_set_id
301 and msa.sr_instance_id = v_instance_id
302 ORDER BY
303 msa.assignment_set_id,
304 sr_view.organization_id,
305 sr_view.inventory_item_id,
306 sr_view.sourcing_level ASC;
307
308 BEGIN
309
310 lv_end_date := v_current_date;
311
312 /* Added this new Profile opt MSC_SH_START_DATE_OFFSET(in months)
313 For enfore sourcing splits
314 This profile will control the start date of Sourcing History calculation
315 for those transactions that are not calculated anytime before in the Destination*/
316
317 SELECT TO_NUMBER(FND_PROFILE.VALUE('MSC_START_SOURCING_HISTORY'))
318 into start_date_offset
319 from dual;
320
321 IF (start_date_offset IS NOT NULL) THEN
322 start_date_new := add_months(v_current_date,-start_date_offset);
323 END IF;
324
325 BEGIN
326 OPEN cur_item_sourcing_history;
327 fetch cur_item_sourcing_history
328 bulk collect into
329 t_sr_inv_item_id,
330 t_inv_item_id,
331 t_org_id,
332 t_sr_org_id,
333 t_sr_supplier_id,
334 t_supplier_id,
335 t_sr_supp_site_id,
336 t_supp_site_id,
337 t_start_date,
338 t_sr_rule_id,
339 t_sr_level,
340 t_assignment_set_id;
341
342 IF t_inv_item_id.COUNT > 0 then
343
344 FOR i in 1..t_inv_item_id.COUNT LOOP
345
346 IF t_inv_item_id(i) = lv_p_inventory_item_id AND
347 t_org_id(i) = lv_p_organization_id AND
351 ---GOTO fetch_item_sourcing_history;
348 t_assignment_set_id(i) = lv_p_assignment_set_id AND
349 t_sr_level(i) > lv_p_sourcing_level THEN
350 null;
352 ELSE
353
354 lv_p_sourcing_level := t_sr_level(i);
355 lv_p_inventory_item_id:= t_inv_item_id(i);
356 lv_p_organization_id := t_org_id(i);
357 lv_p_assignment_set_id := t_assignment_set_id(i);
358 lv_start_date := t_start_date(i);
359
360 /* if the profile option is set and the effectivity date of the Sourcing rule is
361 greater than the new start date (based on offset days as calculated above) ,
362 then the start date will be the effectivity date.If the parameter Purge SH has been set in ODS
363 then the table msc_sourcing_history would have been deleted and relcalculation will be
364 done from this new date */
365
366 IF (start_date_offset IS NOT NULL) THEN
367 IF start_date_new > lv_start_date THEN
368 lv_start_date := start_date_new - 1;
369 ELSE
370 lv_start_date := lv_start_date - 1;
371 /* added this code since the transactions on effectivity date of Sourcing rule
372 were not getting calculated for Bug: 2069633 */
373 END IF;
374 END IF;
375
376 get_sourcing_history(
377 t_sr_org_id(i),
378 t_sr_supplier_id(i),
379 t_supplier_id(i),
380 t_sr_supp_site_id(i),
381 t_supp_site_id(i),
382 t_sr_inv_item_id(i),
383 t_inv_item_id(i),
384 t_org_id(i),
385 t_sr_rule_id(i),
386 lv_start_date,
387 v_current_date);
388
389 END IF;
390 END LOOP;
391
392 END IF;
393
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 log_message('Error within load_sourcing_history_sub1: '||SQLERRM);
398 IF cur_item_sourcing_history%ISOPEN THEN
399 CLOSE cur_item_sourcing_history;
400 END IF;
401 RAISE;
402 END;
403 EXCEPTION
404 when others then
405 log_message('Error in load_sourcing_history_sub1: '||SQLERRM);
406
407 END load_sourcing_history_sub1;
408
409
410
411 PROCEDURE get_sourcing_history
412 ( arg_source_org IN NUMBER,
413 arg_sr_supplier_id IN NUMBER,
414 arg_supplier_id IN NUMBER,
415 arg_sr_supplier_site_id IN NUMBER,
416 arg_supplier_site_id IN NUMBER,
417 arg_sr_item_id IN NUMBER,
418 arg_item_id IN NUMBER,
419 arg_org_id IN NUMBER,
420 arg_sourcing_rule_id IN NUMBER,
421 arg_start_date IN DATE,
422 arg_end_date IN DATE )
423 IS
424
425 --PRAGMA AUTONOMOUS_TRANSACTION;
426
427 L_ST_INNER_ORG CONSTANT NUMBER:= 1;
428 L_ST_INTER_ORG CONSTANT NUMBER:= 2;
429 L_ST_PO_SUPPLIER CONSTANT NUMBER:= 3;
430
431 lv_rowid UROWID;
432 lv_start_date DATE;
433 lv_historical_allocation NUMBER;
434 lv_record_exists NUMBER;
435 lv_total_alloc_qty NUMBER;
436 lv_source_type NUMBER;
437
438 BEGIN
439
440 BEGIN
441 SELECT msh.ROWID,
442 msh.last_calculated_date,
443 msh.historical_allocation,
444 SYS_YES
445 INTO lv_rowid,
446 lv_start_date,
447 lv_historical_allocation,
448 lv_record_exists
449 FROM MSC_SOURCING_HISTORY msh
450 WHERE msh.inventory_item_id= arg_item_id
451 AND msh.organization_id= arg_org_id
452 AND msh.sr_instance_id= v_instance_id
453 AND msh.sourcing_rule_id= arg_sourcing_rule_id
454 AND NVL( msh.source_org_id,-1)= NVL( arg_source_org,-1)
455 AND NVL( msh.supplier_id,-1)= NVL ( arg_supplier_id,-1)
456 AND NVL( msh.supplier_site_id,-1)= NVL( arg_supplier_site_id,-1);
457
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 lv_start_date := arg_start_date;
461 /* subtracting by minus 1, is added since the transactions done on the same day as the
462 effectivity date were not getting calculated */
463 --lv_start_date := lv_start_date - 1;
464 /* commented this code since the transactions on effectivity date of Sourcing rule were not getting
465 calculated for Bug: 2069633 */
466 lv_historical_allocation:= 0;
467 lv_record_exists := SYS_NO;
468 END;
469
470 IF lv_start_date = v_current_date THEN
471 RETURN;
472 END IF;
473
474 IF lv_start_date< arg_start_date THEN
475 lv_historical_allocation:= 0;
476 lv_start_date:= arg_start_date;
477 END IF;
478
479 /* These are the Debug messages
480 FND_MESSAGE.SET_NAME('MSC', 'MSC_SH_TASK_START');
481 FND_MESSAGE.SET_TOKEN('ITEM_ID:',arg_item_id);
482 LOG_MESSAGE(FND_MESSAGE.GET);
483
484 FND_MESSAGE.SET_NAME('MSC', 'MSC_SH_TASK_START');
485 FND_MESSAGE.SET_TOKEN('START_DATE:',lv_start_date);
486 LOG_MESSAGE(FND_MESSAGE.GET);
487 */
488
489 IF arg_source_org IS NULL THEN
490 lv_source_type := L_ST_PO_SUPPLIER;
491 ELSIF arg_source_org= arg_org_id THEN
492 lv_source_type := L_ST_INNER_ORG;
493 ELSE lv_source_type := L_ST_INTER_ORG;
494 END IF;
495
499 into lv_total_alloc_qty
496 IF lv_source_type= L_ST_INNER_ORG THEN
497
498 select GREATEST(NVL(SUM(transaction_qty),0),0)
500 from MSC_SOURCING_TRANSACTIONS
501 where inventory_item_id = arg_sr_item_id
502 and organization_id = arg_org_id
503 and transaction_date > lv_start_date
504 and trunc(transaction_date) <= trunc(arg_end_date)
505 and transaction_type = 1;
506
507 ELSIF lv_source_type= L_ST_INTER_ORG THEN
508
509 select GREATEST(NVL(SUM(transaction_qty),0),0)
510 into lv_total_alloc_qty
511 from MSC_SOURCING_TRANSACTIONS
512 where inventory_item_id = arg_sr_item_id
513 and organization_id = arg_org_id
514 and SOURCE_ORG_ID = arg_source_org
515 and transaction_date > lv_start_date
516 and trunc(transaction_date) <= trunc(arg_end_date)
517 and transaction_type = 2;
518
519 ELSIF lv_source_type= L_ST_PO_SUPPLIER THEN
520
521 select GREATEST(NVL(SUM(transaction_qty),0),0)
522 into lv_total_alloc_qty
523 from MSC_SOURCING_TRANSACTIONS
524 where inventory_item_id = arg_sr_item_id
525 and SUPPLIER_ID = arg_sr_supplier_id
526 and SUPPLIER_SITE_ID = arg_sr_supplier_site_id
527 and transaction_date > lv_start_date
528 and trunc(transaction_date) <= trunc(arg_end_date)
529 and transaction_type = 3;
530
531 END IF;
532
533 lv_total_alloc_qty:= lv_total_alloc_qty +
534 NVL( lv_historical_allocation,0);
535
536 IF lv_record_exists= SYS_YES THEN
537
538 UPDATE MSC_SOURCING_HISTORY
539 SET historical_allocation= lv_total_alloc_qty,
540 last_calculated_date = v_current_date,
541 LAST_UPDATED_BY = v_current_user,
542 LAST_UPDATE_DATE = v_current_date
543 WHERE rowid= lv_rowid;
544 c_count:= c_count+1;
545
546 ELSE
547
548 INSERT INTO MSC_SOURCING_HISTORY
549 ( inventory_item_id,
550 organization_id,
551 sourcing_rule_id,
552 source_org_id,
553 source_sr_instance_id,
554 supplier_id,
555 supplier_site_id,
556 historical_allocation,
557 refresh_number,
558 last_calculated_date,
559 sr_instance_id,
560 LAST_UPDATED_BY,
561 LAST_UPDATE_DATE,
562 CREATION_DATE,
563 CREATED_BY)
564 VALUES
565 ( arg_item_id,
566 arg_org_id,
567 arg_sourcing_rule_id,
568 arg_source_org,
569 v_instance_id,
570 arg_supplier_id,
571 arg_supplier_site_id,
572 lv_total_alloc_qty,
573 v_refresh_number,
574 v_current_date,
575 v_instance_id,
576 v_current_user,
577 v_current_date,
578 v_current_date,
579 v_current_user);
580
581 c_count:= c_count+1;
582 END IF;
583
584
585 IF c_count>lv_pbs THEN
586 COMMIT;
587 END IF;
588
589 END get_sourcing_history;
590
591
592 FUNCTION inner_org_trx_qty
593 ( arg_inventory_item_id IN NUMBER,
594 arg_organization_id IN NUMBER,
595 arg_start_date IN DATE,
596 arg_end_date IN DATE)
597 RETURN NUMBER
598 IS
599 lv_sql_stmt VARCHAR2(2000);
600 lv_total_alloc_qty NUMBER;
601 BEGIN
602
603 lv_sql_stmt:=
604 'SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
605 ||' FROM MRP_AP_INNER_ORG_TRXS_V'||v_dblink||' x'
606 ||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
607 ||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
608 ||' AND x.inventory_item_id = :arg_inventory_item_id'
609 ||' AND x.organization_id = :arg_organization_id';
610
611 EXECUTE IMMEDIATE lv_sql_stmt
612 INTO lv_total_alloc_qty
613 USING arg_start_date,
614 arg_end_date,
615 arg_inventory_item_id,
616 arg_organization_id;
617
618 RETURN lv_total_alloc_qty;
619
620 END inner_org_trx_qty;
621
622
623 FUNCTION inter_org_trx_qty
624 ( arg_inventory_item_id IN NUMBER,
625 arg_organization_id IN NUMBER,
626 arg_source_org_id IN NUMBER,
627 arg_start_date IN DATE,
628 arg_end_date IN DATE)
629 RETURN NUMBER
630 IS
631 lv_sql_stmt VARCHAR2(2000);
632 lv_total_alloc_qty NUMBER;
633 BEGIN
634
635 lv_sql_stmt:=
636 ' SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
637 ||' FROM MRP_AP_INTER_ORG_TRXS_V'||v_dblink||' x'
638 ||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
639 ||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
640 ||' AND x.inventory_item_id = :arg_inventory_item_id'
641 ||' AND x.organization_id = :arg_organization_id'
642 ||' AND x.source_org_id = :arg_source_org_id';
643
644 EXECUTE IMMEDIATE lv_sql_stmt
645 INTO lv_total_alloc_qty
646 USING arg_start_date,
647 arg_end_date,
648 arg_inventory_item_id,
649 arg_organization_id,
650 arg_source_org_id;
651
652 RETURN lv_total_alloc_qty;
653
654 END inter_org_trx_qty;
655
656 FUNCTION po_supplier_trx_qty
657 ( arg_inventory_item_id IN NUMBER,
658 arg_organization_id IN NUMBER,
659 arg_supplier_id IN NUMBER,
660 arg_supplier_site_id IN NUMBER,
661 arg_start_date IN DATE,
662 arg_end_date IN DATE)
663 RETURN NUMBER
664 IS
665 lv_sql_stmt VARCHAR2(2000);
666 lv_total_alloc_qty NUMBER;
667 BEGIN
668
669 lv_sql_stmt:=
670 ' SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
671 ||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x'
672 ||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
673 ||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
674 ||' AND x.inventory_item_id = :arg_inventory_item_id'
675 ||' AND x.supplier_id = :arg_supplier_id'
676 ||' AND NVL(x.supplier_site_id, -1)'
677 ||' = NVL(:arg_supplier_site_id,-1)';
678
679 EXECUTE IMMEDIATE lv_sql_stmt
680 INTO lv_total_alloc_qty
681 USING arg_start_date,
682 arg_end_date,
683 arg_inventory_item_id,
684 arg_supplier_id,
685 arg_supplier_site_id;
686
687 RETURN lv_total_alloc_qty;
688
689 END po_supplier_trx_qty;
690
691
692 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
693 IS
694 BEGIN
695 IF FND_GLOBAL.CONC_REQUEST_ID > 0 THEN
696 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
697 null;
698 ELSE
699 NULL;
700 END IF;
701 --DBMS_OUTPUT.PUT_LINE( pBUFF);
702 END LOG_MESSAGE;
703
704 END MSC_CL_MISCELLANEOUS;