1 PACKAGE BODY WIP_WIPDJLIS_XMLP_PKG AS
2 /* $Header: WIPDJLISB.pls 120.1 2008/01/31 12:17:26 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 SELECT fifst.id_flex_num
8 into p_item_flex_num
9 FROM fnd_id_flex_structures fifst
10 WHERE fifst.application_id = 401
11 AND fifst.id_flex_code = 'MSTK'
12 AND fifst.enabled_flag = 'Y'
13 AND fifst.freeze_flex_definition_flag = 'Y'
14 and rownum<2;
15 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
16 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
17 APPL_SHORT_NAME="INV" OUTPUT=":P_ITEM_DATA"
18 MODE="SELECT" DISPLAY="ALL" TABLEALIAS="SI"')*/NULL;
19 IF (P_FROM_ASSEMBLY IS NOT NULL) THEN
20 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
21 NULL;
22 ELSE
23 NULL;
24 END IF;
25 ELSE
26 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
27 NULL;
28 END IF;
29 END IF;
30 RETURN TRUE;
31 END;
32 RETURN (TRUE);
33 END BEFOREREPORT;
34
35 FUNCTION AFTERREPORT RETURN BOOLEAN IS
36 BEGIN
37 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
38 RETURN (TRUE);
39 END AFTERREPORT;
40
41 FUNCTION DATE_LIMITER RETURN CHARACTER IS
42 LIMIT_DATES VARCHAR2(120);
43 BEGIN
44 IF (P_FROM_START_DATE IS NOT NULL) THEN
45 IF (P_TO_START_DATE IS NOT NULL) THEN
46 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) BETWEEN TO_DATE(''' || TO_CHAR(P_FROM_START_DATE
47 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND TO_DATE(''' || TO_CHAR(P_TO_START_DATE
48 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
49 ELSE
50 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) >= TO_DATE(''' || TO_CHAR(P_FROM_START_DATE
51 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
52 END IF;
53 ELSE
54 IF (P_TO_START_DATE IS NOT NULL) THEN
55 LIMIT_DATES := ' AND TRUNC(DJ.SCHEDULED_START_DATE) <= TO_DATE(''' || TO_CHAR(P_TO_START_DATE
56 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
57 ELSE
58 LIMIT_DATES := ' ';
59 END IF;
60 END IF;
61 RETURN (LIMIT_DATES);
62 END DATE_LIMITER;
63
64 FUNCTION JOB_LIMITER RETURN CHARACTER IS
65 LIMIT_JOBS VARCHAR2(768);
66 BEGIN
67 IF (P_FROM_JOB IS NOT NULL) THEN
68 IF (P_TO_JOB IS NOT NULL) THEN
69 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME BETWEEN ''' || REPLACE(P_FROM_JOB
70 ,''''
71 ,'''''') || ''' AND ''' || REPLACE(P_TO_JOB
72 ,''''
73 ,'''''') || '''';
74 ELSE
75 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME >= ''' || REPLACE(P_FROM_JOB
76 ,''''
77 ,'''''') || '''';
78 END IF;
79 ELSE
80 IF (P_TO_JOB IS NOT NULL) THEN
81 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME <= ''' || REPLACE(P_TO_JOB
82 ,''''
83 ,'''''') || '''';
84 ELSE
85 LIMIT_JOBS := ' ';
86 END IF;
87 END IF;
88 RETURN (LIMIT_JOBS);
89 END JOB_LIMITER;
90
91 FUNCTION DYNAMIC_ORDER_BY RETURN CHARACTER IS
92 ORDER_BY_CLAUSE VARCHAR2(100);
93 BEGIN
94 IF (P_SORT_CODE = 10) THEN
95 ORDER_BY_CLAUSE := 'TRUNC(DJ.SCHEDULED_START_DATE)';
96 ELSIF (P_SORT_CODE = 11) THEN
97 ORDER_BY_CLAUSE := 'WE.WIP_ENTITY_NAME';
98 ELSIF (P_SORT_CODE = 1) THEN
99 ORDER_BY_CLAUSE := P_ITEM_ORDER;
100 ELSIF (P_SORT_CODE = 14) THEN
101 ORDER_BY_CLAUSE := 'SG.SCHEDULE_GROUP_NAME, DJ.BUILD_SEQUENCE';
102 END IF;
103 RETURN (ORDER_BY_CLAUSE);
104 END DYNAMIC_ORDER_BY;
105
106 FUNCTION C_STATUS_LIMITERFORMULA RETURN VARCHAR2 IS
107 BEGIN
108 IF (P_STATUS IS NOT NULL) THEN
109 RETURN ('AND DJ.STATUS_TYPE = ''' || P_STATUS || '''');
110 ELSE
111 RETURN (' ');
112 END IF;
113 RETURN NULL;
114 END C_STATUS_LIMITERFORMULA;
115
116 FUNCTION AFTERPFORM RETURN BOOLEAN IS
117 BEGIN
118 IF P_FROM_ASSEMBLY IS NOT NULL OR P_TO_ASSEMBLY IS NOT NULL THEN
119 P_OUTER := ' ';
120 P_ITEM_ORG := ' AND SI.ORGANIZATION_ID = :ORG_ID';
121 END IF;
122 IF P_SCHEDULE_GROUP_FROM IS NOT NULL OR P_SCHEDULE_GROUP_TO IS NOT NULL THEN
123 P_SG_OUTER := ' ';
124 END IF;
125 RETURN (TRUE);
126 END AFTERPFORM;
127
128 FUNCTION C_LIMITER RETURN VARCHAR2 IS
129 C_OUT VARCHAR2(200);
130 BEGIN
131 IF P_SCHEDULE_GROUP_FROM IS NOT NULL THEN
132 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
133 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME BETWEEN ''' || P_SCHEDULE_GROUP_FROM || ''' AND ''' || P_SCHEDULE_GROUP_TO || '''';
134 ELSE
135 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME >= ''' || P_SCHEDULE_GROUP_FROM || '''';
136 END IF;
137 ELSE
138 IF P_SCHEDULE_GROUP_TO IS NOT NULL THEN
139 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME <= ''' || P_SCHEDULE_GROUP_TO || '''';
140 ELSE
141 C_OUT := ' ';
142 END IF;
143 END IF;
144 RETURN (C_OUT);
145 END C_LIMITER;
146
147 END WIP_WIPDJLIS_XMLP_PKG;
148
149