[Home] [Help]
PACKAGE BODY: APPS.INV_INVTRHAN_XMLP_PKG
Source
1 PACKAGE BODY INV_INVTRHAN_XMLP_PKG AS
2 /* $Header: INVTRHANB.pls 120.2 2008/01/08 06:48:54 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in SRWEXIT')*/NULL;
11 END;
12 BEGIN
13 EXECUTE IMMEDIATE
14 'drop view ' || P_VIEW;
15 EXCEPTION
16 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
17 /*SRW.MESSAGE(2
18 ,'Do sql failed to drop view at end of report.')*/NULL;
19 END;
20 RETURN (TRUE);
21 RETURN (TRUE);
22 END AFTERREPORT;
23 FUNCTION C_TARGET_QTY_VALFORMULA(C_COST_TYPE IN NUMBER
24 ,ASS_INV IN NUMBER
25 ,TARGET_QTY IN NUMBER
26 ,CUR_QTY_VAL_OLD IN NUMBER
27 ,CUR_QTY_VAL IN NUMBER
28 ,SOURCE_TYPE1 IN NUMBER
29 ,SOURCE_TYPE2 IN NUMBER
30 ,SOURCE_TYPE3 IN NUMBER
31 ,SOURCE_TYPE4 IN NUMBER
32 ,SOURCE_TYPE5 IN NUMBER
33 ,OTHER IN NUMBER
34 ,ITEM_ID IN NUMBER
35 ,SUBINVENTORY IN VARCHAR2
36 ,C_STD_PREC IN NUMBER) RETURN NUMBER IS
37 BEGIN
38 DECLARE
39 MY_ORG_ID NUMBER;
40 MY_ITEM_ID NUMBER;
41 MY_SUB VARCHAR2(40);
42 MY_MIN_TRX_ID NUMBER;
43 MY_HIS_DATE VARCHAR2(40);
44 MY_CUR_QTY_VAL NUMBER;
45 MY_TARGET_QTY NUMBER;
46 MY_HIS_VALUE NUMBER;
47 BEGIN
48 IF ((C_COST_TYPE = 1) AND (P_SORT_ID = 1) AND (P_SELECTION = 2) AND (ASS_INV <> 1)) THEN
49 RETURN (0);
50 END IF;
51 IF ((C_COST_TYPE = 2) AND (TARGET_QTY = 0)) THEN
52 RETURN (0);
53 END IF;
54 IF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION in (2,3) THEN
55 RETURN CUR_QTY_VAL_OLD;
56 END IF;
57 IF C_COST_TYPE = 1 OR P_SELECTION = 1 THEN
58 RETURN (CUR_QTY_VAL - SOURCE_TYPE1 - SOURCE_TYPE2 - SOURCE_TYPE3 - SOURCE_TYPE4 - SOURCE_TYPE5 - OTHER);
59 END IF;
60 MY_ORG_ID := P_ORG_ID;
61 MY_ITEM_ID := ITEM_ID;
62 MY_SUB := SUBINVENTORY;
63 MY_HIS_DATE := P_hist_date_1;
64 MY_MIN_TRX_ID := 0;
65 MY_TARGET_QTY := TARGET_QTY;
66 SELECT
67 NVL(MIN(TRANSACTION_ID)
68 ,0)
69 INTO MY_MIN_TRX_ID
70 FROM
71 MTL_MATERIAL_TRANSACTIONS
72 WHERE ORGANIZATION_ID = MY_ORG_ID
73 AND INVENTORY_ITEM_ID = MY_ITEM_ID
74 AND ( SUBINVENTORY_CODE in (
75 SELECT
76 SECONDARY_INVENTORY_NAME
77 FROM
78 MTL_SECONDARY_INVENTORIES
79 WHERE ORGANIZATION_ID = MY_ORG_ID
80 AND ASSET_INVENTORY <> 2 )
81 OR SUBINVENTORY_CODE is null )
82 AND TRANSACTION_DATE >= TO_DATE(MY_HIS_DATE
83 ,'DD-MON-RRRR') + 1
84 AND TRANSACTION_ACTION_ID <> 30;
85 IF (MY_MIN_TRX_ID = 0) THEN
86 RETURN (CUR_QTY_VAL);
87 ELSE
88 SELECT
89 PRIOR_COST
90 INTO MY_HIS_VALUE
91 FROM
92 MTL_MATERIAL_TRANSACTIONS
93 WHERE ORGANIZATION_ID = MY_ORG_ID
94 AND INVENTORY_ITEM_ID = MY_ITEM_ID
95 AND TRANSACTION_ID = MY_MIN_TRX_ID;
96 RETURN (ROUND(MY_TARGET_QTY * MY_HIS_VALUE
97 ,C_STD_PREC));
98 END IF;
99 END;
100 RETURN NULL;
101 END C_TARGET_QTY_VALFORMULA;
102 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
103 BEGIN
104 RETURN (',mtl_item_categories mic, mtl_categories mc');
105 END C_FROM_CATFORMULA;
106 FUNCTION C_WHERE_CATFORMULA RETURN VARCHAR2 IS
107 BEGIN
108 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
109 and mic.category_id = mc.category_id
110 and mic.organization_id = ' || TO_CHAR(P_ORG_ID) || '
111 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID));
112 END C_WHERE_CATFORMULA;
113 FUNCTION C_SOURCE_TYPE1FORMULA RETURN VARCHAR2 IS
114 BEGIN
115 DECLARE
116 SOURCE_TYPE_ID NUMBER;
117 NAME VARCHAR2(40);
118 BEGIN
119 SOURCE_TYPE_ID := P_STYPE1_1;
120 SELECT
121 SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
122 ,0
123 ,14)
124 INTO NAME
125 FROM
126 MTL_TXN_SOURCE_TYPES
127 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
128 RETURN (NAME);
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 RETURN ('Error');
132 WHEN OTHERS THEN
133 RETURN ('Error');
134 END;
135 RETURN NULL;
136 END C_SOURCE_TYPE1FORMULA;
137 FUNCTION C_SOURCE_TYPE2FORMULA RETURN VARCHAR2 IS
138 BEGIN
139 DECLARE
140 SOURCE_TYPE_ID NUMBER;
141 NAME VARCHAR2(40);
142 BEGIN
143 SOURCE_TYPE_ID := P_STYPE2_1;
144 SELECT
145 SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
146 ,0
147 ,14)
148 INTO NAME
149 FROM
150 MTL_TXN_SOURCE_TYPES
151 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
152 RETURN (NAME);
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 RETURN ('Error');
156 WHEN OTHERS THEN
157 RETURN ('Error');
158 END;
159 RETURN NULL;
160 END C_SOURCE_TYPE2FORMULA;
161 FUNCTION C_SOURCE_TYPE3FORMULA RETURN VARCHAR2 IS
162 BEGIN
163 DECLARE
164 SOURCE_TYPE_ID NUMBER;
165 NAME VARCHAR2(40);
166 BEGIN
167 SOURCE_TYPE_ID := P_STYPE3_1;
168 SELECT
169 SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
170 ,0
171 ,13)
172 INTO NAME
173 FROM
174 MTL_TXN_SOURCE_TYPES
175 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
176 RETURN (NAME);
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 RETURN ('Error');
180 WHEN OTHERS THEN
181 RETURN ('Error');
182 END;
183 RETURN NULL;
184 END C_SOURCE_TYPE3FORMULA;
185 FUNCTION C_SOURCE_TYPE4FORMULA RETURN VARCHAR2 IS
186 BEGIN
187 DECLARE
188 SOURCE_TYPE_ID NUMBER;
189 NAME VARCHAR2(40);
190 BEGIN
191 SOURCE_TYPE_ID := P_STYPE4_1;
192 SELECT
193 SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
194 ,0
195 ,13)
196 INTO NAME
197 FROM
198 MTL_TXN_SOURCE_TYPES
199 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
200 RETURN (NAME);
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 RETURN ('Error');
204 WHEN OTHERS THEN
205 RETURN ('Error');
206 END;
207 RETURN NULL;
208 END C_SOURCE_TYPE4FORMULA;
209 FUNCTION C_SOURCE_TYPE5FORMULA RETURN VARCHAR2 IS
210 BEGIN
211 DECLARE
212 SOURCE_TYPE_ID NUMBER;
213 NAME VARCHAR2(40);
214 BEGIN
215 SOURCE_TYPE_ID := P_STYPE5;
216 SELECT
217 SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
218 ,0
219 ,11)
220 INTO NAME
221 FROM
222 MTL_TXN_SOURCE_TYPES
223 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
224 RETURN (NAME);
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 RETURN ('Error');
228 WHEN OTHERS THEN
229 RETURN ('Error');
230 END;
231 RETURN NULL;
232 END C_SOURCE_TYPE5FORMULA;
233 FUNCTION C_WHERE_SUBINVFORMULA RETURN VARCHAR2 IS
234 BEGIN
235 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NOT NULL THEN
236 RETURN ('and v.subinv between ''' || P_SUBINV_LO || ''' and
237 ''' || P_SUBINV_HI || '''');
238 ELSE
239 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NULL THEN
240 RETURN ('and v.subinv >= ''' || P_SUBINV_LO || '''');
241 ELSE
242 IF P_SUBINV_LO IS NULL AND P_SUBINV_HI IS NOT NULL THEN
243 RETURN ('and v.subinv <= ''' || P_SUBINV_HI || '''');
244 ELSE
245 RETURN (' ');
246 END IF;
247 END IF;
248 END IF;
249 RETURN ' ';
250 END C_WHERE_SUBINVFORMULA;
251 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
252 BEGIN
253 DECLARE
254 CAT_SET_ID NUMBER;
255 CAT_SET_NAME VARCHAR2(30);
256 BEGIN
257 IF P_CAT_SET_ID IS NULL THEN
258 RETURN ('');
259 ELSE
260 CAT_SET_ID := P_CAT_SET_ID;
261 SELECT
262 CATEGORY_SET_NAME
263 INTO CAT_SET_NAME
264 FROM
265 MTL_CATEGORY_SETS
266 WHERE CATEGORY_SET_ID = CAT_SET_ID;
267 RETURN (CAT_SET_NAME);
268 END IF;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 RETURN ('');
272 WHEN OTHERS THEN
273 /*SRW.MESSAGE(10
274 ,'Error:No category set selected')*/NULL;
275 END;
276 RETURN NULL;
277 END C_CAT_SET_NAMEFORMULA;
278 FUNCTION C_CHANGE_VALFORMULA(C_TARGET_QTY_VAL IN NUMBER
279 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
280 BEGIN
281 /*SRW.REFERENCE(C_TARGET_QTY_VAL)*/NULL;
282 /*SRW.REFERENCE(CUR_QTY_VAL)*/NULL;
283 RETURN (CUR_QTY_VAL - C_TARGET_QTY_VAL);
284 END C_CHANGE_VALFORMULA;
285 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
286 BEGIN
287 BEGIN
288 P_Stype1_1 := P_Stype1;
289 P_Stype2_1 := P_Stype2;
290 P_Stype3_1 := P_Stype3;
291 P_Stype4_1 := P_Stype4;
292 --P_hist_date_1 := P_hist_date;
293 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
294 EXCEPTION
295 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
296 /*SRW.MESSAGE(1
297 ,'Failed in SRWINIT')*/NULL;
298 RAISE;
299 END;
300 BEGIN
301 NULL;
302 EXCEPTION
303 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
304 /*SRW.MESSAGE(2
305 ,'Failed in MSTK')*/NULL;
306 RAISE;
307 END;
308 BEGIN
309 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
310 NULL;
311 ELSE
312 NULL;
313 END IF;
314 EXCEPTION
315 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
316 /*SRW.MESSAGE(3
317 ,'Failed in MSTK')*/NULL;
318 RAISE;
319 END;
320 BEGIN
321 NULL;
322 EXCEPTION
323 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
324 /*SRW.MESSAGE(3
325 ,'Failed in MSTK/order by')*/NULL;
326 RAISE;
327 END;
328 BEGIN
329 IF P_SORT_ID = 3 THEN
330 NULL;
331 ELSE
332 P_CAT_FLEX := '''X''';
333 END IF;
334 EXCEPTION
335 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
336 /*SRW.MESSAGE(4
337 ,'Failed in MCAT')*/NULL;
338 RAISE;
339 END;
340 BEGIN
341 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
342 NULL;
343 ELSE
344 NULL;
345 END IF;
346 EXCEPTION
347 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
348 /*SRW.MESSAGE(5
349 ,'Failed in MCAT')*/NULL;
350 RAISE;
351 END;
352 RETURN (TRUE);
353 RETURN (TRUE);
354 END BEFOREREPORT;
355 FUNCTION C_CHANGE_QTYFORMULA(CUR_QTY IN NUMBER
356 ,TARGET_QTY IN NUMBER) RETURN NUMBER IS
357 BEGIN
358 RETURN (CUR_QTY - TARGET_QTY);
359 END C_CHANGE_QTYFORMULA;
360 FUNCTION AFTERPFORM RETURN BOOLEAN IS
361 BEGIN
362 /*SRW.MESSAGE(1
363 ,'p_wms_enabled : ' || P_WMS_ENABLED)*/NULL;
364 /*SRW.MESSAGE(1
365 ,'p_pjm_enabled : ' || P_PJM_ENABLED)*/NULL;
366 /*SRW.MESSAGE(1
367 ,'p_wms_pjm_enabled : ' || P_WMS_PJM_ENABLED)*/NULL;
368 DECLARE
369 SELECTION VARCHAR2(20);
370 STYPE1 VARCHAR2(20);
371 STYPE2 VARCHAR2(20);
372 STYPE3 VARCHAR2(20);
373 STYPE4 VARCHAR2(20);
374 STYPE5 VARCHAR2(20);
375 VAR_ORG VARCHAR2(20);
376 HIST_DATE VARCHAR2(40);
377 L_HIST_DATE DATE;
378 VIEW_NAME VARCHAR2(30);
379 CONSIGNED VARCHAR2(20);
380 L_FCN_CURRENCY VARCHAR2(15);
381 L_STMT_NUM NUMBER;
382 L_MSG_COUNT NUMBER;
383 L_MSG_DATA VARCHAR2(8000);
384 L_RETURN_STATUS VARCHAR2(1);
385 L_CST_INV_VAL EXCEPTION;
386 L_VALUATION_DATE DATE;
387 BEGIN
388 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
389 P_hist_date_1 := TO_CHAR(TO_DATE(P_HIST_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-RRRR');
390 P_VIEW_PUTVALIDTRIGGER_1 := P_VIEW_PUTVALIDTRIGGER;
391 SELECTION := TO_CHAR(P_SELECTION);
392 STYPE1 := TO_CHAR(P_STYPE1_1);
393 STYPE2 := TO_CHAR(P_STYPE2_1);
394 STYPE3 := TO_CHAR(P_STYPE3_1);
395 STYPE4 := TO_CHAR(P_STYPE4_1);
396 STYPE5 := TO_CHAR(P_STYPE5);
397 VAR_ORG := TO_CHAR(P_ORG_ID);
398 HIST_DATE := P_hist_date_1;
399 L_HIST_DATE := TO_DATE(P_hist_date_1
400 ,'DD-MON-RRRR') + 1;
401 -- VIEW_NAME := P_VIEW;
402 VIEW_NAME := P_VIEW_PUTVALIDTRIGGER_1 ;
403 CONSIGNED := TO_CHAR(P_CONSIGNED);
404 /*SRW.MESSAGE(1
405 ,'Debugging for Bug 4361479')*/NULL;
406 /*SRW.MESSAGE(1
407 ,VIEW_NAME)*/NULL;
408 IF P_SELECTION = 1 THEN
409 EXECUTE IMMEDIATE
410 'create view ' || VIEW_NAME || ' as
411 select moqd.subinventory_code subinv,
412 moqd.inventory_item_id item_id,
413 0 item_cost,
414 0 source_type1,
415 0 source_type2,
416 0 source_type3,
417 0 source_type4,
418 0 source_type5,
419 0 other,
420 sum(primary_transaction_quantity) cur_qty_val,
421 sum(primary_transaction_quantity) cur_qty,
422 sum(primary_transaction_quantity) target_qty
423 from mtl_onhand_quantities_detail moqd
424 where moqd.organization_id = ' || VAR_ORG || '
425 and moqd.owning_tp_type = DECODE(' || CONSIGNED || ', 2, 2, moqd.owning_tp_type)
426 group by moqd.subinventory_code, moqd.inventory_item_id
427 UNION
428 select mmt.subinventory_code subinv,
429 mmt.inventory_item_id item_id,
430 0 item_cost,
431 sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',primary_quantity)) source_type1,
432 sum(decode(mtst.transaction_source_type_id,' || STYPE2 || ',primary_quantity)) source_type2,
433 sum(decode(mtst.transaction_source_type_id,' || STYPE3 || ',primary_quantity)) source_type3,
434 sum(decode(mtst.transaction_source_type_id,' || STYPE4 || ',primary_quantity)) source_type4,
435 0 source_type5,
436 sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',0,' || STYPE2 || ',0,' || STYPE3 || ',0,' || STYPE4 || ',0,primary_quantity )) other,
437 0 cur_qty_val,
438 0 cur_qty,
439 -sum(primary_quantity) target_qty
440 from mtl_material_transactions mmt,
441 mtl_txn_source_types mtst,
442 mtl_parameters mp
443 where mmt.organization_id = ' || VAR_ORG || '
444 and mp.organization_id = ' || VAR_ORG || '
445 /*and transaction_date >= to_date(''' || HIST_DATE || ''' , ''DD-MON-RRRR'' ) + 1 --GSCC change hist_date + 1 */
446 and transaction_date >= ''' || L_HIST_DATE || ''' --GSCC change hist_date + 1
447 and NVL(mmt.owning_tp_type, 2) = DECODE(' || CONSIGNED || ', 2, 2, NVL(mmt.owning_tp_type, 2))
448 and mmt.transaction_source_type_id = mtst.transaction_source_type_id
449 and nvl(mmt.logical_transaction,2) <> 1 --added for bug 5501066
450 group by mmt.subinventory_code,mmt.inventory_item_id,mp.primary_cost_method
451 ';
452 ELSE
453 IF NVL(P_WMS_ENABLED
454 ,'N') = 'N' AND NVL(P_PJM_ENABLED
455 ,'N') = 'N' THEN
456 EXECUTE IMMEDIATE
457 'create view ' || VIEW_NAME || ' as
458 select moqv.subinventory_code subinv,
459 moqv.inventory_item_id item_id,
460 round(moqv.item_cost,15) item_cost,
461 0 source_type1,
462 0 source_type2,
463 0 source_type3,
464 0 source_type4,
465 0 source_type5,
466 0 other,
467 decode(' || SELECTION || ',1,sum(transaction_quantity),sum(transaction_quantity * NVL(moqv.item_cost,0))) cur_qty_val,
468 sum(transaction_quantity) cur_qty,
469 sum(transaction_quantity) target_qty
470 from mtl_onhand_qty_cost_v moqv
471 where moqv.organization_id = ' || VAR_ORG || '
472 group by moqv.subinventory_code, moqv.inventory_item_id, moqv.item_cost
473 UNION
474 select mmt.subinventory_code subinv,
475 mmt.inventory_item_id item_id,
476 round(cst.item_cost,15) item_cost,
477 sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',
478 decode(' || SELECTION || ',1,primary_quantity,
479 decode(mp.primary_cost_method,2,primary_quantity,
480 decode(' || STYPE1 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
481 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
482 primary_quantity * actual_cost
483 )
484 )
485 )
486 ,0)
487 ) source_type1,
488 sum(decode(mtst.transaction_source_type_id,' || STYPE2 || ',
489 decode(' || SELECTION || ',1,primary_quantity,
490 decode(mp.primary_cost_method,2,primary_quantity,
491 decode(' || STYPE2 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
492 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
493 primary_quantity * actual_cost
494 )
495 )
496 )
497 ,0)
498 ) source_type2,
499 sum(decode(mtst.transaction_source_type_id,' || STYPE3 || ',
500 decode(' || SELECTION || ',1,primary_quantity,
501 decode(mp.primary_cost_method,2,primary_quantity,
502 decode(' || STYPE3 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
503 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
504 primary_quantity * actual_cost
505 )
506 )
507 )
508 ,0)
509 ) source_type3,
510 sum(decode(mtst.transaction_source_type_id,' || STYPE4 || ',
511 decode(' || SELECTION || ',1,primary_quantity,
512 decode(mp.primary_cost_method,2,primary_quantity,
513 decode(mp.primary_cost_method,2,primary_quantity,
514 decode(' || STYPE4 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
515 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
516 primary_quantity * actual_cost
517 )
518 )
519 )
520 )
521 ,0)
522 ) source_type4,
523 0 source_type5,
524 sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',0,' || STYPE2 || ',0,' || STYPE3 || ',0,' || STYPE4 || ',0,
525 decode(' || SELECTION || ',1,primary_quantity,
526 decode(mp.primary_cost_method,2,primary_quantity,
527 decode(mtst.transaction_source_type_id,11,quantity_adjusted*(new_cost-prior_cost),13,
528 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
529 primary_quantity * actual_cost
530 )
531 )
532 )
533 )
534 ) other,
535 0 cur_qty_val,
536 0 cur_qty,
537 -sum(primary_quantity) target_qty
538 from mtl_material_transactions mmt,
539 mtl_txn_source_types mtst,
540 mtl_parameters mp,
541 cst_item_costs_for_gl_view cst
542 where mmt.organization_id = ' || VAR_ORG || '
543 and mp.organization_id = ' || VAR_ORG || '
544 and cst.organization_id = ' || VAR_ORG || '
545 and cst.inventory_item_id = mmt.inventory_item_id
546 and transaction_date >= ''' || L_HIST_DATE || ''' -- GSCC Change hist_date + 1
547 and NVL(mmt.owning_tp_type, 2) = 2
548 and mmt.transaction_source_type_id = mtst.transaction_source_type_id
549 and nvl(mmt.logical_transaction,2) <> 1 --added for bug 5501066
550 group by mmt.subinventory_code,mmt.inventory_item_id,cst.item_cost, mp.primary_cost_method
551 ';
552 ELSE
553 BEGIN
554 L_STMT_NUM := 101;
555 IF P_SELECTION = 3 THEN
556 /*SRW.MESSAGE(1
557 ,'Clearing the source type defaults')*/NULL;
558 P_STYPE1_1 := 0;
559 P_STYPE2_1 := 0;
560 P_STYPE3_1 := 0;
561 P_STYPE4_1 := 0;
562 P_STYPE5 := 0;
563 END IF;
564 EXECUTE IMMEDIATE
565 'create view ' || VIEW_NAME || '
566 as
567 select
568 to_char(NULL) subinv,
569 to_number(NULL) item_id,
570 0 item_cost,
571 0 source_type1,
572 0 source_type2,
573 0 source_type3,
574 0 source_type4,
575 0 source_type5,
576 0 other,
577 0 cur_qty_val,
578 0 cur_qty,
579 0 target_qty
580 from DUAL
581 WHERE 1=2';
582 L_STMT_NUM := 102;
583 CST_INVENTORY_PUB.CALCULATE_INVENTORYVALUE(P_API_VERSION => 1.0
584 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_TRUE
585 ,P_ORGANIZATION_ID => P_ORG_ID
586 ,P_ONHAND_VALUE => 1
587 ,P_INTRANSIT_VALUE => NULL
588 ,P_RECEIVING_VALUE => 0
589 ,P_VALUATION_DATE => L_HIST_DATE
590 ,P_COST_TYPE_ID => NULL
591 ,P_ITEM_FROM => P_ITEM_LO
592 ,P_ITEM_TO => P_ITEM_HI
593 ,P_CATEGORY_SET_ID => P_CAT_SET_ID
594 ,P_CATEGORY_FROM => P_CAT_LO
595 ,P_CATEGORY_TO => P_CAT_HI
596 ,P_COST_GROUP_FROM => P_CG_LO
597 ,P_COST_GROUP_TO => P_CG_HI
598 ,P_SUBINVENTORY_FROM => P_SUBINV_LO
599 ,P_SUBINVENTORY_TO => P_SUBINV_HI
600 ,P_QTY_BY_REVISION => NULL
601 ,P_ZERO_COST_ONLY => NULL
602 ,P_ZERO_QTY => NULL
603 ,P_EXPENSE_ITEM => NULL
604 ,P_EXPENSE_SUB => NULL
605 ,P_UNVALUED_TXNS => 0
606 ,P_RECEIPT => NULL
607 ,P_SHIPMENT => NULL
608 ,X_RETURN_STATUS => L_RETURN_STATUS
609 ,X_MSG_COUNT => L_MSG_COUNT
610 ,X_MSG_DATA => L_MSG_DATA);
611 L_STMT_NUM := 103;
612 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
613 RAISE L_CST_INV_VAL;
614 END IF;
615 L_STMT_NUM := 104;
616 CST_INVENTORY_PVT.CALCULATE_INVENTORYCOST(P_API_VERSION => 1.0
617 ,P_VALUATION_DATE => NULL
618 ,P_ORGANIZATION_ID => P_ORG_ID
619 ,X_RETURN_STATUS => L_RETURN_STATUS);
620 L_STMT_NUM := 105;
621 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
622 RAISE L_CST_INV_VAL;
623 END IF;
624 L_STMT_NUM := 106;
625 L_STMT_NUM := 107;
626 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
627 ,P_COUNT => L_MSG_COUNT
628 ,P_DATA => L_MSG_DATA);
629 L_STMT_NUM := 108;
630 IF L_MSG_COUNT > 0 THEN
631 FOR i IN 1 .. L_MSG_COUNT LOOP
632 L_MSG_DATA := FND_MSG_PUB.GET(I
633 ,CST_UTILITY_PUB.GET_FALSE);
634 /*SRW.MESSAGE(1
635 ,'Message : ' || L_MSG_DATA)*/NULL;
636 END LOOP;
637 END IF;
638 RETURN TRUE;
639 EXCEPTION
640 WHEN OTHERS THEN
641 /*SRW.MESSAGE(999
642 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
643 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
644 ,P_COUNT => L_MSG_COUNT
645 ,P_DATA => L_MSG_DATA);
646 IF L_MSG_COUNT > 0 THEN
647 FOR i IN 1 .. L_MSG_COUNT LOOP
648 L_MSG_DATA := FND_MSG_PUB.GET(I
649 ,CST_UTILITY_PUB.GET_FALSE);
650 /*SRW.MESSAGE(1
651 ,'Message : ' || L_MSG_DATA)*/NULL;
652 END LOOP;
653 END IF;
654 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
655 END;
656 END IF;
657 END IF;
658 EXCEPTION
659 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
660 /*SRW.MESSAGE(1
661 ,'Do sql failed to create view.' || SQLERRM)*/NULL;
662 END;
663 RETURN (TRUE);
664 END AFTERPFORM;
665 -- FUNCTION P_VIEW_PUTVALIDTRIGGER RETURN BOOLEAN IS
666 FUNCTION P_VIEW_PUTVALIDTRIGGER RETURN VARCHAR2 IS
667 BEGIN
668 P_VIEW := 'txn_analysis_view' || TO_CHAR(P_CONC_REQUEST_ID);
669 RETURN (P_VIEW);
670 END P_VIEW_PUTVALIDTRIGGER;
671 FUNCTION C_CURRENCY_CODEFORMULA(CURRENCY_CODE_REP IN VARCHAR2) RETURN VARCHAR2 IS
672 BEGIN
673 RETURN ('(' || CURRENCY_CODE_REP || ')');
674 END C_CURRENCY_CODEFORMULA;
675 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
676 BEGIN
677 DECLARE
678 VIEW_TO_DROP USER_VIEWS.VIEW_NAME%TYPE;
679 CURSOR SEL_VIEWS IS
680 SELECT
681 VIEW_NAME
682 FROM
683 USER_VIEWS
684 WHERE VIEW_NAME LIKE 'TXN_ANALYSIS_VIEW%';
685 BEGIN
686 OPEN SEL_VIEWS;
687 LOOP
688 FETCH SEL_VIEWS
689 INTO VIEW_TO_DROP;
690 EXIT WHEN SEL_VIEWS%NOTFOUND;
691 BEGIN
692 EXECUTE IMMEDIATE
693 'DROP VIEW ' || VIEW_TO_DROP;
694 EXCEPTION
695 /* WHEN SRW.USER_EXIT_FAILURE OTHERS THEN
696 /*SRW.MESSAGE(1
697 ,'Before Form Trigger Failed')NULL;*/
698 WHEN OTHERS THEN
699 /*SRW.MESSAGE(2
700 ,'Before Form Trigger Failed')*/NULL;
701 END;
702 END LOOP;
703 CLOSE SEL_VIEWS;
704 END;
705 /*SRW.MESSAGE(1
706 ,'Just finished dropping ANALYSIS views, if any')*/NULL;
707 RETURN (TRUE);
708 END BEFOREPFORM;
709 FUNCTION C_CAT_PADFORMULA(C_CAT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
710 BEGIN
711 RETURN (C_CAT_PAD);
712 END C_CAT_PADFORMULA;
713 FUNCTION CUR_QTY_VALFORMULA(ASS_INV IN NUMBER
714 ,CUR_QTY_VAL_OLD IN NUMBER
715 ,SOURCE_TYPE1 IN NUMBER
716 ,SOURCE_TYPE2 IN NUMBER
717 ,SOURCE_TYPE3 IN NUMBER
718 ,SOURCE_TYPE4 IN NUMBER
719 ,OTHER IN NUMBER) RETURN NUMBER IS
720 BEGIN
721 BEGIN
722 IF ((P_SORT_ID = 1) AND (P_SELECTION = 2) AND (ASS_INV <> 1)) THEN
723 RETURN (0);
724 ELSIF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION = 2 THEN
725 RETURN (CUR_QTY_VAL_OLD + SOURCE_TYPE1 + SOURCE_TYPE2 + SOURCE_TYPE3 + SOURCE_TYPE4 + OTHER);
726 ELSIF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION = 3 THEN
727 RETURN (CUR_QTY_VAL_OLD + OTHER);
728 ELSE
729 RETURN (CUR_QTY_VAL_OLD);
730 END IF;
731 END;
732 RETURN NULL;
733 END CUR_QTY_VALFORMULA;
734 FUNCTION C_COST_TYPEFORMULA RETURN NUMBER IS
735 BEGIN
736 DECLARE
737 ORG_ID NUMBER;
738 COST_TYPE NUMBER;
739 BEGIN
740 ORG_ID := P_ORG_ID;
741 SELECT
742 PRIMARY_COST_METHOD
743 INTO COST_TYPE
744 FROM
745 MTL_PARAMETERS
746 WHERE ORGANIZATION_ID = ORG_ID;
747 RETURN (COST_TYPE);
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 RETURN ('Error');
751 WHEN OTHERS THEN
752 RETURN ('Error');
753 END;
754 RETURN NULL;
755 END C_COST_TYPEFORMULA;
756 FUNCTION C_OTHERSFORMULA(OTHER IN NUMBER
757 ,C_COST_TYPE IN NUMBER
758 ,ITEM_ID IN NUMBER
759 ,SUBINVENTORY IN VARCHAR2
760 ,TARGET_QTY IN NUMBER
761 ,SOURCE_TYPE1 IN NUMBER
762 ,SOURCE_TYPE2 IN NUMBER
763 ,SOURCE_TYPE3 IN NUMBER
764 ,SOURCE_TYPE4 IN NUMBER
765 ,C_STD_PREC IN NUMBER
766 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
767 BEGIN
768 DECLARE
769 CURRENT_VALUE NUMBER;
770 HIST_VALUE NUMBER;
771 NEW_OTHER_VALUE NUMBER;
772 MY_ORG_ID NUMBER;
773 MY_ITEM_ID NUMBER;
774 MY_SUB VARCHAR2(40);
775 MY_MIN_TRX_ID NUMBER;
776 MY_HIS_DATE VARCHAR2(40);
777 MY_CUR_QTY_VAL NUMBER;
778 MY_TARGET_QTY NUMBER;
779 MY_HIS_VALUE NUMBER;
780 MY_SOURCE1 NUMBER;
781 MY_SOURCE2 NUMBER;
782 MY_SOURCE3 NUMBER;
783 MY_SOURCE4 NUMBER;
784 CURRENT_ITEM_COST NUMBER;
785 BEGIN
786 IF (P_SELECTION = 1) THEN
787 RETURN (OTHER);
788 END IF;
789 IF (C_COST_TYPE = 1) THEN
790 RETURN (OTHER);
791 END IF;
792 BEGIN
793 SELECT
794 ITEM_COST
795 INTO CURRENT_ITEM_COST
796 FROM
797 CST_ITEM_COSTS_FOR_GL_VIEW
798 WHERE ORGANIZATION_ID = P_ORG_ID
799 AND INVENTORY_ITEM_ID = ITEM_ID;
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 RETURN (OTHER);
803 END;
804 IF ((C_COST_TYPE = 2) AND ((P_STYPE1_1 = 13) OR (P_STYPE2_1 = 13) OR (P_STYPE3_1 = 13) OR (P_STYPE4_1 = 13))) THEN
805 RETURN (NVL(OTHER
806 ,0));
807 END IF;
808 MY_ORG_ID := P_ORG_ID;
809 MY_ITEM_ID := ITEM_ID;
810 MY_SUB := SUBINVENTORY;
811 MY_HIS_DATE := P_hist_date_1;
812 MY_MIN_TRX_ID := 0;
813 MY_TARGET_QTY := TARGET_QTY;
814 MY_SOURCE1 := SOURCE_TYPE1;
815 MY_SOURCE2 := SOURCE_TYPE2;
816 MY_SOURCE3 := SOURCE_TYPE3;
817 MY_SOURCE4 := SOURCE_TYPE4;
818 IF (TARGET_QTY = 0) THEN
819 MY_HIS_VALUE := 0;
820 END IF;
821 SELECT
822 NVL(MIN(TRANSACTION_ID)
823 ,0)
824 INTO MY_MIN_TRX_ID
825 FROM
826 MTL_MATERIAL_TRANSACTIONS
827 WHERE ORGANIZATION_ID = MY_ORG_ID
828 AND INVENTORY_ITEM_ID = MY_ITEM_ID
829 AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
830 ,'DD-MON-RRRR');
831 IF (MY_MIN_TRX_ID = 0) THEN
832 RETURN (0);
833 ELSE
834 SELECT
835 NVL(PRIOR_COST
836 ,0)
837 INTO MY_HIS_VALUE
838 FROM
839 MTL_MATERIAL_TRANSACTIONS
840 WHERE ORGANIZATION_ID = MY_ORG_ID
841 AND INVENTORY_ITEM_ID = MY_ITEM_ID
842 AND TRANSACTION_ID = MY_MIN_TRX_ID;
843 END IF;
844 HIST_VALUE := ROUND(MY_TARGET_QTY * MY_HIS_VALUE
845 ,C_STD_PREC);
846 CURRENT_VALUE := CUR_QTY_VAL;
847 NEW_OTHER_VALUE := CURRENT_VALUE - HIST_VALUE - MY_SOURCE1 - MY_SOURCE2 - MY_SOURCE3 - MY_SOURCE4;
848 RETURN (NEW_OTHER_VALUE);
849 END;
850 RETURN NULL;
851 END C_OTHERSFORMULA;
852 FUNCTION C_SOURCE_TYPE1_CFORMULA(SOURCE_TYPE1 IN NUMBER
853 ,C_COST_TYPE IN NUMBER
854 ,ITEM_ID IN NUMBER
855 ,SUBINVENTORY IN VARCHAR2
856 ,TARGET_QTY IN NUMBER
857 ,C_SOURCE_TYPE2_C IN NUMBER
858 ,C_SOURCE_TYPE3_C IN NUMBER
859 ,C_SOURCE_TYPE4_C IN NUMBER
860 ,OTHER IN NUMBER
861 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
862 BEGIN
863 DECLARE
864 CURRENT_VALUE NUMBER;
865 HIST_VALUE NUMBER;
866 NEW_OTHER_VALUE NUMBER;
867 MY_ORG_ID NUMBER;
868 MY_ITEM_ID NUMBER;
869 MY_SUB VARCHAR2(40);
870 MY_MIN_TRX_ID NUMBER;
871 MY_HIS_DATE VARCHAR2(40);
872 MY_CUR_QTY_VAL NUMBER;
873 MY_TARGET_QTY NUMBER;
874 MY_HIS_VALUE NUMBER;
875 NEW_SOURCE1 NUMBER;
876 MY_SOURCE2 NUMBER;
877 MY_SOURCE3 NUMBER;
878 MY_SOURCE4 NUMBER;
879 MY_OTHERS NUMBER;
880 CURRENT_ITEM_COST NUMBER;
881 BEGIN
882 IF (P_SELECTION = 1) THEN
883 RETURN (SOURCE_TYPE1);
884 END IF;
885 IF ((C_COST_TYPE <> 2) OR (P_STYPE1_1 <> 13)) THEN
886 RETURN (SOURCE_TYPE1);
887 END IF;
888 MY_ORG_ID := P_ORG_ID;
889 MY_ITEM_ID := ITEM_ID;
890 MY_SUB := SUBINVENTORY;
891 MY_HIS_DATE := P_hist_date_1;
892 MY_MIN_TRX_ID := 0;
893 MY_TARGET_QTY := TARGET_QTY;
894 IF ((C_COST_TYPE = 2) AND (P_STYPE2_1 = 13)) THEN
895 MY_SOURCE2 := 0;
896 ELSE
897 MY_SOURCE2 := C_SOURCE_TYPE2_C;
898 END IF;
899 IF ((C_COST_TYPE = 2) AND (P_STYPE3_1 = 13)) THEN
900 MY_SOURCE3 := 0;
901 ELSE
902 MY_SOURCE3 := C_SOURCE_TYPE3_C;
903 END IF;
904 IF ((C_COST_TYPE = 2) AND (P_STYPE4_1 = 13)) THEN
905 MY_SOURCE4 := 0;
906 ELSE
907 MY_SOURCE4 := C_SOURCE_TYPE4_C;
908 END IF;
909 SELECT
910 ITEM_COST
911 INTO CURRENT_ITEM_COST
912 FROM
913 CST_ITEM_COSTS_FOR_GL_VIEW
914 WHERE ORGANIZATION_ID = P_ORG_ID
915 AND INVENTORY_ITEM_ID = ITEM_ID;
916 MY_OTHERS := NVL(OTHER
917 ,0);
918 IF (TARGET_QTY = 0) THEN
919 MY_HIS_VALUE := 0;
920 END IF;
921 SELECT
922 NVL(MIN(TRANSACTION_ID)
923 ,0)
924 INTO MY_MIN_TRX_ID
925 FROM
926 MTL_MATERIAL_TRANSACTIONS
927 WHERE ORGANIZATION_ID = MY_ORG_ID
928 AND INVENTORY_ITEM_ID = MY_ITEM_ID
929 AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
930 ,'DD-MON-RRRR');
931 IF (MY_MIN_TRX_ID = 0) THEN
932 RETURN (0);
933 ELSE
934 SELECT
935 PRIOR_COST
936 INTO MY_HIS_VALUE
937 FROM
938 MTL_MATERIAL_TRANSACTIONS
939 WHERE ORGANIZATION_ID = MY_ORG_ID
940 AND INVENTORY_ITEM_ID = MY_ITEM_ID
941 AND TRANSACTION_ID = MY_MIN_TRX_ID;
942 END IF;
943 HIST_VALUE := MY_HIS_VALUE * MY_TARGET_QTY;
944 CURRENT_VALUE := CUR_QTY_VAL;
945 NEW_SOURCE1 := CURRENT_VALUE - HIST_VALUE - MY_OTHERS - MY_SOURCE2 - MY_SOURCE3 - MY_SOURCE4;
946 RETURN (NEW_SOURCE1);
947 END;
948 RETURN NULL;
949 END C_SOURCE_TYPE1_CFORMULA;
950 FUNCTION C_SOURCE_TYPE2_CFORMULA(SOURCE_TYPE2 IN NUMBER
951 ,C_COST_TYPE IN NUMBER
952 ,ITEM_ID IN NUMBER
953 ,SUBINVENTORY IN VARCHAR2
954 ,TARGET_QTY IN NUMBER
955 ,SOURCE_TYPE1 IN NUMBER
956 ,SOURCE_TYPE3 IN NUMBER
957 ,SOURCE_TYPE4 IN NUMBER
958 ,OTHER IN NUMBER
959 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
960 BEGIN
961 DECLARE
962 CURRENT_VALUE NUMBER;
963 HIST_VALUE NUMBER;
964 NEW_OTHER_VALUE NUMBER;
965 MY_ORG_ID NUMBER;
966 MY_ITEM_ID NUMBER;
967 MY_SUB VARCHAR2(40);
968 MY_MIN_TRX_ID NUMBER;
969 MY_HIS_DATE VARCHAR2(40);
970 MY_CUR_QTY_VAL NUMBER;
971 MY_TARGET_QTY NUMBER;
972 MY_HIS_VALUE NUMBER;
973 NEW_SOURCE2 NUMBER;
974 MY_SOURCE1 NUMBER;
978 CURRENT_ITEM_COST NUMBER;
975 MY_SOURCE3 NUMBER;
976 MY_SOURCE4 NUMBER;
977 MY_OTHERS NUMBER;
979 BEGIN
980 IF (P_SELECTION = 1) THEN
981 RETURN (SOURCE_TYPE2);
982 END IF;
983 IF ((C_COST_TYPE <> 2) OR (P_STYPE2_1 <> 13)) THEN
984 RETURN (SOURCE_TYPE2);
985 END IF;
986 MY_ORG_ID := P_ORG_ID;
987 MY_ITEM_ID := ITEM_ID;
988 MY_SUB := SUBINVENTORY;
989 MY_HIS_DATE := P_hist_date_1;
990 MY_MIN_TRX_ID := 0;
991 MY_TARGET_QTY := TARGET_QTY;
992 IF ((C_COST_TYPE = 2) AND (P_STYPE1_1 = 13)) THEN
993 MY_SOURCE1 := 0;
994 ELSE
995 MY_SOURCE1 := SOURCE_TYPE1;
996 END IF;
997 IF ((C_COST_TYPE = 2) AND (P_STYPE3_1 = 13)) THEN
998 MY_SOURCE3 := 0;
999 ELSE
1000 MY_SOURCE3 := SOURCE_TYPE3;
1001 END IF;
1002 IF ((C_COST_TYPE = 2) AND (P_STYPE4_1 = 13)) THEN
1003 MY_SOURCE4 := 0;
1004 ELSE
1005 MY_SOURCE4 := SOURCE_TYPE4;
1006 END IF;
1007 SELECT
1008 ITEM_COST
1009 INTO CURRENT_ITEM_COST
1010 FROM
1011 CST_ITEM_COSTS_FOR_GL_VIEW
1012 WHERE ORGANIZATION_ID = P_ORG_ID
1013 AND INVENTORY_ITEM_ID = ITEM_ID;
1014 MY_OTHERS := NVL(OTHER
1015 ,0);
1016 IF (TARGET_QTY = 0) THEN
1017 MY_HIS_VALUE := 0;
1018 END IF;
1019 SELECT
1020 NVL(MIN(TRANSACTION_ID)
1021 ,0)
1022 INTO MY_MIN_TRX_ID
1023 FROM
1024 MTL_MATERIAL_TRANSACTIONS
1025 WHERE ORGANIZATION_ID = MY_ORG_ID
1026 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1027 AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
1028 ,'DD-MON-RRRR');
1029 IF (MY_MIN_TRX_ID = 0) THEN
1030 RETURN (0);
1031 ELSE
1032 SELECT
1033 PRIOR_COST
1034 INTO MY_HIS_VALUE
1035 FROM
1036 MTL_MATERIAL_TRANSACTIONS
1037 WHERE ORGANIZATION_ID = MY_ORG_ID
1038 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1039 AND TRANSACTION_ID = MY_MIN_TRX_ID;
1040 END IF;
1041 HIST_VALUE := MY_HIS_VALUE * MY_TARGET_QTY;
1042 CURRENT_VALUE := CUR_QTY_VAL;
1043 NEW_SOURCE2 := CURRENT_VALUE - HIST_VALUE - MY_OTHERS - MY_SOURCE1 - MY_SOURCE3 - MY_SOURCE4;
1044 RETURN (NEW_SOURCE2);
1045 END;
1046 RETURN NULL;
1047 END C_SOURCE_TYPE2_CFORMULA;
1048 FUNCTION C_SOURCE_TYPE3_CFORMULA(SOURCE_TYPE3 IN NUMBER
1049 ,C_COST_TYPE IN NUMBER
1050 ,ITEM_ID IN NUMBER
1051 ,SUBINVENTORY IN VARCHAR2
1052 ,TARGET_QTY IN NUMBER
1053 ,SOURCE_TYPE1 IN NUMBER
1054 ,SOURCE_TYPE2 IN NUMBER
1055 ,SOURCE_TYPE4 IN NUMBER
1056 ,OTHER IN NUMBER
1057 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
1058 BEGIN
1059 DECLARE
1060 CURRENT_VALUE NUMBER;
1061 HIST_VALUE NUMBER;
1062 NEW_OTHER_VALUE NUMBER;
1063 MY_ORG_ID NUMBER;
1064 MY_ITEM_ID NUMBER;
1065 MY_SUB VARCHAR2(40);
1066 MY_MIN_TRX_ID NUMBER;
1067 MY_HIS_DATE VARCHAR2(40);
1068 MY_CUR_QTY_VAL NUMBER;
1069 MY_TARGET_QTY NUMBER;
1070 MY_HIS_VALUE NUMBER;
1071 NEW_SOURCE3 NUMBER;
1072 MY_SOURCE1 NUMBER;
1073 MY_SOURCE2 NUMBER;
1074 MY_SOURCE4 NUMBER;
1075 MY_OTHERS NUMBER;
1076 CURRENT_ITEM_COST NUMBER;
1077 BEGIN
1078 IF (P_SELECTION = 1) THEN
1079 RETURN (SOURCE_TYPE3);
1080 END IF;
1081 IF ((C_COST_TYPE <> 2) OR (P_STYPE3_1 <> 13)) THEN
1082 RETURN (SOURCE_TYPE3);
1083 END IF;
1084 MY_ORG_ID := P_ORG_ID;
1085 MY_ITEM_ID := ITEM_ID;
1086 MY_SUB := SUBINVENTORY;
1087 MY_HIS_DATE := P_hist_date_1;
1088 MY_MIN_TRX_ID := 0;
1089 MY_TARGET_QTY := TARGET_QTY;
1090 IF ((C_COST_TYPE = 2) AND (P_STYPE1_1 = 13)) THEN
1091 MY_SOURCE1 := 0;
1092 ELSE
1093 MY_SOURCE1 := SOURCE_TYPE1;
1094 END IF;
1095 IF ((C_COST_TYPE = 2) AND (P_STYPE2_1 = 13)) THEN
1096 MY_SOURCE2 := 0;
1097 ELSE
1098 MY_SOURCE2 := SOURCE_TYPE2;
1099 END IF;
1100 IF ((C_COST_TYPE = 2) AND (P_STYPE4_1 = 13)) THEN
1101 MY_SOURCE4 := 0;
1102 ELSE
1103 MY_SOURCE4 := SOURCE_TYPE4;
1104 END IF;
1105 SELECT
1106 ITEM_COST
1107 INTO CURRENT_ITEM_COST
1108 FROM
1109 CST_ITEM_COSTS_FOR_GL_VIEW
1110 WHERE ORGANIZATION_ID = P_ORG_ID
1111 AND INVENTORY_ITEM_ID = ITEM_ID;
1112 MY_OTHERS := NVL(OTHER
1113 ,0);
1114 IF (TARGET_QTY = 0) THEN
1115 MY_HIS_VALUE := 0;
1116 END IF;
1117 SELECT
1118 NVL(MIN(TRANSACTION_ID)
1119 ,0)
1120 INTO MY_MIN_TRX_ID
1121 FROM
1122 MTL_MATERIAL_TRANSACTIONS
1123 WHERE ORGANIZATION_ID = MY_ORG_ID
1124 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1125 AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
1126 ,'DD-MON-RRRR');
1127 IF (MY_MIN_TRX_ID = 0) THEN
1128 RETURN (0);
1129 ELSE
1130 SELECT
1131 PRIOR_COST
1132 INTO MY_HIS_VALUE
1133 FROM
1134 MTL_MATERIAL_TRANSACTIONS
1135 WHERE ORGANIZATION_ID = MY_ORG_ID
1136 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1140 CURRENT_VALUE := CUR_QTY_VAL;
1137 AND TRANSACTION_ID = MY_MIN_TRX_ID;
1138 END IF;
1139 HIST_VALUE := MY_HIS_VALUE * MY_TARGET_QTY;
1141 NEW_SOURCE3 := CURRENT_VALUE - HIST_VALUE - MY_OTHERS - MY_SOURCE1 - MY_SOURCE2 - MY_SOURCE4;
1142 RETURN (NEW_SOURCE3);
1143 END;
1144 RETURN NULL;
1145 END C_SOURCE_TYPE3_CFORMULA;
1146 FUNCTION C_SOURCE_TYPE4_CFORMULA(SOURCE_TYPE4 IN NUMBER
1147 ,C_COST_TYPE IN NUMBER
1148 ,ITEM_ID IN NUMBER
1149 ,SUBINVENTORY IN VARCHAR2
1150 ,TARGET_QTY IN NUMBER
1151 ,SOURCE_TYPE1 IN NUMBER
1152 ,SOURCE_TYPE2 IN NUMBER
1153 ,SOURCE_TYPE3 IN NUMBER
1154 ,OTHER IN NUMBER
1155 ,CUR_QTY_VAL IN NUMBER) RETURN NUMBER IS
1156 BEGIN
1157 DECLARE
1158 CURRENT_VALUE NUMBER;
1159 HIST_VALUE NUMBER;
1160 NEW_OTHER_VALUE NUMBER;
1161 MY_ORG_ID NUMBER;
1162 MY_ITEM_ID NUMBER;
1163 MY_SUB VARCHAR2(40);
1164 MY_MIN_TRX_ID NUMBER;
1165 MY_HIS_DATE VARCHAR2(40);
1166 MY_CUR_QTY_VAL NUMBER;
1167 MY_TARGET_QTY NUMBER;
1168 MY_HIS_VALUE NUMBER;
1169 NEW_SOURCE4 NUMBER;
1170 MY_SOURCE1 NUMBER;
1171 MY_SOURCE2 NUMBER;
1172 MY_SOURCE3 NUMBER;
1173 MY_OTHERS NUMBER;
1174 CURRENT_ITEM_COST NUMBER;
1175 BEGIN
1176 IF (P_SELECTION = 1) THEN
1177 RETURN (SOURCE_TYPE4);
1178 END IF;
1179 IF ((C_COST_TYPE <> 2) OR (P_STYPE4_1 <> 13)) THEN
1180 RETURN (SOURCE_TYPE4);
1181 END IF;
1182 MY_ORG_ID := P_ORG_ID;
1183 MY_ITEM_ID := ITEM_ID;
1184 MY_SUB := SUBINVENTORY;
1185 MY_HIS_DATE := P_hist_date_1;
1186 MY_MIN_TRX_ID := 0;
1187 MY_TARGET_QTY := TARGET_QTY;
1188 IF ((C_COST_TYPE = 2) AND (P_STYPE1_1 = 13)) THEN
1189 MY_SOURCE1 := 0;
1190 ELSE
1191 MY_SOURCE1 := SOURCE_TYPE1;
1192 END IF;
1193 IF ((C_COST_TYPE = 2) AND (P_STYPE2_1 = 13)) THEN
1194 MY_SOURCE2 := 0;
1195 ELSE
1196 MY_SOURCE2 := SOURCE_TYPE2;
1197 END IF;
1198 IF ((C_COST_TYPE = 2) AND (P_STYPE3_1 = 13)) THEN
1199 MY_SOURCE3 := 0;
1200 ELSE
1201 MY_SOURCE3 := SOURCE_TYPE3;
1202 END IF;
1203 SELECT
1204 ITEM_COST
1205 INTO CURRENT_ITEM_COST
1206 FROM
1207 CST_ITEM_COSTS_FOR_GL_VIEW
1208 WHERE ORGANIZATION_ID = P_ORG_ID
1209 AND INVENTORY_ITEM_ID = ITEM_ID;
1210 MY_OTHERS := NVL(OTHER
1211 ,0);
1212 IF (TARGET_QTY = 0) THEN
1213 MY_HIS_VALUE := 0;
1214 END IF;
1215 SELECT
1216 NVL(MIN(TRANSACTION_ID)
1217 ,0)
1218 INTO MY_MIN_TRX_ID
1219 FROM
1220 MTL_MATERIAL_TRANSACTIONS
1221 WHERE ORGANIZATION_ID = MY_ORG_ID
1222 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1223 AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
1224 ,'DD-MON-RRRR');
1225 IF (MY_MIN_TRX_ID = 0) THEN
1226 RETURN (0);
1227 ELSE
1228 SELECT
1229 PRIOR_COST
1230 INTO MY_HIS_VALUE
1231 FROM
1232 MTL_MATERIAL_TRANSACTIONS
1233 WHERE ORGANIZATION_ID = MY_ORG_ID
1234 AND INVENTORY_ITEM_ID = MY_ITEM_ID
1235 AND TRANSACTION_ID = MY_MIN_TRX_ID;
1236 END IF;
1237 HIST_VALUE := MY_HIS_VALUE * MY_TARGET_QTY;
1238 CURRENT_VALUE := CUR_QTY_VAL;
1239 NEW_SOURCE4 := CURRENT_VALUE - HIST_VALUE - MY_OTHERS - MY_SOURCE1 - MY_SOURCE2 - MY_SOURCE3;
1240 RETURN (NEW_SOURCE4);
1241 END;
1242 RETURN NULL;
1243 END C_SOURCE_TYPE4_CFORMULA;
1244 FUNCTION C_SOURCE_TYPE5_CFORMULA(SOURCE_TYPE5 IN NUMBER) RETURN NUMBER IS
1245 BEGIN
1246 BEGIN
1247 RETURN (SOURCE_TYPE5);
1248 END;
1249 RETURN NULL;
1250 END C_SOURCE_TYPE5_CFORMULA;
1251 END INV_INVTRHAN_XMLP_PKG;
1252