1 PACKAGE BODY INV_INVARPMT_XMLP_PKG AS
2 /* $Header: INVARPMTB.pls 120.2 2011/03/01 12:24:32 sanjeevs 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(1
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 FND_PROFILE.PUT('MFG_ORGANIZATION_ID'
18 ,P_ORG_ID_CHAR);
19 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
20 EXCEPTION
21 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
22 /*SRW.MESSAGE(020
23 ,'Failed in before report trigger, setting org profile ')*/NULL;
24 RAISE;
25 END;
26 BEGIN
27 NULL;
28 EXCEPTION
29 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
30 /*SRW.MESSAGE(1
31 ,'Failed in before report trigger:MSTK')*/NULL;
32 RAISE;
33 END;
34 BEGIN
35 NULL;
36 EXCEPTION
37 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
38 /*SRW.MESSAGE(1
39 ,'Failed in before report trigger:MTLL')*/NULL;
40 RAISE;
41 END;
42 DECLARE
43 M_RETURN_STATUS VARCHAR2(1);
44 M_MSG_COUNT NUMBER;
45 M_MSG_DATA VARCHAR2(2000);
46 BEGIN
47 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => M_RETURN_STATUS
48 ,X_MSG_COUNT => M_MSG_COUNT
49 ,X_MSG_DATA => M_MSG_DATA
50 ,P_ORGANIZATION_ID => P_ORG_ID)) THEN
51 P_WMS_INSTALLED := 'TRUE';
52 END IF;
53 END;
54 RETURN (TRUE);
55 END BEFOREREPORT;
56
57 FUNCTION AFTERREPORT RETURN BOOLEAN IS
58 BEGIN
59 BEGIN
60 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
61 EXCEPTION
62 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
63 /*SRW.MESSAGE(1
64 ,'SRWEXIT failed')*/NULL;
65 END;
66 RETURN (TRUE);
67 END AFTERREPORT;
68
69 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
70 BEGIN
71 RETURN ('(' || R_CURRENCY_CODE || ')');
72 END C_CURRENCY_CODEFORMULA;
73
74 FUNCTION C_WHEREFORMULA RETURN VARCHAR2 IS
75 BEGIN
76 IF P_REPORT_ID = 2 THEN
77 RETURN ('and pit.void_flag <> 1
78 and pit.tag_quantity is null');
79 ELSE
80 RETURN ' ';
81 END IF;
82 RETURN ' ';
83 END C_WHEREFORMULA;
84
85 FUNCTION C_PHYS_INV_NAMEFORMULA RETURN VARCHAR2 IS
86 BEGIN
87 DECLARE
88 NAME VARCHAR2(30);
89 ORG NUMBER;
90 PI_ID NUMBER;
91 BEGIN
92 ORG := P_ORG_ID;
93 PI_ID := P_PHYS_INV_ID;
94 SELECT
95 PHYSICAL_INVENTORY_NAME
96 INTO NAME
97 FROM
98 MTL_PHYSICAL_INVENTORIES
99 WHERE ORGANIZATION_ID = ORG
100 AND PHYSICAL_INVENTORY_ID = PI_ID;
101 RETURN (NAME);
102 EXCEPTION
103 WHEN NO_DATA_FOUND THEN
104 RETURN (NULL);
105 END;
106 RETURN NULL;
107 END C_PHYS_INV_NAMEFORMULA;
108
109 --bug#11709065 changing the function argument
110 FUNCTION CF_OUTERMOST_LPNFORMULA(P_OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
111 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
112 BEGIN
113 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_OUTERMOST_LPN_ID IS NOT NULL)) THEN
114 BEGIN
115 SELECT
116 LICENSE_PLATE_NUMBER
117 INTO M_OUTERMOST_LPN
118 FROM
119 WMS_LICENSE_PLATE_NUMBERS
120 WHERE LPN_ID = P_OUTERMOST_LPN_ID;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 M_OUTERMOST_LPN := 'ERROR';
124 END;
125 END IF;
126 RETURN (M_OUTERMOST_LPN);
127 END CF_OUTERMOST_LPNFORMULA;
128
129 --bug#11709065 changing the function argument
130 FUNCTION CF_PARENT_LPNFORMULA(P_PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
131 M_PARENT_LPN VARCHAR2(30) := NULL;
132 BEGIN
133 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_PARENT_LPN_ID IS NOT NULL)) THEN
134 BEGIN
135 SELECT
136 LICENSE_PLATE_NUMBER
137 INTO M_PARENT_LPN
138 FROM
139 WMS_LICENSE_PLATE_NUMBERS
140 WHERE LPN_ID = P_PARENT_LPN_ID;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 M_PARENT_LPN := 'ERROR';
144 END;
145 END IF;
146 RETURN (M_PARENT_LPN);
147 END CF_PARENT_LPNFORMULA;
148
149 --bug#11709065 changing the function argument
150 FUNCTION CF_COST_GROUPFORMULA(P_COST_GROUP_ID IN NUMBER) RETURN CHAR IS
151 M_COST_GROUP VARCHAR2(10) := NULL;
152 BEGIN
153 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_COST_GROUP_ID IS NOT NULL)) THEN
154 BEGIN
155 SELECT
156 COST_GROUP
157 INTO M_COST_GROUP
158 FROM
159 CST_COST_GROUPS
160 WHERE COST_GROUP_ID = P_COST_GROUP_ID;
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 M_COST_GROUP := 'ERROR';
164 END;
165 END IF;
166 RETURN (M_COST_GROUP);
167 END CF_COST_GROUPFORMULA;
168
169 END INV_INVARPMT_XMLP_PKG;
170