1 PACKAGE BODY BOM_CSTRPMDD_XMLP_PKG AS
2 /* $Header: CSTRPMDDB.pls 120.0 2007/12/24 10:14:13 dwkrishn noship $ */
3 FUNCTION C_SOURCE_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 SOURCE_TYPE_ID NUMBER;
7 SOURCE_TYPE_NAME VARCHAR2(30);
8 BEGIN
9 SOURCE_TYPE_ID := P_SOURCE_TYPE_ID;
10 IF P_SOURCE_TYPE_ID IS NOT NULL THEN
11 SELECT
12 TRANSACTION_SOURCE_TYPE_NAME
13 INTO SOURCE_TYPE_NAME
14 FROM
15 MTL_TXN_SOURCE_TYPES
16 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
17 RETURN (SOURCE_TYPE_NAME);
18 ELSE
19 RETURN ('');
20 END IF;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 RETURN ('No Data');
24 END;
25 RETURN NULL;
26 END C_SOURCE_TYPE_NAMEFORMULA;
27
28 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
29 BEGIN
30 IF P_SORT_ID = 1 THEN
31 /*SRW.SET_MAXROW('Q_acct_item'
32 ,0)*/NULL;
33 /*SRW.SET_MAXROW('Q_item_acct'
34 ,0)*/NULL;
35 ELSIF P_SORT_ID = 2 THEN
36 /*SRW.SET_MAXROW('Q_item_acct'
37 ,0)*/NULL;
38 /*SRW.SET_MAXROW('Q_acct'
39 ,0)*/NULL;
40 ELSIF P_SORT_ID = 3 THEN
41 /*SRW.SET_MAXROW('Q_acct'
42 ,0)*/NULL;
43 /*SRW.SET_MAXROW('Q_acct_item'
44 ,0)*/NULL;
45 ELSE
46 NULL;
47 END IF;
48 BEGIN
49 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
50 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
51 EXCEPTION
52 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
53 /*SRW.MESSAGE(1
54 ,'Failed in SRWINIT')*/NULL;
55 RAISE;
56 END;
57 BEGIN
58 SELECT
59 FRV.RESPONSIBILITY_NAME,
60 TO_CHAR(FCR.REQUEST_DATE
61 ,'YYYY/MM/DD HH24:MI:SS'),
62 FAV.APPLICATION_NAME,
63 FU.USER_NAME
64 INTO CP_RESPONSIBILITY,CP_REQUEST_TIME,CP_APPLICATION,CP_REQUESTED_BY
65 FROM
66 FND_CONCURRENT_REQUESTS FCR,
67 FND_RESPONSIBILITY_VL FRV,
68 FND_APPLICATION_VL FAV,
69 FND_USER FU
70 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
71 AND FCR.RESPONSIBILITY_APPLICATION_ID = FRV.APPLICATION_ID
72 AND FCR.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
73 AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
74 AND FU.USER_ID = FCR.REQUESTED_BY;
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 /*SRW.MESSAGE(30
78 ,'Failed Request By and Request time Init, no data')*/NULL;
79 WHEN OTHERS THEN
80 /*SRW.MESSAGE(31
81 ,'Failed Request By and Request time Init.')*/NULL;
82 END;
83 BEGIN
84 SELECT
85 DISTINCT
86 XFI.NAME,
87 CCG.COST_GROUP,
88 CCT.COST_TYPE,
89 NVL(FC.PRECISION
90 ,2),
91 NVL(FC.EXTENDED_PRECISION
92 ,5),
93 CPP.PERIOD_NAME
94 INTO CP_LEGAL_ENTITY,CP_COST_GROUP,CP_COST_TYPE,CP_PRECISION,CP_EXT_PRECISION,CP_PERIOD_NAME
95 FROM
96 CST_PAC_PERIODS CPP,
97 XLE_FIRSTPARTY_INFORMATION_V XFI,
98 FND_CURRENCIES FC,
99 CST_COST_GROUPS CCG,
100 CST_COST_TYPES CCT
101 WHERE XFI.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
102 AND CPP.PAC_PERIOD_ID = P_PERIOD_ID
103 AND FC.CURRENCY_CODE = P_CURRENCY_CODE
104 AND CCG.COST_GROUP_ID = P_COST_GROUP_ID
105 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 /*SRW.MESSAGE(30
109 ,'Failed in Legal Entity Init. no data')*/NULL;
110 WHEN OTHERS THEN
111 /*SRW.MESSAGE(31
112 ,'Failed in legal entity Init.')*/NULL;
113 END;
114 Qty_precision := bom_common_xmlp_pkg.get_precision(CP_PRECISION);
115 BEGIN
116 NULL;
117 EXCEPTION
118 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
119 /*SRW.MESSAGE(2
120 ,'Failed in MSTK/Select')*/NULL;
121 RAISE;
122 END;
123 BEGIN
124 NULL;
125 EXCEPTION
126 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
127 /*SRW.MESSAGE(3
128 ,'Failed in GL#/Select')*/NULL;
129 RAISE;
130 END;
131 BEGIN
132 IF P_ACCT_LO IS NOT NULL OR P_ACCT_HI IS NOT NULL THEN
133 NULL;
134 ELSE
135 NULL;
136 END IF;
137 EXCEPTION
138 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
139 /*SRW.MESSAGE(4
140 ,'Failed in GL#/Where')*/NULL;
141 RAISE;
142 END;
143 BEGIN
144 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
145 NULL;
146 ELSE
147 NULL;
148 END IF;
149 EXCEPTION
150 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
151 /*SRW.MESSAGE(5
152 ,'Failed in MSTK/Where')*/NULL;
153 RAISE;
154 END;
155 BEGIN
156 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
157 NULL;
158 ELSE
159 NULL;
160 END IF;
161 EXCEPTION
162 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
163 /*SRW.MESSAGE(6
164 ,'Failed in MCAT/Where')*/NULL;
165 RAISE;
166 END;
167 BEGIN
168 NULL;
169 EXCEPTION
170 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
171 /*SRW.MESSAGE(9
172 ,'Failed in MKTS/Sel')*/NULL;
173 RAISE;
174 END;
175 BEGIN
176 NULL;
177 EXCEPTION
178 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
179 /*SRW.MESSAGE(10
180 ,'Failed in MDSP/Sel')*/NULL;
181 RAISE;
182 END;
183 BEGIN
184 NULL;
185 EXCEPTION
186 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
187 /*SRW.MESSAGE(11
188 ,'Failed in GL#/Sel')*/NULL;
189 RAISE;
190 END;
191 BEGIN
192 IF P_SOURCE_TYPE_ID in (2,8,12) THEN
193 BEGIN
194 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
195 NULL;
196 ELSE
197 NULL;
198 END IF;
199 EXCEPTION
200 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
201 /*SRW.MESSAGE(1
202 ,'Failed in before report trigger:MKTS/where')*/NULL;
203 RAISE;
204 END;
205 ELSIF P_SOURCE_TYPE_ID = 6 THEN
206 BEGIN
207 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
208 NULL;
209 ELSE
210 NULL;
211 END IF;
212 EXCEPTION
213 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
214 /*SRW.MESSAGE(1
215 ,'Failed in before report trigger:MDSP/where')*/NULL;
216 RAISE;
217 END;
218 ELSIF P_SOURCE_TYPE_ID = 3 THEN
219 BEGIN
220 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
221 NULL;
222 ELSE
223 NULL;
224 END IF;
225 EXCEPTION
226 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
227 /*SRW.MESSAGE(1
228 ,'Failed in before report trigger:GL/where')*/NULL;
229 RAISE;
230 END;
231 ELSE
232 NULL;
233 END IF;
234 END;
235 BEGIN
236 IF P_SOURCE_TYPE_ID in (2,8,12) AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
237 P_SOURCE_WHERE2 := 'AND mmt.transaction_source_id = mkts.sales_order_id
238 AND ' || P_SOURCE_WHERE;
239 ELSIF P_SOURCE_TYPE_ID = 3 AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
240 P_SOURCE_WHERE2 := 'AND mmt.transaction_source_id = glc.code_combination_id
241 AND ' || P_SOURCE_WHERE;
242 ELSIF P_SOURCE_TYPE_ID = 6 AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
243 P_SOURCE_WHERE2 := 'AND mmt.transaction_source_id = mdsp.disposition_id
244 and ' || P_SOURCE_WHERE;
245 END IF;
246 END;
247 BEGIN
248 SELECT
249 MEANING
250 INTO CP_SORT_BY_COV
251 FROM
252 MFG_LOOKUPS
253 WHERE LOOKUP_TYPE = 'CST_SRS_PAC_BOM_CSTRPMDD_XMLP_PKG_SORT'
254 AND LOOKUP_CODE = P_SORT_ID;
255 SELECT
256 MEANING
257 INTO CP_TYPE_OPTION_COV
258 FROM
259 MFG_LOOKUPS
260 WHERE LOOKUP_TYPE = 'INV_SRS_DST_TYPE'
261 AND LOOKUP_CODE = P_TYPE_OPTION
262 AND ENABLED_FLAG = 'Y';
263 EXCEPTION
264 WHEN OTHERS THEN
265 /*SRW.MESSAGE(2
266 ,'Failed in initializing sort by for cover page')*/NULL;
267 END;
268 RETURN (TRUE);
269 END BEFOREREPORT;
270
271 FUNCTION AFTERREPORT RETURN BOOLEAN IS
272 BEGIN
273 BEGIN
274 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
275 EXCEPTION
276 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
277 /*SRW.MESSAGE(1
278 ,'Failed in SRWEXIT')*/NULL;
279 END;
280 RETURN (TRUE);
281 RETURN (TRUE);
282 END AFTERREPORT;
283
284 FUNCTION C_ACCT_PAD0FORMULA(C_ACCT_PAD0 IN VARCHAR2) RETURN VARCHAR2 IS
285 BEGIN
286 RETURN (C_ACCT_PAD0);
287 END C_ACCT_PAD0FORMULA;
288
289 FUNCTION C_ACCT_PAD1FORMULA(C_ACCT_PAD1 IN VARCHAR2) RETURN VARCHAR2 IS
290 BEGIN
291 RETURN (C_ACCT_PAD1);
292 END C_ACCT_PAD1FORMULA;
293
294 FUNCTION C_ACCT_PAD2FORMULA(C_ACCT_PAD2 IN VARCHAR2) RETURN VARCHAR2 IS
295 BEGIN
296 RETURN (C_ACCT_PAD2);
297 END C_ACCT_PAD2FORMULA;
298
299 FUNCTION C_ITEM_PAD1FORMULA(C_ITEM_PAD1 IN VARCHAR2) RETURN VARCHAR2 IS
300 BEGIN
301 RETURN (C_ITEM_PAD1);
302 END C_ITEM_PAD1FORMULA;
303
304 FUNCTION C_ITEM_PAD2FORMULA(C_ITEM_PAD2 IN VARCHAR2) RETURN VARCHAR2 IS
305 BEGIN
306 RETURN (C_ITEM_PAD2);
307 END C_ITEM_PAD2FORMULA;
308
309 FUNCTION C_SOURCE_WHEREFORMULA RETURN VARCHAR2 IS
310 BEGIN
311 BEGIN
312 IF P_SOURCE_TYPE_ID = 1 THEN
313 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
314 RETURN ('AND mmt.transaction_source_id = poh.po_header_id
315 AND poh.segment1 BETWEEN ''' || P_SOURCE_LO || ''' AND ''' || P_SOURCE_HI || ''' ');
316 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
317 RETURN ('AND mmt.transaction_source_id = poh.po_header_id
318 AND poh.segment1 >= ''' || P_SOURCE_LO || ''' ');
319 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
320 RETURN ('AND mmt.transaction_source_id = poh.po_header_id
321 AND poh.segment1 <= ''' || P_SOURCE_HI || ''' ');
322 END IF;
323 END IF;
324 END;
325 BEGIN
326 IF P_SOURCE_TYPE_ID = 5 THEN
327 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
328 RETURN ('AND mmt.transaction_source_id = wipe.wip_entity_id
329 AND wipe.wip_entity_name BETWEEN ''' || P_SOURCE_LO || '''
330 AND ''' || P_SOURCE_HI || ''' ');
331 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
332 RETURN ('AND mmt.transaction_source_id = wipe.wip_entity_id
333 AND wipe.wip_entity_name >= ''' || P_SOURCE_LO || ''' ');
334 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
335 RETURN ('AND mmt.transaction_source_id = wipe.wip_entity_id
336 AND wipe.wip_entity_name <= ''' || P_SOURCE_HI || ''' ');
337 END IF;
338 END IF;
339 END;
340 BEGIN
341 IF P_SOURCE_TYPE_ID = 7 THEN
342 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
343 RETURN ('AND mmt.transaction_source_id = prh.requisition_header_id
344 AND prh.segment1 BETWEEN ''' || P_SOURCE_LO || ''' AND ''' || P_SOURCE_HI || ''' ');
345 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
346 RETURN ('AND mmt.transaction_source_id = prh.requisition_header_id
347 AND prh.segment1 >= ''' || P_SOURCE_LO || ''' ');
348 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
349 RETURN ('AND mmt.transaction_source_id = prh.requisition_header_id
350 AND prh.segment1 <= ''' || P_SOURCE_HI || ''' ');
351 END IF;
352 END IF;
353 END;
354 BEGIN
355 IF P_SOURCE_TYPE_ID = 9 THEN
356 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
357 RETURN ('AND mmt.transaction_source_id = cch.cycle_count_header_id
358 AND cch.cycle_count_header_name BETWEEN ''' || P_SOURCE_LO || '''
359 AND ''' || P_SOURCE_HI || ''' ');
360 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
361 RETURN ('AND mmt.transaction_source_id = cch.cycle_count_header_id
362 AND cch.cycle_count_header_name >= ''' || P_SOURCE_LO || ''' ');
363 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
364 RETURN ('AND mmt.transaction_source_id = cch.cycle_count_header_id
365 AND cch.cycle_count_header_name <= ''' || P_SOURCE_HI || ''' ');
366 END IF;
367 END IF;
368 END;
369 BEGIN
370 IF P_SOURCE_TYPE_ID = 10 THEN
371 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
372 RETURN ('AND mmt.transaction_source_id = pi.physical_inventory_id
373 AND pi.physical_inventory_name BETWEEN ''' || P_SOURCE_LO || '''
374 AND ''' || P_SOURCE_HI || ''' ');
375 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
376 RETURN ('AND mmt.transaction_source_id = pi.physical_inventory_id
377 AND pi.physical_inventory_name >= ''' || P_SOURCE_LO || ''' ');
378 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
379 RETURN ('AND mmt.transaction_source_id = pi.physical_inventory_id
380 AND pi.physical_inventory_name <= ''' || P_SOURCE_HI || ''' ');
381 END IF;
382 END IF;
383 END;
384 BEGIN
385 IF P_SOURCE_TYPE_ID = 11 THEN
386 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
387 RETURN ('AND mmt.transaction_source_id = cst.cost_update_id
388 AND cst.description BETWEEN ''' || P_SOURCE_LO || ''' AND ''' || P_SOURCE_HI || ''' ');
389 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
390 RETURN ('AND mmt.transaction_source_id = cst.cost_update_id
391 AND cst.description BETWEEN >= ''' || P_SOURCE_LO || ''' ');
392 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
393 RETURN ('AND mmt.transaction_source_id = cst.cost_update_id
394 AND cst.description <= ''' || P_SOURCE_HI || ''' ');
395 END IF;
396 END IF;
397 END;
398 BEGIN
399 IF P_SOURCE_TYPE_ID >= 13 THEN
400 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
401 RETURN ('AND mmt.transaction_source_name BETWEEN ''' || P_SOURCE_LO || '''
402 AND ''' || P_SOURCE_HI || ''' ');
403 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
404 RETURN ('AND mmt.transaction_source_name >= ''' || P_SOURCE_LO || ''' ');
405 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
406 RETURN ('AND mmt.transaction_source_name <= ''' || P_SOURCE_HI || ''' ');
407 END IF;
408 END IF;
409 END;
410 RETURN ' ';
411 END C_SOURCE_WHEREFORMULA;
412
413 FUNCTION WHERE_VALUE RETURN VARCHAR2 IS
414 BEGIN
415 DECLARE
416 HI VARCHAR2(16);
417 LO VARCHAR2(16);
418 BEGIN
419 HI := TO_CHAR(ABS(P_VALUE_HI));
420 LO := TO_CHAR(ABS(P_VALUE_LO));
421 IF P_VALUE_HI IS NOT NULL AND P_VALUE_LO IS NOT NULL THEN
422 RETURN ('AND nvl(nvl(cal.accounted_dr,cal.accounted_cr),0)
423 BETWEEN ''' || LO || ''' AND ''' || HI || '''');
424 ELSIF P_VALUE_HI IS NULL AND P_VALUE_LO IS NOT NULL THEN
425 RETURN ('AND nvl(nvl(cal.accounted_dr,cal.accounted_cr),0)
426 >= ''' || LO || ''' ');
427 ELSIF P_VALUE_HI IS NOT NULL AND P_VALUE_LO IS NULL THEN
428 RETURN ('AND nvl(nvl(cal.accounted_dr,cal.accounted_cr),0)
429 <= ''' || HI || '''');
430 ELSE
431 RETURN (' ');
432 END IF;
433 END;
434 RETURN ' ';
435 END WHERE_VALUE;
436
437 FUNCTION C_WHERE_REASONFORMULA RETURN VARCHAR2 IS
438 BEGIN
439 IF P_REASON_ID IS NOT NULL THEN
440 RETURN ('AND mmt.reason_id = ' || TO_CHAR(P_REASON_ID));
441 ELSE
442 RETURN (' ');
443 END IF;
444 RETURN ' ';
445 END C_WHERE_REASONFORMULA;
446
447 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
448 BEGIN
449 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
450 RETURN (', mtl_item_categories mic, mtl_categories_b mc');
451 ELSE
452 RETURN (', mtl_item_categories mic');
453 END IF;
454 RETURN NULL;
455 END C_FROM_CATFORMULA;
456
457 FUNCTION C_WHERE_CATFORMULA RETURN VARCHAR2 IS
458 BEGIN
459 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
460 RETURN ('AND mmt.inventory_item_id = mic.inventory_item_id
461 AND mmt.organization_id = mic.organization_id
462 AND mic.category_set_id = ' || P_CAT_SET_ID || ' AND mic.category_id = mc.category_id');
463 ELSE
464 RETURN ('AND mmt.inventory_item_id = mic.inventory_item_id
465 AND mmt.organization_id = mic.organization_id
466 AND mic.category_set_id = ' || P_CAT_SET_ID);
467 END IF;
468 RETURN NULL;
469 END C_WHERE_CATFORMULA;
470
471 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
472 BEGIN
473 DECLARE
474 CAT_SET_ID NUMBER;
475 CAT_SET_NAME VARCHAR2(30);
476 BEGIN
477 CAT_SET_ID := P_CAT_SET_ID;
478 SELECT
479 CATEGORY_SET_NAME
480 INTO CAT_SET_NAME
481 FROM
482 MTL_CATEGORY_SETS
483 WHERE CATEGORY_SET_ID = CAT_SET_ID;
484 RETURN (CAT_SET_NAME);
485 EXCEPTION
486 WHEN NO_DATA_FOUND THEN
487 RETURN ('');
488 WHEN OTHERS THEN
489 RETURN ('Error');
490 END;
491 RETURN NULL;
492 END C_CAT_SET_NAMEFORMULA;
493
494 FUNCTION C_TXN_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
495 BEGIN
496 DECLARE
497 TXN_TYPE_ID NUMBER;
498 TXN_TYPE_NAME VARCHAR2(30);
499 BEGIN
500 TXN_TYPE_ID := P_TXN_TYPE_ID;
501 IF TXN_TYPE_ID IS NOT NULL THEN
502 SELECT
503 TRANSACTION_TYPE_NAME
504 INTO TXN_TYPE_NAME
505 FROM
506 MTL_TRANSACTION_TYPES
507 WHERE TRANSACTION_TYPE_ID = TXN_TYPE_ID;
508 RETURN (TXN_TYPE_NAME);
509 ELSE
510 RETURN ('');
511 END IF;
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514 RETURN ('No Data');
515 END;
516 RETURN NULL;
517 END C_TXN_TYPE_NAMEFORMULA;
518
519 FUNCTION C_REASON_NAMEFORMULA RETURN VARCHAR2 IS
520 BEGIN
521 DECLARE
522 TXN_REASON_ID VARCHAR2(20);
523 REASON_NAME VARCHAR2(30);
524 BEGIN
525 TXN_REASON_ID := P_REASON_ID;
526 IF TXN_REASON_ID IS NOT NULL THEN
527 SELECT
528 REASON_NAME
529 INTO REASON_NAME
530 FROM
531 MTL_TRANSACTION_REASONS
532 WHERE REASON_ID = TXN_REASON_ID;
533 RETURN (REASON_NAME);
534 ELSE
535 RETURN ('');
536 END IF;
537 EXCEPTION
538 WHEN NO_DATA_FOUND THEN
539 RETURN ('No Data');
540 END;
541 RETURN NULL;
542 END C_REASON_NAMEFORMULA;
543
544 FUNCTION C_TYPE_OPTIONFORMULA RETURN VARCHAR2 IS
545 BEGIN
546 IF P_TYPE_OPTION = 1 THEN
547 RETURN ('mtst.transaction_source_type_name');
548 ELSE
549 RETURN ('mtt.transaction_type_name');
550 END IF;
551 RETURN NULL;
552 END C_TYPE_OPTIONFORMULA;
553
554 FUNCTION C_FROM_TYPEFORMULA RETURN VARCHAR2 IS
555 BEGIN
556 IF P_TYPE_OPTION = 1 THEN
557 RETURN (', mtl_txn_source_types mtst');
558 ELSE
559 RETURN (', mtl_transaction_types mtt');
560 END IF;
561 RETURN NULL;
562 END C_FROM_TYPEFORMULA;
563
564 FUNCTION C_WHERE_TYPEFORMULA RETURN VARCHAR2 IS
565 BEGIN
566 IF P_TYPE_OPTION = 1 THEN
567 RETURN ('AND mmt.transaction_source_type_id
568 = mtst.transaction_source_type_id');
569 ELSE
570 RETURN ('AND mmt.transaction_type_id = mtt.transaction_type_id');
571 END IF;
572 RETURN NULL;
573 END C_WHERE_TYPEFORMULA;
574
575 FUNCTION AFTERPFORM RETURN BOOLEAN IS
576 BEGIN
577 RETURN (TRUE);
578 END AFTERPFORM;
579
580 FUNCTION C_SOURCE_FROMFORMULA RETURN VARCHAR2 IS
581 BEGIN
582 BEGIN
583 IF P_SOURCE_TYPE_ID = 1 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
584 RETURN (', po_headers_all poh');
585 END IF;
586 IF P_SOURCE_TYPE_ID = 5 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
587 RETURN (', wip_entities wipe');
588 END IF;
589 IF P_SOURCE_TYPE_ID = 7 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
590 RETURN (', po_requisition_headers prh');
591 END IF;
592 IF P_SOURCE_TYPE_ID = 9 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
593 RETURN (', mtl_cycle_count_headers cch');
594 END IF;
595 IF P_SOURCE_TYPE_ID = 10 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
596 RETURN (', mtl_physical_inventories pi');
597 END IF;
598 IF P_SOURCE_TYPE_ID = 11 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
599 RETURN (', cst_cost_updates cst');
600 END IF;
601 END;
602 RETURN ' ';
603 END C_SOURCE_FROMFORMULA;
604
605 FUNCTION C_ACCT_VALUE0_RFORMULA(C_ACCT_VALUE0 IN NUMBER) RETURN NUMBER IS
606 C_ACCT_VALUE0_R NUMBER;
607 BEGIN
608 C_ACCT_VALUE0_R := ROUND(C_ACCT_VALUE0
609 ,CP_PRECISION);
610 RETURN C_ACCT_VALUE0_R;
611 END C_ACCT_VALUE0_RFORMULA;
612
613 FUNCTION VALUE_RFORMULA(VALUE IN NUMBER) RETURN NUMBER IS
614 VALUE_R NUMBER;
615 BEGIN
616 VALUE_R := ROUND(VALUE
617 ,CP_PRECISION);
618 RETURN VALUE_R;
619 END VALUE_RFORMULA;
620
621 FUNCTION C_REPORT_VALUE_RFORMULA(C_REPORT_VALUE IN NUMBER) RETURN NUMBER IS
622 C_REPORT_VALUE_R NUMBER;
623 BEGIN
624 C_REPORT_VALUE_R := ROUND(C_REPORT_VALUE
625 ,CP_PRECISION);
626 RETURN C_REPORT_VALUE_R;
627 END C_REPORT_VALUE_RFORMULA;
628
629 FUNCTION C_ACCT_VALUE1_RFORMULA(C_ACCT_VALUE1 IN NUMBER) RETURN NUMBER IS
630 C_ACCT_VALUE1_R NUMBER;
631 BEGIN
632 C_ACCT_VALUE1_R := ROUND(C_ACCT_VALUE1
633 ,CP_PRECISION);
634 RETURN C_ACCT_VALUE1_R;
635 END C_ACCT_VALUE1_RFORMULA;
636
637 FUNCTION C_ITEM_VALUE2_RFORMULA(C_ITEM_VALUE2 IN NUMBER) RETURN NUMBER IS
638 C_ITEM_VALUE2_R NUMBER;
639 BEGIN
640 C_ITEM_VALUE2_R := ROUND(C_ITEM_VALUE2
641 ,CP_PRECISION);
642 RETURN C_ITEM_VALUE2_R;
643 END C_ITEM_VALUE2_RFORMULA;
644
645 FUNCTION C_REPORT_VALUE1_RFORMULA(C_REPORT_VALUE1 IN NUMBER) RETURN NUMBER IS
646 C_REPORT_VALUE1_R NUMBER;
647 BEGIN
648 C_REPORT_VALUE1_R := ROUND(C_REPORT_VALUE1
649 ,CP_PRECISION);
650 RETURN C_REPORT_VALUE1_R;
651 END C_REPORT_VALUE1_RFORMULA;
652
653 FUNCTION C_REPORT_VALUE2_RFORMULA(C_REPORT_VALUE2 IN NUMBER) RETURN NUMBER IS
654 C_REPORT_VALUE2_R NUMBER;
655 BEGIN
656 C_REPORT_VALUE2_R := ROUND(C_REPORT_VALUE2
657 ,CP_PRECISION);
658 RETURN C_REPORT_VALUE2_R;
659 END C_REPORT_VALUE2_RFORMULA;
660
661 FUNCTION VALUE1_RFORMULA(VALUE1 IN NUMBER) RETURN NUMBER IS
662 VALUE1_R NUMBER;
663 BEGIN
664 VALUE1_R := ROUND(VALUE1
665 ,CP_PRECISION);
666 RETURN VALUE1_R;
667 END VALUE1_RFORMULA;
668
669 FUNCTION VALUE2_RFORMULA(VALUE2 IN NUMBER) RETURN NUMBER IS
670 VALUE2_R NUMBER;
671 BEGIN
672 VALUE2_R := ROUND(VALUE2
673 ,CP_PRECISION);
674 RETURN VALUE2_R;
675 END VALUE2_RFORMULA;
676
677 FUNCTION C_RT_WHERE_CATFORMULA RETURN CHAR IS
678 BEGIN
679 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
680 RETURN ('AND rsl.item_id = mic.inventory_item_id
681 AND cah.organization_id = mic.organization_id
682 AND mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID) || ' AND mic.category_id = mc.category_id');
683 ELSE
684 RETURN ('AND rsl.item_id = mic.inventory_item_id
685 AND cah.organization_id = mic.organization_id
686 AND mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID));
687 END IF;
688 RETURN ' ';
689 END C_RT_WHERE_CATFORMULA;
690
691 FUNCTION C_RT_WHERE_REASONFORMULA RETURN CHAR IS
692 BEGIN
693 IF P_REASON_ID IS NOT NULL THEN
694 RETURN ('AND rt.reason_id = ' || TO_CHAR(P_REASON_ID));
695 ELSE
696 RETURN (' ');
697 END IF;
698 RETURN NULL;
699 END C_RT_WHERE_REASONFORMULA;
700
701 FUNCTION C_RT_TYPE_OPTIONFORMULA RETURN CHAR IS
702 BEGIN
703 IF P_TYPE_OPTION = 1 THEN
704 RETURN ('rt.source_document_code');
705 ELSE
706 RETURN ('rt.transaction_type');
707 END IF;
708 RETURN NULL;
709 END C_RT_TYPE_OPTIONFORMULA;
710
711 FUNCTION C_TXN_QUANTITY(L_AE_LINE_ID IN NUMBER
712 ,L_QUANTITY IN NUMBER) RETURN NUMBER IS
713 L_TXN_QUANTITY NUMBER;
714 L_EVENT_TYPE VARCHAR2(15);
715 L_TRANSACTION_ID NUMBER;
716 L_PO_DISTRIBUTION_ID NUMBER;
717 L_ACCRUAL_QTY NUMBER;
718 L_ENCUM_QTY NUMBER;
719 L_SERVICE_FLAG NUMBER;
720 L_PO_LINE_LOCATION_ID NUMBER;
721 L_SHIPMENT_QTY NUMBER;
722 L_DIST_QTY NUMBER;
723 L_MATCH_OPTION VARCHAR2(1);
724 L_PERIOD_END_DATE DATE;
725 L_RETURN_STATUS VARCHAR2(1);
726 L_MSG_COUNT NUMBER;
727 L_MSG_DATA VARCHAR2(240);
728 L_STMT_NUM NUMBER;
729 PROCESS_ERROR EXCEPTION;
730 BEGIN
731 L_RETURN_STATUS := CST_UTILITY_PUB.GET_RET_STS_SUCCESS;
732 L_STMT_NUM := 10;
733 SELECT
734 CAH.AE_CATEGORY,
735 CAH.ACCOUNTING_EVENT_ID,
736 CAL.PO_DISTRIBUTION_ID
737 INTO L_EVENT_TYPE,L_TRANSACTION_ID,L_PO_DISTRIBUTION_ID
738 FROM
739 CST_AE_HEADERS CAH,
740 CST_AE_LINES CAL
741 WHERE CAL.AE_LINE_ID = L_AE_LINE_ID
742 AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID;
743 IF (L_EVENT_TYPE = 'Accrual') THEN
744 L_STMT_NUM := 20;
745 SELECT
746 PERIOD_END_DATE
747 INTO L_PERIOD_END_DATE
748 FROM
749 CST_PAC_PERIODS CPP
750 WHERE CPP.PAC_PERIOD_ID = P_PERIOD_ID;
751 L_STMT_NUM := 30;
752 SELECT
753 DECODE(POLL.MATCHING_BASIS
754 ,'AMOUNT'
755 ,1
756 ,0),
757 POLL.LINE_LOCATION_ID,
758 DECODE(POLL.MATCHING_BASIS
759 ,'AMOUNT'
760 ,POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED
761 ,0)
762 ,POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED
763 ,0)),
764 DECODE(POLL.MATCHING_BASIS
765 ,'AMOUNT'
766 ,POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
767 ,0)
768 ,POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
769 ,0)),
770 NVL(POLL.MATCH_OPTION
771 ,'P')
772 INTO L_SERVICE_FLAG,L_PO_LINE_LOCATION_ID,L_SHIPMENT_QTY,L_DIST_QTY,L_MATCH_OPTION
773 FROM
774 PO_DISTRIBUTIONS_ALL POD,
775 PO_LINE_LOCATIONS_ALL POLL,
776 PO_LINES_ALL POL
777 WHERE POD.PO_DISTRIBUTION_ID = L_PO_DISTRIBUTION_ID
778 AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
779 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
780 AND NVL(POLL.ACCRUE_ON_RECEIPT_FLAG
781 ,'N') = 'N';
782 L_STMT_NUM := 40;
783 CST_PERENDACCRUALS_PVT.CALCULATE_ACCRUALAMOUNT(P_API_VERSION => 1.0
784 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_FALSE
785 ,P_VALIDATION_LEVEL => 100
786 ,X_RETURN_STATUS => L_RETURN_STATUS
787 ,X_MSG_COUNT => L_MSG_COUNT
788 ,X_MSG_DATA => L_MSG_DATA
789 ,P_MATCH_OPTION => L_MATCH_OPTION
790 ,P_DISTRIBUTION_ID => L_PO_DISTRIBUTION_ID
791 ,P_SHIPMENT_ID => L_PO_LINE_LOCATION_ID
792 ,P_TRANSACTION_ID => L_TRANSACTION_ID
793 ,P_SERVICE_FLAG => L_SERVICE_FLAG
794 ,P_DIST_QTY => L_DIST_QTY
795 ,P_SHIPMENT_QTY => L_SHIPMENT_QTY
796 ,P_END_DATE => L_PERIOD_END_DATE
797 ,X_ACCRUAL_QTY => L_ACCRUAL_QTY
798 ,X_ENCUM_QTY => L_ENCUM_QTY);
799 IF (L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS) THEN
800 RAISE PROCESS_ERROR;
801 END IF;
802 L_STMT_NUM := 50;
803 SELECT
804 DECODE(CAL.ACCOUNTED_DR
805 ,NULL
806 ,-1 * L_ACCRUAL_QTY
807 ,L_ACCRUAL_QTY)
808 INTO L_TXN_QUANTITY
809 FROM
810 CST_AE_LINES CAL
811 WHERE CAL.AE_LINE_ID = L_AE_LINE_ID;
812 ELSE
813 L_STMT_NUM := 100;
814 L_TXN_QUANTITY := L_QUANTITY;
815 END IF;
816 RETURN (L_TXN_QUANTITY);
817 EXCEPTION
818 WHEN PROCESS_ERROR THEN
819 /*SRW.MESSAGE(998
820 ,'Failed calculating accrual quantity :' || TO_CHAR(L_STMT_NUM) || ':' || L_MSG_DATA)*/NULL;
821 RAISE;
822 WHEN OTHERS THEN
823 /*SRW.MESSAGE(999
824 ,'c_txn_quantity : failed calculating transaction quantity :' || TO_CHAR(L_STMT_NUM) || ':' || SUBSTR(SQLERRM
825 ,1
826 ,170))*/NULL;
827 RAISE;
828 END C_TXN_QUANTITY;
829
830 FUNCTION C_QUANTITYFORMULA(AE_LINE_ID IN NUMBER
831 ,QUANTITY IN NUMBER) RETURN NUMBER IS
832 BEGIN
833 /*SRW.REFERENCE(AE_LINE_ID)*/NULL;
834 /*SRW.REFERENCE(QUANTITY)*/NULL;
835 RETURN (C_TXN_QUANTITY(AE_LINE_ID
836 ,QUANTITY));
837 END C_QUANTITYFORMULA;
838
839 FUNCTION C_QUANTITY1FORMULA(AE_LINE_ID1 IN NUMBER
840 ,QUANTITY1 IN NUMBER) RETURN NUMBER IS
841 BEGIN
842 /*SRW.REFERENCE(AE_LINE_ID1)*/NULL;
843 /*SRW.REFERENCE(QUANTITY1)*/NULL;
844 RETURN (C_TXN_QUANTITY(AE_LINE_ID1
845 ,QUANTITY1));
846 END C_QUANTITY1FORMULA;
847
848 FUNCTION C_QUANTITY2FORMULA(AE_LINE_ID2 IN NUMBER
849 ,QUANTITY2 IN NUMBER) RETURN NUMBER IS
850 BEGIN
851 /*SRW.REFERENCE(AE_LINE_ID2)*/NULL;
852 /*SRW.REFERENCE(QUANTITY2)*/NULL;
853 RETURN (C_TXN_QUANTITY(AE_LINE_ID2
854 ,QUANTITY2));
855 END C_QUANTITY2FORMULA;
856
857 FUNCTION CP_APPLICATION_P RETURN VARCHAR2 IS
858 BEGIN
859 RETURN CP_APPLICATION;
860 END CP_APPLICATION_P;
861
862 FUNCTION CP_COST_GROUP_P RETURN VARCHAR2 IS
863 BEGIN
864 RETURN CP_COST_GROUP;
865 END CP_COST_GROUP_P;
866
867 FUNCTION CP_COST_TYPE_P RETURN VARCHAR2 IS
868 BEGIN
869 RETURN CP_COST_TYPE;
870 END CP_COST_TYPE_P;
871
872 FUNCTION CP_LEGAL_ENTITY_P RETURN VARCHAR2 IS
873 BEGIN
874 RETURN CP_LEGAL_ENTITY;
875 END CP_LEGAL_ENTITY_P;
876
877 FUNCTION CP_PERIOD_NAME_P RETURN VARCHAR2 IS
878 BEGIN
879 RETURN CP_PERIOD_NAME;
880 END CP_PERIOD_NAME_P;
881
882 FUNCTION CP_REQUESTED_BY_P RETURN VARCHAR2 IS
883 BEGIN
884 RETURN CP_REQUESTED_BY;
885 END CP_REQUESTED_BY_P;
886
887 FUNCTION CP_REQUEST_TIME_P RETURN VARCHAR2 IS
888 BEGIN
889 RETURN CP_REQUEST_TIME;
890 END CP_REQUEST_TIME_P;
891
892 FUNCTION CP_RESPONSIBILITY_P RETURN VARCHAR2 IS
893 BEGIN
894 RETURN CP_RESPONSIBILITY;
895 END CP_RESPONSIBILITY_P;
896
897 FUNCTION CP_PRECISION_P RETURN NUMBER IS
898 BEGIN
899 RETURN CP_PRECISION;
900 END CP_PRECISION_P;
901
902 FUNCTION CP_SORT_BY_COV_P RETURN VARCHAR2 IS
903 BEGIN
904 RETURN CP_SORT_BY_COV;
905 END CP_SORT_BY_COV_P;
906
907 FUNCTION CP_TYPE_OPTION_COV_P RETURN VARCHAR2 IS
908 BEGIN
909 RETURN CP_TYPE_OPTION_COV;
910 END CP_TYPE_OPTION_COV_P;
911
912 FUNCTION CP_EXT_PRECISION_P RETURN NUMBER IS
913 BEGIN
914 RETURN CP_EXT_PRECISION;
915 END CP_EXT_PRECISION_P;
916
917 END BOM_CSTRPMDD_XMLP_PKG;
918
919
920
[Home] [Help]