1 PACKAGE BODY INV_INVKBCPR_XMLP_PKG AS
2 /* $Header: INVKBCPRB.pls 120.2 2011/02/07 13:41:35 sisankar ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 EXCEPTION
9 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
10 /*SRW.MESSAGE(010
11 ,'Failed in before report trigger, srwinit. ')*/NULL;
12 RAISE;
13 END;
14 DECLARE
15 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
16 BEGIN
17 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
18 EXCEPTION
19 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
20 /*SRW.MESSAGE(020
21 ,'Failed in before report trigger, setting org profile ')*/NULL;
22 RAISE;
23 END;
24 BEGIN
25 NULL;
26 EXCEPTION
27 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
28 /*SRW.MESSAGE(020
29 ,'Failed in before report trigger, item select. ')*/NULL;
30 RAISE;
31 END;
32 BEGIN
33 NULL;
34 EXCEPTION
35 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
36 /*SRW.MESSAGE(030
37 ,'Failed in before report trigger, locator select. ')*/NULL;
38 RAISE;
39 END;
40 BEGIN
41 NULL;
42 EXCEPTION
43 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
44 /*SRW.MESSAGE(030
45 ,'Failed in before report trigger, source locator select. ')*/NULL;
46 RAISE;
47 END;
48 BEGIN
49 IF P_ITEM_HIGH IS NOT NULL OR P_ITEM_LOW IS NOT NULL THEN
50 NULL;
51 END IF;
52 EXCEPTION
53 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
54 /*SRW.MESSAGE(1
55 ,'Failed in before report trigger:Item/where')*/NULL;
56 RAISE;
57 END;
58 BEGIN
59 IF P_LOCATOR_HI IS NOT NULL OR P_LOCATOR_LOW IS NOT NULL THEN
60 NULL;
61 END IF;
62 EXCEPTION
63 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
64 /*SRW.MESSAGE(1
65 ,'Failed in before report trigger:Loc/where')*/NULL;
66 RAISE;
67 END;
68 IF P_CALL_FROM = 1 THEN
69 IF P_DATE_CREATED_LOW IS NOT NULL THEN
70 P_CALL_WHERE := P_CALL_WHERE || ' AND TO_CHAR(MKC.CREATION_DATE,''YYYY-MM-DD'' ) >= ''' || TO_CHAR(P_DATE_CREATED_LOW
71 ,'YYYY-MM-DD') || '''';
72 END IF;
73 IF P_DATE_CREATED_HIGH IS NOT NULL THEN
74 P_CALL_WHERE := P_CALL_WHERE || ' AND TO_CHAR(MKC.CREATION_DATE,''YYYY-MM-DD'' ) <= ''' || TO_CHAR(P_DATE_CREATED_HIGH
75 ,'YYYY-MM-DD') || '''';
76 END IF;
77 IF P_ORG_ID IS NOT NULL THEN
78 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.ORGANIZATION_ID = ' || TO_CHAR(P_ORG_ID);
79 END IF;
80 IF P_SOURCE_TYPE IS NOT NULL THEN
81 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SOURCE_TYPE = ' || TO_CHAR(P_SOURCE_TYPE);
82 END IF;
83 IF P_SOURCE_ORG_ID IS NOT NULL THEN
84 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SOURCE_ORGANIZATION_ID = ' || TO_CHAR(P_SOURCE_ORG_ID);
85 END IF;
86 IF P_SOURCE_SUBINV IS NOT NULL THEN
87 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SOURCE_SUBINVENTORY = ''' || P_SOURCE_SUBINV || '''';
88 END IF;
89 IF P_SOURCE_LOC_ID IS NOT NULL THEN
90 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SOURCE_LOCATOR_ID = ' || TO_CHAR(P_SOURCE_LOC_ID);
91 END IF;
92 IF P_KANBAN_CARD_NUMBER_LOW IS NOT NULL THEN
93 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.KANBAN_CARD_NUMBER >= ''' || P_KANBAN_CARD_NUMBER_LOW || '''';
94 END IF;
95 IF P_KANBAN_CARD_NUMBER_HIGH IS NOT NULL THEN
96 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.KANBAN_CARD_NUMBER <= ''' || P_KANBAN_CARD_NUMBER_HIGH || '''';
97 END IF;
98 IF P_SUPPLIER_ID IS NOT NULL THEN
99 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SUPPLIER_ID = ' || TO_CHAR(P_SUPPLIER_ID);
100 END IF;
101 IF P_SUPPLIER_SITE_ID IS NOT NULL THEN
102 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SUPPLIER_SITE_ID = ' || TO_CHAR(P_SUPPLIER_SITE_ID);
103 END IF;
104 IF P_SUBINV IS NOT NULL THEN
105 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.SUBINVENTORY_NAME = ''' || P_SUBINV || '''';
106 END IF;
107 IF P_KANBAN_CARD_TYPE IS NOT NULL THEN
108 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.KANBAN_CARD_TYPE = ' || TO_CHAR(P_KANBAN_CARD_TYPE);
109 END IF;
110 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HIGH IS NOT NULL THEN
111 P_CALL_WHERE := P_CALL_WHERE || ' AND ' || P_ITEM_WHERE;
112 END IF;
113 IF P_LOCATOR_LOW IS NOT NULL OR P_LOCATOR_HI IS NOT NULL THEN
114 P_CALL_WHERE := P_CALL_WHERE || ' AND ' || P_LOC_WHERE;
115 END IF;
116 ELSIF P_CALL_FROM = 2 THEN
117 --in case for multiple cards from adf page also p_call_from has to be 2
118 --in case of pullsequence id also from adf page p_call_from has to be 2
119 IF P_REPORT_ID IS NOT NULL THEN
120 P_CALL_WHERE := P_CALL_WHERE || ' AND (MKC.KANBAN_CARD_ID IN ( SELECT MKBNT.KANBAN_CARD_ID FROM MTL_KANBAN_CARD_PRINT_TEMP MKBNT
121 WHERE MKBNT.REPORT_ID = ' || TO_CHAR(P_REPORT_ID) || ')
122 OR MKC.KANBAN_CARD_ID IN ( SELECT MKC.KANBAN_CARD_ID FROM MTL_KANBAN_CARDS MKC WHERE
123 MKC.PULL_SEQUENCE_ID IN ( SELECT MKBNT.PULL_SEQUENCE_ID
124 FROM MTL_KANBAN_CARD_PRINT_TEMP MKBNT
125 WHERE MKBNT.REPORT_ID = ' || TO_CHAR(P_REPORT_ID) || ')))';
126 END IF;
127 ELSIF P_CALL_FROM = 3 THEN
128 IF P_KANBAN_CARD_ID IS NOT NULL THEN
129 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.KANBAN_CARD_ID = ' || TO_CHAR(P_KANBAN_CARD_ID);
130 END IF;
131 END IF;
132 IF P_SORT_BY = 1 THEN
133 P_ORDER_BY := 'MKC.KANBAN_CARD_NUMBER';
134 ELSIF P_SORT_BY = 2 THEN
135 P_ORDER_BY := 'MKC.SUBINVENTORY_NAME';
136 ELSIF P_SORT_BY = 3 THEN
137 P_ORDER_BY := 'MKC.SUBINVENTORY_NAME , ' || P_LOC_FLEXDATA;
138 ELSIF P_SORT_BY = 4 THEN
139 P_ORDER_BY := 'PSV.VENDOR_NAME , MSS.VENDOR_SITE_CODE ';
140 ELSIF P_SORT_BY = 5 THEN
141 P_ORDER_BY := 'MKC.SOURCE_ORGANIZATION_ID,
142 MKC.SOURCE_SUBINVENTORY, ' || P_SOURCE_LOC_FLEXDATA;
143 END IF;
144 RETURN (TRUE);
145 END BEFOREREPORT;
146
147 FUNCTION AFTERREPORT RETURN BOOLEAN IS
148 BEGIN
149 --IF P_CALL_FROM = 2 THEN
150 --Added as part of ekanban by javakat
151 --where 4 is cal from adf page
152 IF P_CALL_FROM IN ( 2 ,4) THEN
153 BEGIN
154 DELETE FROM MTL_KANBAN_CARD_PRINT_TEMP
155 WHERE REPORT_ID = P_REPORT_ID;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 NULL;
159 WHEN OTHERS THEN
160 NULL;
161 END;
162 END IF;
163 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
164 COMMIT;
165 RETURN (TRUE);
166 END AFTERREPORT;
167
168 FUNCTION AFTERPFORM RETURN BOOLEAN IS
169 BEGIN
170 IF P_TRACE_FLAG = 1 THEN
171 EXECUTE IMMEDIATE
172 ' alter session set sql_trace = true ';
173 END IF;
174 RETURN (TRUE);
175 END AFTERPFORM;
176 --Added this funtion for ekanban by javakat
177 --to print report for multiple kanaban records s
178 /* Formatted on 2011/01/05 12:36 (Formatter Plus v4.8.8) */
179 PROCEDURE call_kanban_report (
180 errbuf OUT NOCOPY VARCHAR2,
181 retcode OUT NOCOPY NUMBER,
182 x_request_id OUT NOCOPY NUMBER,
183 p_pull_sequence_id IN NUMBER,
184 p_mul_kbn_card_ids IN VARCHAR2
185 )
186 IS
187 v_report_id NUMBER;
188 v_sort_by NUMBER := 3;
189 v_call_from NUMBER := 2;
190 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
191 v_request_id NUMBER;
192 l_count NUMBER;
193 l_value VARCHAR2 (2000);
194 l_language VARCHAR2 (10);
195 l_territory VARCHAR2 (10);
196 l_add_layout BOOLEAN;
197 BEGIN
198 fnd_file.put_line (fnd_file.LOG, 'call_kanban_report');
199 fnd_file.put_line (fnd_file.LOG,
200 'P_PULL_SEQUENCE_ID: ' || P_PULL_SEQUENCE_ID
201 );
202 fnd_file.put_line (fnd_file.LOG,
203 'P_MUL_KBN_CARD_IDS: ' || p_mul_kbn_card_ids
204 );
205
206 IF P_PULL_SEQUENCE_ID IS NOT NULL THEN
207 v_report_id:=0;
208 fnd_file.put_line (fnd_file.LOG,
209 'Call from is 3 and Pull sequence is Passed'
210 );
211 IF v_report_id IS NULL
212 THEN
213 SELECT mtl_kanban_card_print_temp_s.NEXTVAL
214 INTO v_report_id
215 FROM DUAL;
216 END IF;
217
218 INSERT INTO mtl_kanban_card_print_temp
219 (report_id, kanban_card_id
220 )
221 select v_report_id,kanban_card_id from MTL_KANBAN_CARDS_V
222 where pull_sequence_id=pull_sequence_id;
223 ELSE
224
225 l_count :=
226 LENGTH (p_mul_kbn_card_ids)
227 - LENGTH (REPLACE (p_mul_kbn_card_ids, ',', ''))
228 + 1;
229 fnd_file.put_line (fnd_file.LOG, 'no of kanban cards selceted : ' || l_count);
230
231 -- In oracle 11g use regexp_count to determine l_count
232 FOR i IN 1 .. l_count
233 LOOP
234 SELECT REGEXP_SUBSTR (p_mul_kbn_card_ids, '[^,]+', 1, i)
235 INTO l_value
236 FROM DUAL;
237 IF v_report_id IS NULL
238 THEN
239 SELECT mtl_kanban_card_print_temp_s.NEXTVAL
240 INTO v_report_id
241 FROM DUAL;
242 END IF;
243
244 INSERT INTO mtl_kanban_card_print_temp
245 (report_id, kanban_card_id
246 )
247 VALUES (v_report_id, l_value
248 );
249 END LOOP;
250
251 fnd_file.put_line (fnd_file.LOG, 'v_report_id: ' || v_report_id);
252 END IF;
253 -- call to report conc pgm report
254 IF v_report_id IS NOT NULL OR P_PULL_SEQUENCE_ID IS NOT NULL
255 THEN
256 BEGIN
257 SELECT LOWER(FL.iso_language),ISO_TERRITORY
258 INTO l_language,l_territory
259 FROM NLS_SESSION_PARAMETERS NSP
260 ,fnd_languages FL
261 WHERE NSP.parameter = 'NLS_LANGUAGE'
262 AND NSP.value = FL.nls_language;
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 FND_MESSAGE.set_name('FLM', 'FLM_SET_NLS_PARAM');
266 fnd_file.put_line(fnd_file.log, fnd_message.get);
267 END;
268 l_add_layout :=
269 fnd_request.add_layout ('INV', 'INVKBCPR_XML', l_language, l_territory, 'RTF');
270
271
272 IF l_add_layout
273 THEN
274 v_request_id :=
275 fnd_request.submit_request ('INV',
276 'INVKBCPR_XML',
277 NULL,
278 NULL,
279 FALSE,
280 NULL, /* p_org_id */
281 NULL, /* p_date_created_low */
282 NULL, /* p_date_created_high */
283 NULL, /* p_kanban_card_number_low */
284 NULL, /* p_kanban_card_number_high */
285 NULL, /* p_item_low */
286 NULL, /* p_item_high */
287 NULL, /* p_subinv */
288 NULL, /* p_locator_low */
289 NULL, /* p_locator_high */
290 NULL, /* p_source_type */
291 NULL, /* p_kanban_card_type */
292 NULL, /* p_supplier */
293 NULL, /* p_supplier_site */
294 NULL, /* p_source_org_id */
295 NULL, /* p_source_subinv */
296 NULL, /* p_source_loc_id */
297 v_sort_by, /* p_sort_by */
298 v_call_from, /* p_call_from */
299 NULL, /* p_kanban_card_id */
300 v_report_id, /* p_report_id */
301 NULL /* DebugFlag */
302 );
303 COMMIT;
304 END IF;
305 END IF;
306 retcode := 0 ;
307 x_request_id := v_request_id;
308
309 fnd_file.put_line (fnd_file.LOG, 'request_id: ' || x_request_id);
310 EXCEPTION
311 WHEN OTHERS
312 THEN
313 retcode := 2;
314 errbuf := SQLCODE || ':' || SQLERRM;
315 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
316 END call_kanban_report;
317 END INV_INVKBCPR_XMLP_PKG;
318
[Home] [Help]