1 PACKAGE BODY GML_GMLUNALC_XMLP_PKG AS
2 /* $Header: GMLUNALCB.pls 120.0 2007/12/24 13:19:58 nchinnam noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 SORT4 VARCHAR2(20);
5 BEGIN
6 P_DEFAULT_LOCATION := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
7 PARAM_WHERE_CLAUSE := ' ';
8 IF (P_FROM_WHSE IS NOT NULL) THEN
9 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and whse.whse_code >= :p_from_whse ';
10 END IF;
11 IF (P_TO_WHSE IS NOT NULL) THEN
12 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and whse.whse_code <= :p_to_whse ';
13 END IF;
14 IF (P_FROM_ORDER_NO IS NOT NULL) THEN
15 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and HDR.ORDER_NUMBER >= :p_from_order_no ';
16 END IF;
17 IF (P_TO_ORDER_NO IS NOT NULL) THEN
18 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and HDR.ORDER_NUMBER <= :p_to_order_no ';
19 END IF;
20 IF (P_FROM_ITEM_NO IS NOT NULL) THEN
21 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and itm.item_no >= :p_from_item_no ';
22 END IF;
23 IF (P_TO_ITEM_NO IS NOT NULL) THEN
24 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and itm.item_no <= :p_to_item_no ';
25 END IF;
26 IF (P_FROM_CUST_NO IS NOT NULL) THEN
27 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and dtl.ship_to_org_id >= :p_from_cust_no ';
28 END IF;
29 IF (P_TO_CUST_NO IS NOT NULL) THEN
30 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and dtl.ship_to_org_id <= :p_to_cust_no ';
31 END IF;
32 IF (P_FROM_SHIPDATE IS NOT NULL) THEN
33 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(DTL.SCHEDULE_SHIP_DATE) >= TRUNC(:p_from_shipdate) ';
34 END IF;
35 IF (P_TO_SHIPDATE IS NOT NULL) THEN
36 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(DTL.SCHEDULE_SHIP_DATE) <= TRUNC(:p_to_shipdate) ';
37 END IF;
38 P_SORT := ' ';
39 IF P_SORT_1 = '5' THEN
40 P_SORT := P_SORT || ',WHSE_CODE';
41 ELSIF P_SORT_1 = '1' THEN
42 P_SORT := P_SORT || ',ITEM_NO';
43 ELSIF P_SORT_1 = '3' THEN
44 P_SORT := P_SORT || ',SHIP_DATE';
45 ELSIF P_SORT_1 = '2' THEN
46 P_SORT := P_SORT || ',ORDER_NO';
47 ELSIF P_SORT_1 = '4' THEN
48 P_SORT := P_SORT || ',CUSTOMER_NAME';
49 END IF;
50 IF P_SORT_2 = '5' THEN
51 P_SORT := P_SORT || ',WHSE_CODE';
52 ELSIF P_SORT_2 = '1' THEN
53 P_SORT := P_SORT || ',ITEM_NO';
54 ELSIF P_SORT_2 = '3' THEN
55 P_SORT := P_SORT || ',SHIP_DATE';
56 ELSIF P_SORT_2 = '2' THEN
57 P_SORT := P_SORT || ',ORDER_NO';
58 ELSIF P_SORT_2 = '4' THEN
59 P_SORT := P_SORT || ',CUSTOMER_NAME';
60 END IF;
61 IF P_SORT_3 = '5' THEN
62 P_SORT := P_SORT || ',WHSE_CODE';
63 ELSIF P_SORT_3 = '1' THEN
64 P_SORT := P_SORT || ',ITEM_NO';
65 ELSIF P_SORT_3 = '3' THEN
66 P_SORT := P_SORT || ',SHIP_DATE';
67 ELSIF P_SORT_3 = '2' THEN
68 P_SORT := P_SORT || ',ORDER_NO';
69 ELSIF P_SORT_3 = '4' THEN
70 P_SORT := P_SORT || ',CUSTOMER_NAME';
71 END IF;
72 IF P_SORT_4 = '5' THEN
73 P_SORT := P_SORT || ',WHSE_CODE';
74 ELSIF P_SORT_4 = '1' THEN
75 P_SORT := P_SORT || ',ITEM_NO';
76 ELSIF P_SORT_4 = '3' THEN
77 P_SORT := P_SORT || ',SHIP_DATE';
78 ELSIF P_SORT_4 = '2' THEN
79 P_SORT := P_SORT || ',ORDER_NO';
80 ELSIF P_SORT_4 = '4' THEN
81 P_SORT := P_SORT || ',CUSTOMER_NAME';
82 END IF;
83 RETURN (TRUE);
84 END AFTERPFORM;
85
86 FUNCTION CF_1FORMULA(ITEM_ID IN NUMBER
87 ,WHSE_CODE IN VARCHAR2
88 ,QC_GRADE IN VARCHAR2
89 ,UOM IN VARCHAR2) RETURN NUMBER IS
90 BEGIN
91 DECLARE
92 V_SHIP_QTY NUMBER;
93 V_COMMITTEDSALES_QTY NUMBER;
94 V_COMMITTEDPROD_QTY NUMBER;
95 V_INVENTORY_AVAIL NUMBER;
96 V_TEMP NUMBER;
97 L_GRADE_CTL NUMBER;
98 L_QTY_RESERVED_REAL NUMBER;
99 L_QTY2_RESERVED_REAL NUMBER;
100 L_ONHAND_QTY1 NUMBER := 0;
101 L_ONHAND_QTY2 NUMBER := 0;
102 L_COMMITTEDSALES_QTY1 NUMBER := 0;
103 L_COMMITTEDSALES_QTY2 NUMBER := 0;
104 L_ITEM_UOM VARCHAR2(4);
105 L_ORDER_UM VARCHAR2(4);
106 L_CONVERTED_UOM VARCHAR2(4);
107 L_CONVERTED_INVENTORY_AVAIL NUMBER;
108 CURSOR RESERVED_QUANTITY_FOR_GRD IS
109 SELECT
110 SUM(NVL(TRANS_QTY
111 ,0)),
112 SUM(NVL(TRANS_QTY2
113 ,0))
114 FROM
115 IC_TRAN_PND
116 WHERE ITEM_ID = CF_1FORMULA.ITEM_ID
117 AND WHSE_CODE = CF_1FORMULA.WHSE_CODE
118 AND COMPLETED_IND = 0
119 AND DELETE_MARK = 0
120 AND DOC_TYPE = 'OMSO'
121 AND QC_GRADE = CF_1FORMULA.QC_GRADE
122 AND ( LOT_ID <> 0
123 OR LOCATION <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT') );
124 CURSOR RESERVED_QUANTITY_FOR_ATP IS
125 SELECT
126 SUM(NVL(TRANS_QTY
127 ,0)),
128 SUM(NVL(TRANS_QTY2
129 ,0))
130 FROM
131 IC_TRAN_PND
132 WHERE ITEM_ID = CF_1FORMULA.ITEM_ID
133 AND WHSE_CODE = CF_1FORMULA.WHSE_CODE
134 AND COMPLETED_IND = 0
135 AND DELETE_MARK = 0
136 AND ( LOT_ID <> 0
137 OR LOCATION <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT') );
138 CURSOR QTY_ON_HAND IS
139 SELECT
140 SUM(NVL(S.ONHAND_ORDER_QTY
141 ,0)),
142 SUM(NVL(S.ONHAND_ORDER_QTY2
143 ,0)),
144 SUM(NVL(S.COMMITTEDSALES_QTY
145 ,0)),
146 SUM(NVL(S.COMMITTEDSALES_QTY2
147 ,0))
148 FROM
149 IC_SUMM_INV S
150 WHERE S.ITEM_ID = CF_1FORMULA.ITEM_ID
151 AND S.WHSE_CODE = CF_1FORMULA.WHSE_CODE;
152 CURSOR QTY_ON_HAND_GRADE IS
153 SELECT
154 SUM(NVL(S.ONHAND_ORDER_QTY
155 ,0)),
156 SUM(NVL(S.ONHAND_ORDER_QTY2
157 ,0)),
158 SUM(NVL(S.COMMITTEDSALES_QTY
159 ,0)),
160 SUM(NVL(S.COMMITTEDSALES_QTY2
161 ,0))
162 FROM
163 IC_SUMM_INV S
164 WHERE S.ITEM_ID = CF_1FORMULA.ITEM_ID
165 AND S.WHSE_CODE = CF_1FORMULA.WHSE_CODE
166 AND S.QC_GRADE = CF_1FORMULA.QC_GRADE;
167 CURSOR GET_GRADE_CTL IS
168 SELECT
169 GRADE_CTL
170 FROM
171 IC_ITEM_MST
172 WHERE ITEM_ID = CF_1FORMULA.ITEM_ID;
173 CURSOR GET_ITEM_UOM IS
174 SELECT
175 ITEM_UM
176 FROM
177 IC_ITEM_MST
178 WHERE ITEM_ID = CF_1FORMULA.ITEM_ID;
179 BEGIN
180 OPEN GET_GRADE_CTL;
181 FETCH GET_GRADE_CTL
182 INTO L_GRADE_CTL;
183 CLOSE GET_GRADE_CTL;
184 IF (L_GRADE_CTL > 0 AND CF_1FORMULA.QC_GRADE IS NOT NULL) THEN
185 OPEN RESERVED_QUANTITY_FOR_GRD;
186 FETCH RESERVED_QUANTITY_FOR_GRD
187 INTO L_QTY_RESERVED_REAL,L_QTY2_RESERVED_REAL;
188 CLOSE RESERVED_QUANTITY_FOR_GRD;
189 ELSE
190 OPEN RESERVED_QUANTITY_FOR_ATP;
191 FETCH RESERVED_QUANTITY_FOR_ATP
192 INTO L_QTY_RESERVED_REAL,L_QTY2_RESERVED_REAL;
193 CLOSE RESERVED_QUANTITY_FOR_ATP;
194 END IF;
195 L_QTY_RESERVED_REAL := NVL(L_QTY_RESERVED_REAL
196 ,0);
197 L_QTY2_RESERVED_REAL := NVL(L_QTY2_RESERVED_REAL
198 ,0);
199 IF (L_GRADE_CTL > 0 AND CF_1FORMULA.QC_GRADE IS NOT NULL) THEN
200 OPEN QTY_ON_HAND_GRADE;
201 FETCH QTY_ON_HAND_GRADE
202 INTO L_ONHAND_QTY1,L_ONHAND_QTY2,L_COMMITTEDSALES_QTY1,L_COMMITTEDSALES_QTY2;
203 CLOSE QTY_ON_HAND_GRADE;
204 ELSE
205 OPEN QTY_ON_HAND;
206 FETCH QTY_ON_HAND
207 INTO L_ONHAND_QTY1,L_ONHAND_QTY2,L_COMMITTEDSALES_QTY1,L_COMMITTEDSALES_QTY2;
208 CLOSE QTY_ON_HAND;
209 END IF;
210 L_ONHAND_QTY1 := NVL(L_ONHAND_QTY1
211 ,0);
212 L_ONHAND_QTY2 := NVL(L_ONHAND_QTY2
213 ,0);
214 L_COMMITTEDSALES_QTY1 := NVL(L_COMMITTEDSALES_QTY1
215 ,0);
216 L_COMMITTEDSALES_QTY2 := NVL(L_COMMITTEDSALES_QTY2
217 ,0);
218 V_INVENTORY_AVAIL := L_ONHAND_QTY1 + L_QTY_RESERVED_REAL;
219 OPEN GET_ITEM_UOM;
220 FETCH GET_ITEM_UOM
221 INTO L_ITEM_UOM;
222 CLOSE GET_ITEM_UOM;
223 /*SRW.MESSAGE('9'
224 ,'l_item_uom = ' || L_ITEM_UOM)*/NULL;
225 /*SRW.MESSAGE('2'
226 ,'just b4 uom call uom = ' || UOM)*/NULL;
227 /*SRW.MESSAGE('3'
228 ,'just b4 uom item_id = ' || ITEM_ID)*/NULL;
229 /*SRW.MESSAGE('4'
230 ,'v_inventory_avail = ' || V_INVENTORY_AVAIL)*/NULL;
231 SELECT
232 UM_CODE
233 INTO L_ORDER_UM
234 FROM
235 SY_UOMS_MST
236 WHERE UOM_CODE = CF_1FORMULA.UOM;
237 GMICUOM.ICUOMCV(CF_1FORMULA.ITEM_ID
238 ,0
239 ,V_INVENTORY_AVAIL
240 ,L_ITEM_UOM
241 ,L_ORDER_UM
242 ,L_CONVERTED_INVENTORY_AVAIL);
243 /*SRW.MESSAGE('4'
244 ,'l_converted_inventory_avail = ' || L_CONVERTED_INVENTORY_AVAIL)*/NULL;
245 V_INVENTORY_AVAIL := L_CONVERTED_INVENTORY_AVAIL;
246 IF (V_INVENTORY_AVAIL < 0) THEN
247 RETURN 0;
248 ELSE
249 RETURN V_INVENTORY_AVAIL;
250 END IF;
251 END;
252 RETURN NULL;
253 END CF_1FORMULA;
254
255 FUNCTION CF_SORT_DESCFORMULA RETURN VARCHAR2 IS
256 BEGIN
257 IF P_SORT_1 IS NOT NULL THEN
258 SELECT
259 MEANING
260 INTO CP_SORT_1
261 FROM
262 GEM_LOOKUP_VALUES
263 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
264 AND LOOKUP_CODE = P_SORT_1;
265 END IF;
266 IF P_SORT_2 IS NOT NULL THEN
267 SELECT
268 MEANING
269 INTO CP_SORT_2
270 FROM
271 GEM_LOOKUP_VALUES
272 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
273 AND LOOKUP_CODE = P_SORT_2;
274 END IF;
275 IF P_SORT_3 IS NOT NULL THEN
276 SELECT
277 MEANING
278 INTO CP_SORT_3
279 FROM
280 GEM_LOOKUP_VALUES
281 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
282 AND LOOKUP_CODE = P_SORT_3;
283 END IF;
284 IF P_SORT_4 IS NOT NULL THEN
285 SELECT
286 MEANING
287 INTO CP_SORT_4
288 FROM
289 GEM_LOOKUP_VALUES
290 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
291 AND LOOKUP_CODE = P_SORT_4;
292 END IF;
293 RETURN NULL;
294 EXCEPTION
295 WHEN OTHERS THEN
296 RETURN NULL;
297 END CF_SORT_DESCFORMULA;
298
299 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
300 BEGIN
301 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
302 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
303 SELECT
304 NAME
305 INTO CP_ORGN_NAME
306 FROM
307 HR_OPERATING_UNITS
308 WHERE ORGANIZATION_ID = FND_PROFILE.VALUE('ORG_ID');
309 SELECT
310 USER_NAME
311 INTO CP_USER
312 FROM
313 FND_USER
314 WHERE P_DEFAULT_USER = USER_ID;
315 IF P_SORT_1 IS NOT NULL THEN
316 SELECT
317 MEANING
318 INTO CP_SORT_1
319 FROM
320 GEM_LOOKUPS
321 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
322 AND LOOKUP_CODE = P_SORT_1;
323 END IF;
324 IF P_SORT_2 IS NOT NULL THEN
325 SELECT
326 MEANING
327 INTO CP_SORT_2
328 FROM
329 GEM_LOOKUPS
330 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
331 AND LOOKUP_CODE = P_SORT_2;
332 END IF;
333 IF P_SORT_3 IS NOT NULL THEN
334 SELECT
335 MEANING
336 INTO CP_SORT_3
337 FROM
338 GEM_LOOKUPS
339 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
340 AND LOOKUP_CODE = P_SORT_3;
341 END IF;
342 IF P_SORT_4 IS NOT NULL THEN
343 SELECT
344 MEANING
345 INTO CP_SORT_4
346 FROM
347 GEM_LOOKUPS
348 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
349 AND LOOKUP_CODE = P_SORT_4;
350 END IF;
351 RETURN (TRUE);
352 EXCEPTION
353 WHEN OTHERS THEN
354 RETURN TRUE;
355 END BEFOREREPORT;
356
357 FUNCTION CF_LINE_NUMBERFORMULA(LINE_NUMBER IN NUMBER
358 ,SHIPMENT_NUMBER IN NUMBER) RETURN NUMBER IS
359 BEGIN
360 DECLARE
361 L_LINE_NUMBER NUMBER;
362 L_SHIPMENT_NUMBER NUMBER;
363 BEGIN
364 L_LINE_NUMBER := LINE_NUMBER || '.' || SHIPMENT_NUMBER;
365 RETURN L_LINE_NUMBER;
366 END;
367 RETURN NULL;
368 END CF_LINE_NUMBERFORMULA;
369
370 FUNCTION CF_UNALLOCATED_INVENTORYFORMUL(ITEM_ID IN NUMBER
371 ,LINE_ID IN NUMBER
372 ,UNALLOCATED_INVENTORY IN NUMBER
373 ,UOM IN VARCHAR2) RETURN NUMBER IS
374 BEGIN
375 DECLARE
376 L_ITEM_UOM VARCHAR2(4);
377 L_CONVERTED_UNALLOCATED_INV NUMBER;
378 V_UNALLOCATED_INVENTORY NUMBER;
379 L_LINE_ID NUMBER;
380 L_UNALLOCATED_INVENTORY NUMBER;
381 L_ORDER_UM VARCHAR2(4);
382 CURSOR GET_ITEM_UOM IS
383 SELECT
384 ITEM_UM
385 FROM
386 IC_ITEM_MST
387 WHERE ITEM_ID = CF_UNALLOCATED_INVENTORYFORMUL.ITEM_ID;
388 CURSOR CHECK_IC_TRAN_PND IS
389 SELECT
390 PND.LINE_ID
391 FROM
392 IC_TRAN_PND PND
393 WHERE PND.DOC_TYPE = 'OMSO'
394 AND PND.COMPLETED_IND = 0
395 AND PND.DELETE_MARK = 0
396 AND PND.TRANS_QTY < 0
397 AND LINE_ID = CF_UNALLOCATED_INVENTORYFORMUL.LINE_ID;
398 BEGIN
399 /*SRW.MESSAGE('1'
400 ,'in unallocated_inventory conversion function')*/NULL;
401 OPEN CHECK_IC_TRAN_PND;
402 FETCH CHECK_IC_TRAN_PND
403 INTO L_LINE_ID;
404 CLOSE CHECK_IC_TRAN_PND;
405 IF (NVL(L_LINE_ID
406 ,0) = 0) THEN
407 RETURN (CF_UNALLOCATED_INVENTORYFORMUL.UNALLOCATED_INVENTORY);
408 ELSE
409 OPEN GET_ITEM_UOM;
410 FETCH GET_ITEM_UOM
411 INTO L_ITEM_UOM;
412 CLOSE GET_ITEM_UOM;
413 /*SRW.MESSAGE('9'
414 ,'zzl_item_uom = ' || L_ITEM_UOM)*/NULL;
415 /*SRW.MESSAGE('2'
416 ,'zzjust b4 uom call uom = ' || UOM)*/NULL;
417 /*SRW.MESSAGE('3'
418 ,'zzjust b4 uom item_id = ' || ITEM_ID)*/NULL;
419 /*SRW.MESSAGE('4'
420 ,'zzunallocated_inventory = ' || UNALLOCATED_INVENTORY)*/NULL;
421 SELECT
422 UM_CODE
423 INTO L_ORDER_UM
424 FROM
425 SY_UOMS_MST
426 WHERE UOM_CODE = UOM;
427 GMICUOM.ICUOMCV(CF_UNALLOCATED_INVENTORYFORMUL.ITEM_ID
428 ,0
429 ,CF_UNALLOCATED_INVENTORYFORMUL.UNALLOCATED_INVENTORY
430 ,L_ITEM_UOM
431 ,L_ORDER_UM
432 ,L_CONVERTED_UNALLOCATED_INV);
433 /*SRW.MESSAGE('4'
434 ,'l_converted_iunallocated_inventory = ' || L_CONVERTED_UNALLOCATED_INV)*/NULL;
435 V_UNALLOCATED_INVENTORY := L_CONVERTED_UNALLOCATED_INV;
436 IF (V_UNALLOCATED_INVENTORY < 0) THEN
437 RETURN 0;
438 ELSE
439 RETURN V_UNALLOCATED_INVENTORY;
440 END IF;
441 END IF;
442 END;
443 END CF_UNALLOCATED_INVENTORYFORMUL;
444
445 FUNCTION AFTERREPORT RETURN BOOLEAN IS
446 BEGIN
447 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
448 RETURN (TRUE);
449 END AFTERREPORT;
450
451 FUNCTION CP_ORGN_NAME_P RETURN VARCHAR2 IS
452 BEGIN
453 RETURN CP_ORGN_NAME;
454 END CP_ORGN_NAME_P;
455
456 FUNCTION CP_USER_P RETURN VARCHAR2 IS
457 BEGIN
458 RETURN CP_USER;
459 END CP_USER_P;
460
461 FUNCTION CP_SORT_4_P RETURN VARCHAR2 IS
462 BEGIN
463 RETURN CP_SORT_4;
464 END CP_SORT_4_P;
465
466 FUNCTION CP_SORT_3_P RETURN VARCHAR2 IS
467 BEGIN
468 RETURN CP_SORT_3;
469 END CP_SORT_3_P;
470
471 FUNCTION CP_SORT_2_P RETURN VARCHAR2 IS
472 BEGIN
473 RETURN CP_SORT_2;
474 END CP_SORT_2_P;
475
476 FUNCTION CP_SORT_1_P RETURN VARCHAR2 IS
477 BEGIN
478 RETURN CP_SORT_1;
479 END CP_SORT_1_P;
480
481 END GML_GMLUNALC_XMLP_PKG;
482
[Home] [Help]