1 PACKAGE BODY GMI_PIR05_XMLP_PKG AS
2 /* $Header: PIR05B.pls 120.0 2007/12/24 13:18:43 nchinnam noship $ */
3 FUNCTION WHSE_DESCFORMULA RETURN VARCHAR2 IS
4 WHSE_DESC VARCHAR2(40);
5 BEGIN
6 SELECT
7 WHSE_NAME
8 INTO WHSE_DESC
9 FROM
10 IC_WHSE_MST
11 WHERE WHSE_CODE = WHSE;
12 RETURN (WHSE_DESC);
13 RETURN NULL;
14 EXCEPTION
15 WHEN OTHERS THEN
16 RETURN (NULL);
17 END WHSE_DESCFORMULA;
18 FUNCTION AFTERPFORM RETURN BOOLEAN IS
19 CURSOR CYCLE_DESC(CYCLE_ID_1 number) IS
20 SELECT
21 CYCLE_NO
22 FROM
23 IC_CYCL_HDR
24 WHERE CYCLE_ID = CYCLE_ID_1;
25 CURSOR CURSOR_00007(CYCLE_ID_1 number) IS
26 SELECT
27 MIN(COUNT_NO)
28 FROM
29 IC_CYCL_DTL
30 WHERE CYCLE_ID = CYCLE_ID_1;
31 CURSOR CURSOR_00011(CYCLE_ID_1 number) IS
32 SELECT
33 count(*)
34 FROM
35 IC_PHYS_CNT
36 WHERE IC_PHYS_CNT.WHSE_CODE = WHSE
37 AND IC_PHYS_CNT.CYCLE_ID = CYCLE_ID_1
38 AND IC_PHYS_CNT.COUNT_NO >= FROM_COUNT
39 AND IC_PHYS_CNT.COUNT_NO <= TO_COUNT
40 AND IC_PHYS_CNT.CREATION_DATE <> IC_PHYS_CNT.LAST_UPDATE_DATE;
41 X_MIN_COUNT NUMBER := 0;
42 COUNT NUMBER;
43 COUNT1 NUMBER;
44 COUNT2 NUMBER;
45 BEGIN
46 CYCLE_ID_1:=CYCLE_ID;
47 IF CYCLE_ID_1 IS NOT NULL THEN
48 BEGIN
49 OPEN CYCLE_DESC(CYCLE_ID_1);
50 FETCH CYCLE_DESC
51 INTO CYCLE;
52 CLOSE CYCLE_DESC;
53 Exception when others then
54 raise_application_error(-20001,SQLERRM);
55 END;
56 END IF;
57 SELECT
58 PRINT_FORMAT
59 INTO PRN_FOR
60 FROM
61 IC_CYCL_HDR
62 WHERE IC_CYCL_HDR.WHSE_CODE = WHSE
63 AND IC_CYCL_HDR.CYCLE_ID = CYCLE_ID_1;
64 IF PRN_FOR = 0 THEN
65 IF (SORT_BY = 'ITEM,LOT,LOCATION') THEN
66 F_ORDER_BY := 'ITEM_NO,LOT_NO,SUBLOT_NO,LOCATION ASC';
67 END IF;
68 IF (SORT_BY = 'LOCATION,ITEM,LOT') THEN
69 F_ORDER_BY := 'LOCATION,ITEM_NO,LOT_NO,SUBLOT_NO ASC';
70 END IF;
71 IF (SORT_BY = 'ITEM,LOCATION,LOT') THEN
72 F_ORDER_BY := 'ITEM_NO,LOCATION,LOT_NO,SUBLOT_NO ASC';
73 END IF;
74 IF (SORT_BY = 'COUNT_NUMBER') THEN
75 F_ORDER_BY := 'COUNT_NO,ITEM_NO,LOT_NO,SUBLOT_NO,LOCATION ASC';
76 END IF;
77 END IF;
78 IF PRN_FOR = 2 THEN
79 F_ORDER_BY := 'COUNT_NO';
80 END IF;
81 IF PRN_FOR = 1 THEN
82 IF (SORT_BY = 'COUNT_NUMBER') THEN
83 F_ORDER_BY := 'COUNT_NO,ITEM_NO,LOT_NO,SUBLOT_NO,LOCATION ASC';
84 END IF;
85 IF (SORT_BY = 'ITEM,LOT,LOCATION') THEN
86 F_ORDER_BY := 'ITEM_NO,LOT_NO,SUBLOT_NO,LOCATION ASC';
87 END IF;
88 IF (SORT_BY = 'LOCATION,ITEM,LOT') THEN
89 F_ORDER_BY := 'LOCATION,ITEM_NO,LOT_NO,SUBLOT_NO ASC';
90 END IF;
91 IF (SORT_BY = 'ITEM,LOCATION,LOT') THEN
92 F_ORDER_BY := 'ITEM_NO,LOCATION,LOT_NO,SUBLOT_NO ASC';
93 END IF;
94 END IF;
95 BEGIN
96 SELECT MIN(COUNT_NO)
97 INTO X_MIN_COUNT
98 FROM IC_CYCL_DTL
99 WHERE CYCLE_ID = CYCLE_ID_1
100 AND rownum=1;
101 /*
102 OPEN CURSOR_00007(CYCLE_ID_1);
103 FETCH CURSOR_00007
104 INTO X_MIN_COUNT;
105 IF (CURSOR_00007%NOTFOUND) THEN
106 CLOSE CURSOR_00007;
107 END IF;
108 CLOSE CURSOR_00007;*/
109 exception when others then
110 raise_application_error(-20001,SQLERRM);
111 END;
112 BEGIN
113 SELECT count(*)
114 INTO COUNT2
115 FROM IC_PHYS_CNT
116 WHERE IC_PHYS_CNT.WHSE_CODE = WHSE
117 AND IC_PHYS_CNT.CYCLE_ID = CYCLE_ID_1
118 AND IC_PHYS_CNT.COUNT_NO >= FROM_COUNT
119 AND IC_PHYS_CNT.COUNT_NO <= TO_COUNT
120 AND IC_PHYS_CNT.CREATION_DATE <> IC_PHYS_CNT.LAST_UPDATE_DATE
121 AND rownum=1;
122 /* OPEN CURSOR_00011(CYCLE_ID_1);
123 FETCH CURSOR_00011
124 INTO COUNT2;
125 IF COUNT2 > 0 THEN
126 CLOSE CURSOR_00011;
127 END IF;
128 CLOSE CURSOR_00011;*/
129 exception when others then
130 raise_application_error(-20001,SQLERRM);
131 END;
132 RETURN (TRUE);
133 /*EXCEPTION
134 WHEN OTHERS THEN
135 raise_application_error(-20001,SQLERRM);
136 RETURN (false);*/
137 END AFTERPFORM;
138 FUNCTION CYCLE_NOFORMULA RETURN VARCHAR2 IS
139 CYCLE_NO VARCHAR2(32);
140 BEGIN
141 SELECT
142 CYCLE_NO,
143 CYCLE_SOURCE
144 INTO CYCLE_NO,PHYSICAL_OR_CYCLE
145 FROM
146 IC_CYCL_HDR
147 WHERE CYCLE_ID = CYCLE_ID_1;
148 RETURN (CYCLE_NO);
149 RETURN NULL;
150 EXCEPTION
151 WHEN OTHERS THEN
152 RETURN (NULL);
153 END CYCLE_NOFORMULA;
154 PROCEDURE GMI_PIR05_XMLP_PKG_HEADER IS
155 BEGIN
156 NULL;
157 END GMI_PIR05_XMLP_PKG_HEADER;
158 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
159 BEGIN
160 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
161 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
162 RETURN (TRUE);
163 END BEFOREREPORT;
164 FUNCTION F_ITEMUM2FORMULA(ITEM_UM2 IN VARCHAR2) RETURN NUMBER IS
165 BEGIN
166 IF ITEM_UM2 IS NOT NULL THEN
167 P_ITEMUM2LABEL := 'Quantity2:';
168 P_ITEMUM2DASH := '______________________';
169 P_CSITEMUM2_LABEL := 'Quantity2:';
170 P_CSITEMUM2_DASH := '______________________';
171 ELSE
172 P_ITEMUM2LABEL := ' ';
173 P_ITEMUM2DASH := ' ';
174 P_CSITEMUM2_LABEL := ' ';
175 P_CSITEMUM2_DASH := ' ';
176 END IF;
177 RETURN 1;
178 END F_ITEMUM2FORMULA;
179 FUNCTION AFTERREPORT RETURN BOOLEAN IS
180 BEGIN
181 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
182 RETURN (TRUE);
183 END AFTERREPORT;
184 FUNCTION P_ITEMUM2LABEL_P RETURN VARCHAR2 IS
185 BEGIN
186 RETURN P_ITEMUM2LABEL;
187 END P_ITEMUM2LABEL_P;
188 FUNCTION P_ITEMUM2DASH_P RETURN VARCHAR2 IS
189 BEGIN
190 RETURN P_ITEMUM2DASH;
191 END P_ITEMUM2DASH_P;
192 FUNCTION P_CSITEMUM2_LABEL_P RETURN VARCHAR2 IS
193 BEGIN
194 RETURN P_CSITEMUM2_LABEL;
195 END P_CSITEMUM2_LABEL_P;
196 FUNCTION P_CSITEMUM2_DASH_P RETURN VARCHAR2 IS
197 BEGIN
198 RETURN P_CSITEMUM2_DASH;
199 END P_CSITEMUM2_DASH_P;
200 END GMI_PIR05_XMLP_PKG;
201
[Home] [Help]