1 PACKAGE BODY GMI_ICR04_XMLP_PKG AS
2 /* $Header: ICR04B.pls 120.1 2007/12/27 12:27:15 nchinnam noship $ */
3 FUNCTION YTD_USAGECFFORMULA(DOC_TYPE_1 IN VARCHAR2
4 ,REASON_CODE_1 IN VARCHAR2
5 ,ITEM_ID_1 IN NUMBER
6 ,WHSE_CODE_1 IN VARCHAR2) RETURN NUMBER IS
7 COMPLETED_TRANS_QTY NUMBER(10,2) := 0.00;
8 REAS_TRANS_QTY NUMBER(10,2) := 0.00;
9 REASP_TRANS_QTY NUMBER(10,2) := 0.00;
10 PENDING_TRANS_QTY NUMBER(10,2) := 0.00;
11 YTD_USAGE NUMBER(12,2) := 0.00;
12 BEGIN
13 SELECT
14 SUM(T.TRANS_QTY)
15 INTO COMPLETED_TRANS_QTY
16 FROM
17 IC_TRAN_CMP T,
18 PM_MATL_DTL M
19 WHERE DOC_TYPE_1 in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
20 AND REASON_CODE_1 in (
21 SELECT
22 REASON_CODE
23 FROM
24 SY_REAS_CDS
25 WHERE FLOW_TYPE = 0 )
26 AND T.TRANS_DATE >= FROM_DATE
27 AND T.TRANS_DATE <= TO_DATE
28 AND T.DOC_TYPE = 'PROD'
29 AND M.LINE_TYPE = - 1
30 AND T.DOC_ID = M.BATCH_ID
31 AND T.LINE_ID = M.LINE_ID
32 AND T.ITEM_ID = ITEM_ID_1
33 AND T.WHSE_CODE = WHSE_CODE_1
34 AND DOC_TYPE_INCP is null
35 AND DOC_TYPE_OUTCP is null;
36 IF SQL%NOTFOUND THEN
37 COMPLETED_TRANS_QTY := 0;
38 END IF;
39 SELECT
40 SUM(T.TRANS_QTY)
41 INTO REAS_TRANS_QTY
42 FROM
43 IC_TRAN_CMP T,
44 SY_REAS_CDS R
45 WHERE T.TRANS_DATE >= FROM_DATE
46 AND T.TRANS_DATE <= TO_DATE
47 AND R.FLOW_TYPE = 0
48 AND T.REASON_CODE = R.REASON_CODE
49 AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
50 AND T.ITEM_ID = ITEM_ID_1
51 AND T.WHSE_CODE = WHSE_CODE_1
52 AND DOC_TYPE_INCP is null
53 AND DOC_TYPE_OUTCP is null
54 AND R.DELETE_MARK = 0;
55 IF SQL%NOTFOUND THEN
56 REAS_TRANS_QTY := 0;
57 END IF;
58 SELECT
59 SUM(T.TRANS_QTY)
60 INTO PENDING_TRANS_QTY
61 FROM
62 IC_TRAN_PND T,
63 PM_MATL_DTL M
64 WHERE T.TRANS_DATE >= FROM_DATE
65 AND T.TRANS_DATE <= TO_DATE
66 AND T.DOC_TYPE = 'PROD'
67 AND M.LINE_TYPE = - 1
68 AND T.DOC_ID = M.BATCH_ID
69 AND T.LINE_ID = M.LINE_ID
70 AND T.ITEM_ID = ITEM_ID_1
71 AND T.WHSE_CODE = WHSE_CODE_1
72 AND DOC_TYPE_INCP is null
73 AND DOC_TYPE_OUTCP is null
74 AND T.DELETE_MARK = 0
75 AND T.COMPLETED_IND = 1;
76 IF SQL%NOTFOUND THEN
77 PENDING_TRANS_QTY := 0;
78 END IF;
79 SELECT
80 SUM(T.TRANS_QTY)
81 INTO REASP_TRANS_QTY
82 FROM
83 IC_TRAN_PND T,
84 SY_REAS_CDS R
85 WHERE T.TRANS_DATE >= FROM_DATE
86 AND T.TRANS_DATE <= TO_DATE
87 AND R.FLOW_TYPE = 0
88 AND T.REASON_CODE = R.REASON_CODE
89 AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
90 AND T.ITEM_ID = ITEM_ID_1
91 AND T.WHSE_CODE = WHSE_CODE_1
92 AND DOC_TYPE_INCP is null
93 AND DOC_TYPE_OUTCP is null
94 AND R.DELETE_MARK = 0
95 AND T.DELETE_MARK = 0
96 AND T.COMPLETED_IND = 1;
97 IF SQL%NOTFOUND THEN
98 REASP_TRANS_QTY := 0;
99 END IF;
100 YTD_USAGE := COMPLETED_TRANS_QTY + PENDING_TRANS_QTY + REAS_TRANS_QTY + REASP_TRANS_QTY;
101 RETURN (YTD_USAGE);
102 END YTD_USAGECFFORMULA;
103
104 FUNCTION ACT_USAGEFORMULA(DOC_TYPE_1 IN VARCHAR2
105 ,REASON_CODE_1 IN VARCHAR2
106 ,ITEM_ID_1 IN NUMBER
107 ,WHSE_CODE_1 IN VARCHAR2) RETURN NUMBER IS
108 COMPLETED_TRANS_QTY NUMBER(10,2) := 0.00;
109 REAS_TRANS_QTY NUMBER(10,2) := 0.00;
110 REASP_TRANS_QTY NUMBER(10,2) := 0.00;
111 PENDING_TRANS_QTY NUMBER(10,2) := 0.00;
112 ACTUAL_USAGE NUMBER(12,2) := 0.00;
113 BEGIN
114 SELECT
115 SUM(T.TRANS_QTY)
116 INTO COMPLETED_TRANS_QTY
117 FROM
118 IC_TRAN_CMP T,
119 PM_MATL_DTL M
120 WHERE DOC_TYPE_1 in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
121 AND REASON_CODE_1 in (
122 SELECT
123 REASON_CODE
124 FROM
125 SY_REAS_CDS
126 WHERE FLOW_TYPE = 0 )
127 AND T.TRANS_DATE >= (
128 SELECT
129 BEGIN_DATE
130 FROM
131 IC_CLDR_HDR
132 WHERE ORGN_CODE = T.ORGN_CODE
133 AND FISCAL_YEAR = FISCAL_YEAR )
134 AND T.TRANS_DATE <= TO_DATE
135 AND T.DOC_TYPE = 'PROD'
136 AND M.LINE_TYPE = - 1
137 AND T.DOC_ID = M.BATCH_ID
138 AND T.LINE_ID = M.LINE_ID
139 AND T.ITEM_ID = ITEM_ID_1
140 AND T.WHSE_CODE = WHSE_CODE_1
141 AND DOC_TYPE_INCP is null
142 AND DOC_TYPE_OUTCP is null;
143 IF SQL%NOTFOUND THEN
144 COMPLETED_TRANS_QTY := 0;
145 END IF;
146 SELECT
147 SUM(T.TRANS_QTY)
148 INTO REAS_TRANS_QTY
149 FROM
150 IC_TRAN_CMP T,
151 SY_REAS_CDS R
152 WHERE T.TRANS_DATE >= (
153 SELECT
154 BEGIN_DATE
155 FROM
156 IC_CLDR_HDR
157 WHERE ORGN_CODE = T.ORGN_CODE
158 AND FISCAL_YEAR = FISCAL_YEAR )
159 AND T.TRANS_DATE <= TO_DATE
160 AND R.FLOW_TYPE = 0
161 AND T.REASON_CODE = R.REASON_CODE
162 AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
163 AND T.ITEM_ID = ITEM_ID_1
164 AND T.WHSE_CODE = WHSE_CODE_1
165 AND DOC_TYPE_INCP is null
166 AND DOC_TYPE_OUTCP is null
167 AND R.DELETE_MARK = 0;
168 IF SQL%NOTFOUND THEN
169 REAS_TRANS_QTY := 0;
170 END IF;
171 SELECT
172 SUM(T.TRANS_QTY)
173 INTO PENDING_TRANS_QTY
174 FROM
175 IC_TRAN_PND T,
176 PM_MATL_DTL M
177 WHERE T.TRANS_DATE >= (
178 SELECT
179 BEGIN_DATE
180 FROM
181 IC_CLDR_HDR
182 WHERE ORGN_CODE = T.ORGN_CODE
183 AND FISCAL_YEAR = FISCAL_YEAR )
184 AND T.TRANS_DATE <= TO_DATE
185 AND T.DOC_TYPE = 'PROD'
186 AND M.LINE_TYPE = - 1
187 AND T.DOC_ID = M.BATCH_ID
188 AND T.LINE_ID = M.LINE_ID
189 AND T.ITEM_ID = ITEM_ID_1
190 AND T.WHSE_CODE = WHSE_CODE_1
191 AND DOC_TYPE_INCP is null
192 AND DOC_TYPE_OUTCP is null
193 AND T.DELETE_MARK = 0
194 AND T.COMPLETED_IND = 1;
195 IF SQL%NOTFOUND THEN
196 PENDING_TRANS_QTY := 0;
197 END IF;
198 SELECT
199 SUM(T.TRANS_QTY)
200 INTO REASP_TRANS_QTY
201 FROM
202 IC_TRAN_PND T,
203 SY_REAS_CDS R
204 WHERE T.TRANS_DATE >= (
205 SELECT
206 BEGIN_DATE
207 FROM
208 IC_CLDR_HDR
209 WHERE ORGN_CODE = T.ORGN_CODE
210 AND FISCAL_YEAR = FISCAL_YEAR )
211 AND T.TRANS_DATE <= TO_DATE
212 AND R.FLOW_TYPE = 0
213 AND T.REASON_CODE = R.REASON_CODE
214 AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
215 AND T.ITEM_ID = ITEM_ID_1
216 AND T.WHSE_CODE = WHSE_CODE_1
217 AND DOC_TYPE_INCP is null
218 AND DOC_TYPE_OUTCP is null
219 AND R.DELETE_MARK = 0
220 AND T.DELETE_MARK = 0
221 AND T.COMPLETED_IND = 1;
222 IF SQL%NOTFOUND THEN
223 REASP_TRANS_QTY := 0;
224 END IF;
225 ACTUAL_USAGE := COMPLETED_TRANS_QTY + PENDING_TRANS_QTY + REAS_TRANS_QTY + REASP_TRANS_QTY;
226 RETURN (ACTUAL_USAGE);
227 END ACT_USAGEFORMULA;
228
229 FUNCTION YTD_VALUECFFORMULA RETURN NUMBER IS
230 BEGIN
231 RETURN (0);
232 END YTD_VALUECFFORMULA;
233
234 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
235 BEGIN
236 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
237 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
238
239 RETURN (TRUE);
240 END BEFOREREPORT;
241
242 PROCEDURE HEADER IS
243 BEGIN
244 NULL;
245 END HEADER;
246 function R_Daily_Item_Usage(doc_type_1 varchar2,reason_code_1 varchar2) return varchar2 is
247 return_flag varchar2(4);
248 begin
249 if doc_type_1 in ('ADJI','ADJR') then
250 select 'TRUE' into return_flag from dual
251 where reason_code_1 in (select reason_code from sy_reas_cds where flow_type in (1,-1));
252 end if;
253 return('true');
254 exception
255 when no_data_found then
256 return('false');
257 end;
258 function F_Doc_Type_InCP(doc_type_1 varchar2,reason_code_1 varchar2,line_id_1 number,doc_id_1 number,quantity_1 number) return varchar2 is
259 return_flag varchar2(4);
260 begin
261 select 'TRUE' into return_flag from dual
262 where doc_type_1 in ('PORD','RECV','CREI','CRER','FPO','REQ')
263 or (doc_type_1 in ('TRNI','TRNR')and quantity_1 > 0)
264 or (doc_type_1 ='PROD' and line_id_1 in (select line_id from pm_matl_dtl
265 where line_type in (1,2) and batch_id = doc_id_1 and line_id=line_id_1))
266 or (doc_type_1 in ('ADJI','ADJR','PICY','PIPH','REPI','REPR') and reason_code_1
267 in (select reason_code from sy_reas_cds where flow_type=1)) ;
268 return ('TRUE');
269 RETURN NULL; exception when no_data_found then
270 return('FALSE');
271 end;
272 function F_Doc_Type_OutCP(doc_type_1 varchar2,reason_code_1 varchar2,line_id_1 number,doc_id_1 number,quantity_1 number) return varchar2 is
273 return_flag varchar2(4);
274 return_flag1 varchar2(4);
275 begin
276 select 'TRUE' into return_flag from dual
277 where doc_type_1 in ('OPCR','OPSO','OPSP')
278 or (doc_type_1 in ('TRNI','TRNR','MTRI')and quantity_1 < 0)
279 or (doc_type_1 ='PROD' and line_id_1 in (select line_id from pm_matl_dtl
280 where line_type =-1 and batch_id = doc_id_1 and line_id=line_id_1))
281 or (doc_type_1 in ('ADJI','ADJR','PICY','PIPH','REPI','REPR') and reason_code_1
282 in (select reason_code from sy_reas_cds where flow_type=-1)) ;
283 if sql%found then
284 return ('TRUE');
285 else
286 return('FALSE');
287 end if;
288 RETURN NULL; exception when no_data_found then
289 return('FALSE');
290
291 end;
292
293 FUNCTION AFTERREPORT RETURN BOOLEAN IS
294 BEGIN
295 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
296 RETURN (TRUE);
297 END AFTERREPORT;
298
299 FUNCTION DOC_TYPE_INCP_P RETURN VARCHAR2 IS
300 BEGIN
301 RETURN DOC_TYPE_INCP;
302 END DOC_TYPE_INCP_P;
303
304 FUNCTION REASON_CODE_INCP_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN REASON_CODE_INCP;
307 END REASON_CODE_INCP_P;
308
309 FUNCTION QUANTITY_INCP_P RETURN NUMBER IS
310 BEGIN
311 RETURN QUANTITY_INCP;
312 END QUANTITY_INCP_P;
313
314 FUNCTION DOC_TYPE_OUTCP_P RETURN VARCHAR2 IS
315 BEGIN
316 RETURN DOC_TYPE_OUTCP;
317 END DOC_TYPE_OUTCP_P;
318
319 FUNCTION REASON_CODE_OUTCP_P RETURN VARCHAR2 IS
320 BEGIN
321 RETURN REASON_CODE_OUTCP;
322 END REASON_CODE_OUTCP_P;
323
324 FUNCTION QUANTITY_OUTCP_P RETURN NUMBER IS
325 BEGIN
326 RETURN QUANTITY_OUTCP;
327 END QUANTITY_OUTCP_P;
328
329 END GMI_ICR04_XMLP_PKG;
330
[Home] [Help]