1 PACKAGE BODY WIP_WIPREORQ_XMLP_PKG AS
2 /* $Header: WIPREORQB.pls 120.1 2008/01/31 12:38:11 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 QTY:=get_precision(P_qty_precision);
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
9 APPL_SHORT_NAME="INV" OUTPUT=":P_ASSY"
10 TABLEALIAS="MSI"
11 MODE="SELECT" DISPLAY="ALL"')*/NULL;
12 IF (P_FROM_COMP IS NOT NULL) THEN
13 IF (P_TO_COMP IS NOT NULL) THEN
14 NULL;
15 ELSE
16 NULL;
17 END IF;
18 ELSE
19 IF (P_TO_COMP IS NOT NULL) THEN
20 NULL;
21 END IF;
22 END IF;
23 RETURN (TRUE);
24 END BEFOREREPORT;
25
26 FUNCTION AFTERREPORT RETURN BOOLEAN IS
27 BEGIN
28 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
29 RETURN (TRUE);
30 END AFTERREPORT;
31
32 FUNCTION LIMIT_LINES RETURN CHARACTER IS
33 LIMIT_LINES VARCHAR2(80);
34 BEGIN
35 IF (P_FROM_LINE IS NOT NULL) THEN
36 IF (P_TO_LINE IS NOT NULL) THEN
37 LIMIT_LINES := ' AND WL.LINE_CODE BETWEEN ''' || P_FROM_LINE || ''' AND ''' || P_TO_LINE || ''' ';
38 ELSE
39 LIMIT_LINES := ' AND WL.LINE_CODE >= ''' || P_FROM_LINE || ''' ';
40 END IF;
41 ELSE
42 IF (P_TO_LINE IS NOT NULL) THEN
43 LIMIT_LINES := ' AND WL.LINE_CODE <= ''' || P_TO_LINE || ''' ';
44 ELSE
45 LIMIT_LINES := ' ';
46 END IF;
47 END IF;
48 RETURN (LIMIT_LINES);
49 END LIMIT_LINES;
50
51 FUNCTION OPEN_SHORT_LIMITER RETURN CHARACTER IS
52 QTY_OPEN_SHORT VARCHAR2(1000);
53 BEGIN
54 IF (P_AHEAD_BEHIND = 1) THEN
55 QTY_OPEN_SHORT := 'HAVING (SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,
56 :C_END_NUM )
57 - GREATEST(BCD.NEXT_SEQ_NUM, :C_START_NUM ))
58 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
59 + SUM(GREATEST((:C_START_NUM-BCD.NEXT_SEQ_NUM),0)
60 *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE)
61 - SUM(WRO.QUANTITY_ISSUED)) > 0 ';
62 ELSE
63 QTY_OPEN_SHORT := 'HAVING (SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,:C_END_NUM)
64 - GREATEST(BCD.NEXT_SEQ_NUM,:C_START_NUM))
65 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
66 - SUM(WRO.QUANTITY_ISSUED)) > 0 ';
67 END IF;
68 RETURN (QTY_OPEN_SHORT);
69 END OPEN_SHORT_LIMITER;
70
71 FUNCTION OPEN_SHORTFORMULA RETURN NUMBER IS
72 BEGIN
73 RETURN (1);
74 END OPEN_SHORTFORMULA;
75
76 FUNCTION ASSEMBLY_LIMITER RETURN CHARACTER IS
77 LIMIT_ASSLY VARCHAR2(200);
78 BEGIN
79 IF (P_FROM_ASSEMBLY IS NOT NULL) THEN
80 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
81 LIMIT_ASSLY := ' AND WE.WIP_ENTITY_NAME BETWEEN ''' || P_FROM_ASSEMBLY || ''' AND ''' || P_TO_ASSEMBLY || '''';
82 ELSE
83 LIMIT_ASSLY := ' AND WE.WIP_ENTITY_NAME >= ''' || P_FROM_ASSEMBLY || '''';
84 END IF;
85 ELSE
86 IF (P_TO_ASSEMBLY IS NOT NULL) THEN
87 LIMIT_ASSLY := ' AND WE.WIP_ENTITY_NAME <= ''' || P_TO_ASSEMBLY || '''';
88 ELSE
89 LIMIT_ASSLY := ' ';
90 END IF;
91 END IF;
92 RETURN (LIMIT_ASSLY);
93 END ASSEMBLY_LIMITER;
94
95 FUNCTION C_NETTABLE_CONSTRAINTFORMULA RETURN VARCHAR2 IS
96 BEGIN
97 IF (P_NETTABLE = 1) THEN
98 RETURN ('AND MSS.AVAILABILITY_TYPE(+) = 1');
99 ELSE
100 RETURN (' ');
101 END IF;
102 RETURN NULL;
103 END C_NETTABLE_CONSTRAINTFORMULA;
104
105 FUNCTION C_QTY_SHORT_QOHFORMULA(NET_QTY_COMP IN NUMBER
106 ,C_ITEM_TOTAL_QTY IN NUMBER
107 ,C_QTY_SHORT_FORMULA IN NUMBER
108 ,C_OPEN_REQUIREMENTS IN VARCHAR2
109 ,AVAIL_QTY IN NUMBER) RETURN NUMBER IS
110 BEGIN
111 /*SRW.REFERENCE(NET_QTY_COMP)*/NULL;
112 /*SRW.REFERENCE(C_ITEM_TOTAL_QTY)*/NULL;
113 /*SRW.REFERENCE(P_NETTABLE)*/NULL;
114 IF (P_OPEN_SHORT = 1) THEN
115 RETURN (C_QTY_SHORT_FORMULA - C_OPEN_REQUIREMENTS) * -1;
116 ELSE
117 RETURN -1 * (AVAIL_QTY - C_OPEN_REQUIREMENTS);
118 END IF;
119 RETURN NULL;
120 END C_QTY_SHORT_QOHFORMULA;
121
122 FUNCTION C_TOTAL_QTY_FORMULAFORMULA(C_TOTAL_QTY IN NUMBER
123 ,SUPPLY_FLAG IN NUMBER) RETURN NUMBER IS
124 BEGIN
125 RETURN C_TOTAL_QTY * SUPPLY_FLAG;
126 END C_TOTAL_QTY_FORMULAFORMULA;
127
128 FUNCTION C_QTY_SHORT_SUB_LOCFORMULA(C_QTY_SHORT_FORMULA IN NUMBER
129 ,QTY_OPEN IN NUMBER) RETURN NUMBER IS
130 BEGIN
131 RETURN (C_QTY_SHORT_FORMULA - QTY_OPEN) * -1;
132 END C_QTY_SHORT_SUB_LOCFORMULA;
133
134 FUNCTION NET_QTY_DETAILFORMULA(AVAILABILITY_TYPE IN NUMBER
135 ,QUANTITY_ON_HAND IN NUMBER) RETURN NUMBER IS
136 BEGIN
137 RETURN (AVAILABILITY_TYPE * QUANTITY_ON_HAND);
138 END NET_QTY_DETAILFORMULA;
139
140 FUNCTION AVAIL_QTYFORMULA(NET_QTY_COMP IN NUMBER
141 ,C_ITEM_TOTAL_QTY IN NUMBER) RETURN NUMBER IS
142 BEGIN
143 IF (P_NETTABLE = 1) THEN
144 RETURN (NET_QTY_COMP);
145 ELSE
146 RETURN (C_ITEM_TOTAL_QTY);
147 END IF;
148 RETURN NULL;
149 END AVAIL_QTYFORMULA;
150
151 FUNCTION C_LIMIT_COMPONENTSFORMULA RETURN VARCHAR2 IS
152 BEGIN
153 BEGIN
154 IF (P_FLEXWHERE IS NOT NULL) THEN
155 RETURN ('AND ');
156 ELSE
157 RETURN (' ');
158 END IF;
159 END;
160 RETURN NULL;
161 END C_LIMIT_COMPONENTSFORMULA;
162
163 FUNCTION ZEROFORMULA RETURN NUMBER IS
164 BEGIN
165 RETURN (0);
166 END ZEROFORMULA;
167
168 FUNCTION C_COMPONENT_DISP_SORTFORMULA(C_COMPONENT_DISP_SORT IN VARCHAR2) RETURN VARCHAR2 IS
169 BEGIN
170 RETURN (C_COMPONENT_DISP_SORT);
171 END C_COMPONENT_DISP_SORTFORMULA;
172
173 FUNCTION AFTERPFORM RETURN BOOLEAN IS
174 BEGIN
175 IF P_FROM_COMP IS NOT NULL OR P_TO_COMP IS NOT NULL THEN
176 P_ITEM_ORG := 'AND MSI2.ORGANIZATION_ID = :P_ORGANIZATION_ID';
177 END IF;
178 RETURN (TRUE);
179 END AFTERPFORM;
180
181 FUNCTION LIMIT_BULK_SUPPLY_TYPE RETURN CHARACTER IS
182 LIMIT_BULK_SUPPLY_TYPE VARCHAR2(50);
183 BEGIN
184 IF (P_INCLUDE_BULK = 1) THEN
185 LIMIT_BULK_SUPPLY_TYPE := ' ';
186 ELSE
187 LIMIT_BULK_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 4 ';
188 END IF;
189 RETURN (LIMIT_BULK_SUPPLY_TYPE);
190 END LIMIT_BULK_SUPPLY_TYPE;
191
192 FUNCTION LIMIT_VENDOR_SUPPLY_TYPE RETURN CHARACTER IS
193 LIMIT_VENDOR_SUPPLY_TYPE VARCHAR2(50);
194 BEGIN
195 IF (P_INCLUDE_VENDOR = 1) THEN
196 LIMIT_VENDOR_SUPPLY_TYPE := ' ';
197 ELSE
198 LIMIT_VENDOR_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 5 ';
199 END IF;
200 RETURN (LIMIT_VENDOR_SUPPLY_TYPE);
201 END LIMIT_VENDOR_SUPPLY_TYPE;
202
203 function get_precision(qty_precision in number) return VARCHAR2 is
204 begin
205
206 if qty_precision = 0 then return('999G999G999G990');
207
208 elsif qty_precision = 1 then return('999G999G999G990D0');
209
210 elsif qty_precision = 3 then return('999G999G999G990D000');
211
212 elsif qty_precision = 4 then return('999G999G999G990D0000');
213
214 elsif qty_precision = 5 then return('999G999G999G990D00000');
215
216 elsif qty_precision = 6 then return('999G999G999G990D000000');
217
218 else return('999G999G999G990D00');
219
220 end if;
221
222 end;
223
224 END WIP_WIPREORQ_XMLP_PKG;
225
[Home] [Help]