1 PACKAGE BODY INV_INVKBCPR_XMLP_PKG AS
2 /* $Header: INVKBCPRB.pls 120.1 2007/12/25 10:38:41 dwkrishn noship $ */
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 IF P_REPORT_ID IS NOT NULL THEN
118 P_CALL_WHERE := P_CALL_WHERE || ' AND (MKC.KANBAN_CARD_ID IN ( SELECT MKBNT.KANBAN_CARD_ID FROM MTL_KANBAN_CARD_PRINT_TEMP MKBNT
119 WHERE MKBNT.REPORT_ID = ' || TO_CHAR(P_REPORT_ID) || ')
120 OR MKC.KANBAN_CARD_ID IN ( SELECT MKC.KANBAN_CARD_ID FROM MTL_KANBAN_CARDS MKC WHERE
121 MKC.PULL_SEQUENCE_ID IN ( SELECT MKBNT.PULL_SEQUENCE_ID
122 FROM MTL_KANBAN_CARD_PRINT_TEMP MKBNT
123 WHERE MKBNT.REPORT_ID = ' || TO_CHAR(P_REPORT_ID) || ')))';
124 END IF;
125 ELSIF P_CALL_FROM = 3 THEN
126 IF P_KANBAN_CARD_ID IS NOT NULL THEN
127 P_CALL_WHERE := P_CALL_WHERE || ' AND MKC.KANBAN_CARD_ID = ' || TO_CHAR(P_KANBAN_CARD_ID);
128 END IF;
129 END IF;
130 IF P_SORT_BY = 1 THEN
131 P_ORDER_BY := 'MKC.KANBAN_CARD_NUMBER';
132 ELSIF P_SORT_BY = 2 THEN
133 P_ORDER_BY := 'MKC.SUBINVENTORY_NAME';
134 ELSIF P_SORT_BY = 3 THEN
135 P_ORDER_BY := 'MKC.SUBINVENTORY_NAME , ' || P_LOC_FLEXDATA;
136 ELSIF P_SORT_BY = 4 THEN
137 P_ORDER_BY := 'PSV.VENDOR_NAME , MSS.VENDOR_SITE_CODE ';
138 ELSIF P_SORT_BY = 5 THEN
139 P_ORDER_BY := 'MKC.SOURCE_ORGANIZATION_ID,
140 MKC.SOURCE_SUBINVENTORY, ' || P_SOURCE_LOC_FLEXDATA;
141 END IF;
142 RETURN (TRUE);
143 END BEFOREREPORT;
144
145 FUNCTION AFTERREPORT RETURN BOOLEAN IS
146 BEGIN
147 IF P_CALL_FROM = 2 THEN
148 BEGIN
149 DELETE FROM MTL_KANBAN_CARD_PRINT_TEMP
150 WHERE REPORT_ID = P_REPORT_ID;
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 NULL;
154 WHEN OTHERS THEN
155 NULL;
156 END;
157 END IF;
158 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
159 COMMIT;
160 RETURN (TRUE);
161 END AFTERREPORT;
162
163 FUNCTION AFTERPFORM RETURN BOOLEAN IS
164 BEGIN
165 IF P_TRACE_FLAG = 1 THEN
166 EXECUTE IMMEDIATE
167 ' alter session set sql_trace = true ';
168 END IF;
169 RETURN (TRUE);
170 END AFTERPFORM;
171
172 END INV_INVKBCPR_XMLP_PKG;
173
[Home] [Help]