[Home] [Help]
PACKAGE BODY: APPS.GMF_GMFINVAL_XMLP_PKG
Source
1 PACKAGE BODY GMF_GMFINVAL_XMLP_PKG AS
2 /* $Header: GMFINVALB.pls 120.1.12020000.2 2012/07/24 14:41:09 spabolu ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_BASE_CURRENCY_CODE GMF_FISCAL_POLICIES.BASE_CURRENCY_CODE%TYPE;
5 CURSOR CUR_CURRENCY_CODE(CP_LEGAL_ENTITY_ID IN GMF_FISCAL_POLICIES.LEGAL_ENTITY_ID%TYPE) IS
6 SELECT
7 BASE_CURRENCY_CODE
8 FROM
9 GMF_FISCAL_POLICIES
10 WHERE LEGAL_ENTITY_ID = CP_LEGAL_ENTITY_ID;
11 BEGIN
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 OPEN CUR_CURRENCY_CODE(P_LEGAL_ENTITY_ID);
14 FETCH CUR_CURRENCY_CODE
15 INTO L_BASE_CURRENCY_CODE;
16 CLOSE CUR_CURRENCY_CODE;
17 IF ((P_CURRENCY_CODE IS NULL) OR (P_CURRENCY_CODE = L_BASE_CURRENCY_CODE)) THEN
18 P_CURRENCY_CODE := L_BASE_CURRENCY_CODE;
19 P_EXCHANGE_RATE := 1;
20 ELSE
21 IF (P_EXCHANGE_RATE IS NULL) THEN
22 FND_MESSAGE.SET_NAME('GMF'
23 ,'GMF_SRS_EFC_NO_XCHG');
24 /*SRW.MESSAGE(100
25 ,FND_MESSAGE.GET)*/NULL;
26 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
27 END IF;
28 END IF;
29 RETURN (TRUE);
30 END BEFOREREPORT;
31 PROCEDURE HEADER IS
32 BEGIN
33 NULL;
34 END HEADER;
35 FUNCTION AFTERPFORM RETURN BOOLEAN IS
36 BEGIN
37 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
38 SELECT argument7 INTO P_CURRENT FROM FND_CONCURRENT_REQUESTS WHERE request_id=P_CONC_REQUEST_ID;
39 FROM_ORGN_CODE := GMF_UTILITIES_GRP.GET_ORGANIZATION_CODE(P_FROM_ORGANIZATION_ID);
40 TO_ORGN_CODE := GMF_UTILITIES_GRP.GET_ORGANIZATION_CODE(P_TO_ORGANIZATION_ID);
41 FROM_ITEM_NUM := GMF_UTILITIES_GRP.GET_ITEM_NUMBER(P_FROM_ITEM_ID
42 ,P_ORGANIZATION_CONTEXT);
43 TO_ITEM_NUM := GMF_UTILITIES_GRP.GET_ITEM_NUMBER(P_TO_ITEM_ID
44 ,P_ORGANIZATION_CONTEXT);
45 IF FROM_ORGN_CODE > TO_ORGN_CODE THEN
46 FND_MESSAGE.SET_NAME('GMF'
47 ,'CM_FRMWHSECODE_TOWHSECODE');
48 /*SRW.MESSAGE(100
49 ,FND_MESSAGE.GET)*/NULL;
50 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
51 END IF;
52 IF FROM_ITEM_NUM > TO_ITEM_NUM THEN
53 FND_MESSAGE.SET_NAME('GMF'
54 ,'CM_FRMITEM_LTE_TOITM');
55 /*SRW.MESSAGE(100
56 ,FND_MESSAGE.GET)*/NULL;
57 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
58 END IF;
59 CURRENT_DUMMY := P_CURRENT;
60 IF (P_CURRENT = 'N') THEN
61 P_CURRENT := NULL;
62 END IF;
63 IF P_PERIOD_YEAR IS NOT NULL AND P_PERIOD_NUM IS NOT NULL THEN
64 IF P_CURRENT IS NULL THEN
65 Q1ORQ2 := '2';
66 RETURN (TRUE);
67 END IF;
68 END IF;
69 IF P_CURRENT IS NOT NULL THEN
70 IF P_PERIOD_YEAR IS NULL AND P_PERIOD_NUM IS NULL THEN
71 Q1ORQ2 := '1';
72 RETURN (TRUE);
73 END IF;
74 END IF;
75 RETURN (TRUE);
76 END AFTERPFORM;
77 FUNCTION UNITCOSTCFFORMULA(INVENTORY_ITEM_ID IN NUMBER
78 ,ORGANIZATION_ID IN NUMBER
79 ,TRANSDATE IN DATE) RETURN NUMBER IS
80 L_RETURN NUMBER;
81 L_RETURN_STATUS VARCHAR2(100);
82 L_MSG_COUNT VARCHAR2(100);
83 L_MSG_DATA VARCHAR2(100);
84 L_COST_METHOD VARCHAR2(100);
85 L_COST_COMPONENT_CLASS_ID NUMBER;
86 L_COST_ANALYSIS_CODE VARCHAR2(100);
87 L_UNIT_COST NUMBER;
88 L_NO_OF_ROWS NUMBER;
89 L_UOM_RATE NUMBER;
90 CURSOR CUR_CMWHSE_ASC(V_ORGANIZATION_ID IN NUMBER,V_TRANS_DATE IN DATE) IS
91 SELECT
92 COST_ORGANIZATION_ID
93 FROM
94 CM_WHSE_ASC
95 WHERE ORGANIZATION_ID = V_ORGANIZATION_ID
96 AND EFF_START_DATE <= V_TRANS_DATE
97 AND EFF_END_DATE >= V_TRANS_DATE
98 AND DELETE_MARK = 0;
99 X_COST_ORGANIZATION_ID MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
100 L_FROM_UOM MTL_SYSTEM_ITEMS_B.PRIMARY_UNIT_OF_MEASURE%TYPE;
101 L_TO_UOM MTL_SYSTEM_ITEMS_B.PRIMARY_UNIT_OF_MEASURE%TYPE;
102 BEGIN
103
104 /* Commented against Bug#12970368
105 L_RETURN := GMF_CMCOMMON.GET_PROCESS_ITEM_COST(P_API_VERSION => 1.0
106 ,P_INIT_MSG_LIST => NULL
107 ,X_RETURN_STATUS => L_RETURN_STATUS
108 ,X_MSG_COUNT => L_MSG_COUNT
109 ,X_MSG_DATA => L_MSG_DATA
110 ,P_INVENTORY_ITEM_ID => INVENTORY_ITEM_ID
111 ,P_ORGANIZATION_ID => ORGANIZATION_ID
112 ,P_TRANSACTION_DATE => TRANSDATE
113 ,P_DETAIL_FLAG => 1
114 ,P_COST_METHOD => L_COST_METHOD
115 ,P_COST_COMPONENT_CLASS_ID => L_COST_COMPONENT_CLASS_ID
116 ,P_COST_ANALYSIS_CODE => L_COST_ANALYSIS_CODE
117 ,X_TOTAL_COST => L_UNIT_COST
118 ,X_NO_OF_ROWS => L_NO_OF_ROWS);
119 */
120
121 /* Bug#12970368 Begins */
122
123 IF Q1ORQ2 = '1' THEN
124 L_RETURN := GMF_CMCOMMON.GET_PROCESS_ITEM_COST(P_API_VERSION => 1.0
125 ,P_INIT_MSG_LIST => NULL
126 ,X_RETURN_STATUS => L_RETURN_STATUS
127 ,X_MSG_COUNT => L_MSG_COUNT
128 ,X_MSG_DATA => L_MSG_DATA
129 ,P_INVENTORY_ITEM_ID => INVENTORY_ITEM_ID
130 ,P_ORGANIZATION_ID => ORGANIZATION_ID
131 ,P_TRANSACTION_DATE => TRANSDATE
132 ,P_DETAIL_FLAG => 1
133 ,P_COST_METHOD => L_COST_METHOD
134 ,P_COST_COMPONENT_CLASS_ID => L_COST_COMPONENT_CLASS_ID
135 ,P_COST_ANALYSIS_CODE => L_COST_ANALYSIS_CODE
136 ,X_TOTAL_COST => L_UNIT_COST
137 ,X_NO_OF_ROWS => L_NO_OF_ROWS
138 ,p_from_where => 'MOQD'
139 ,p_fiscal_year => P_PERIOD_YEAR
140 ,p_period => P_PERIOD_NUM);
141 ELSIF Q1ORQ2 = '2' THEN
142 L_RETURN := GMF_CMCOMMON.GET_PROCESS_ITEM_COST(P_API_VERSION => 1.0
143 ,P_INIT_MSG_LIST => NULL
144 ,X_RETURN_STATUS => L_RETURN_STATUS
145 ,X_MSG_COUNT => L_MSG_COUNT
146 ,X_MSG_DATA => L_MSG_DATA
147 ,P_INVENTORY_ITEM_ID => INVENTORY_ITEM_ID
148 ,P_ORGANIZATION_ID => ORGANIZATION_ID
149 ,P_TRANSACTION_DATE => TRANSDATE
150 ,P_DETAIL_FLAG => 1
151 ,P_COST_METHOD => L_COST_METHOD
152 ,P_COST_COMPONENT_CLASS_ID => L_COST_COMPONENT_CLASS_ID
153 ,P_COST_ANALYSIS_CODE => L_COST_ANALYSIS_CODE
154 ,X_TOTAL_COST => L_UNIT_COST
155 ,X_NO_OF_ROWS => L_NO_OF_ROWS
156 ,p_from_where => 'GPB'
157 ,p_fiscal_year => P_PERIOD_YEAR
158 ,p_period => P_PERIOD_NUM);
159 END IF;
160
161 /* Bug#12970368 Ends */
162
163
164 OPEN CUR_CMWHSE_ASC(ORGANIZATION_ID,TRANSDATE);
165 FETCH CUR_CMWHSE_ASC
166 INTO X_COST_ORGANIZATION_ID;
167 IF (CUR_CMWHSE_ASC%NOTFOUND) THEN
168 X_COST_ORGANIZATION_ID := UNITCOSTCFFORMULA.ORGANIZATION_ID;
169 ELSE
170 IF NVL(X_COST_ORGANIZATION_ID
171 ,-1) <> UNITCOSTCFFORMULA.ORGANIZATION_ID THEN
172 BEGIN
173 SELECT
174 PRIMARY_UNIT_OF_MEASURE
175 INTO L_FROM_UOM
176 FROM
177 MTL_SYSTEM_ITEMS
178 WHERE INVENTORY_ITEM_ID = UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
179 AND ORGANIZATION_ID = X_COST_ORGANIZATION_ID;
180 EXCEPTION
181 WHEN OTHERS THEN
182 L_FROM_UOM := NULL;
183 END;
184 BEGIN
185 SELECT
186 PRIMARY_UNIT_OF_MEASURE
187 INTO L_TO_UOM
188 FROM
189 MTL_SYSTEM_ITEMS
190 WHERE INVENTORY_ITEM_ID = UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
191 AND ORGANIZATION_ID = UNITCOSTCFFORMULA.ORGANIZATION_ID;
192 EXCEPTION
193 WHEN OTHERS THEN
194 L_TO_UOM := NULL;
195 END;
196 INV_CONVERT.INV_UM_CONVERSION(FROM_UNIT => L_FROM_UOM
197 ,TO_UNIT => L_TO_UOM
198 ,ITEM_ID => UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
199 ,LOT_NUMBER => NULL
200 ,ORGANIZATION_ID => UNITCOSTCFFORMULA.ORGANIZATION_ID
201 ,UOM_RATE => L_UOM_RATE);
202 END IF;
203 END IF;
204 CLOSE CUR_CMWHSE_ASC;
205 IF L_UOM_RATE IS NOT NULL THEN
206 L_UNIT_COST := L_UNIT_COST / L_UOM_RATE;
207 END IF;
208 UNITCOSTCP := ROUND(NVL(L_UNIT_COST,0) * P_EXCHANGE_RATE
209 ,8);
210 /* temp_gmt_insert('L_UNIT_COST',' '||L_UNIT_COST);
211 temp_gmt_insert('P_EXCHANGE_RATE',' '||P_EXCHANGE_RATE);
212 temp_gmt_insert('UNITCOSTCP',' '||UNITCOSTCP);*/
213 RETURN (UNITCOSTCP);
214 END UNITCOSTCFFORMULA;
215 FUNCTION TOTALCOSTCFFORMULA(ITEM_PRIMARY_QUANTITY_CS IN NUMBER) RETURN NUMBER IS
216 BEGIN
217 TOTALCOSTCP := ROUND(UNITCOSTCP * ITEM_PRIMARY_QUANTITY_CS
218 ,8);
219 RETURN (TOTALCOSTCP);
220 END TOTALCOSTCFFORMULA;
221 FUNCTION BALANCECFFORMULA RETURN VARCHAR2 IS
222 BEGIN
223 /*SRW.REFERENCE(BALANCECP)*/NULL;
224 IF Q1ORQ2 = '1' THEN
225 BALANCECP := 'Current Balances';
226 ELSIF Q1ORQ2 = '2' THEN
227 BALANCECP := 'Ending Balances for year ' || P_PERIOD_YEAR || ' and period ' || P_PERIOD_NUM;
228 END IF;
229 RETURN (BALANCECP);
230 END BALANCECFFORMULA;
231 FUNCTION CF_BASE_CURRENCYFORMULA RETURN VARCHAR2 IS
232 L_BASE_CURRENCY_CODE GL_PLCY_MST.BASE_CURRENCY_CODE%TYPE;
233 CURSOR CUR_CURRENCY_CODE(CP_LEGAL_ENTITY_ID IN GMF_FISCAL_POLICIES.LEGAL_ENTITY_ID%TYPE) IS
234 SELECT
235 BASE_CURRENCY_CODE
236 FROM
237 GMF_FISCAL_POLICIES
238 WHERE LEGAL_ENTITY_ID = CP_LEGAL_ENTITY_ID;
239 BEGIN
240 OPEN CUR_CURRENCY_CODE(P_LEGAL_ENTITY_ID);
241 FETCH CUR_CURRENCY_CODE
242 INTO L_BASE_CURRENCY_CODE;
243 CLOSE CUR_CURRENCY_CODE;
244 RETURN (L_BASE_CURRENCY_CODE);
245 END CF_BASE_CURRENCYFORMULA;
246 FUNCTION AFTERREPORT RETURN BOOLEAN IS
247 BEGIN
248 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
249 RETURN (TRUE);
250 END AFTERREPORT;
251 FUNCTION ORGANIZATIONCPFORMULA(ORGANIZATIONCF IN VARCHAR2) RETURN VARCHAR2 IS
252 BEGIN
253 /*SRW.REFERENCE(ORGANIZATIONCF)*/NULL;
254 END ORGANIZATIONCPFORMULA;
255 FUNCTION ITEMCPFORMULA(ITEMCF IN VARCHAR2) RETURN CHAR IS
256 BEGIN
257 /*SRW.REFERENCE(ITEMCF)*/NULL;
258 END ITEMCPFORMULA;
259 FUNCTION ITEMCFFORMULA RETURN CHAR IS
260 BEGIN
261 /*SRW.REFERENCE(ITEMCP)*/NULL;
262 IF FROM_ITEM_NUM IS NOT NULL AND TO_ITEM_NUM IS NOT NULL THEN
263 ITEMCP := ' and iim.item_number >= ''' || FROM_ITEM_NUM || ''' and iim.item_number <= ''' || TO_ITEM_NUM || '''';
264 ELSIF FROM_ITEM_NUM IS NULL AND TO_ITEM_NUM IS NULL THEN
265 ITEMCP := 'and 1=1';
266 FROM_ITEM_NUM := SY_ALL;
267 TO_ITEM_NUM := SY_ALL;
268 ELSIF FROM_ITEM_NUM IS NOT NULL AND TO_ITEM_NUM IS NULL THEN
269 ITEMCP := ' and iim.item_number >= ''' || FROM_ITEM_NUM || '''';
270 TO_ITEM_NUM := SY_ALL;
271 ELSIF FROM_ITEM_NUM IS NULL AND TO_ITEM_NUM IS NOT NULL THEN
272 ITEMCP := ' and iim.item_number <= ''' || TO_ITEM_NUM || '''';
273 FROM_ITEM_NUM := SY_ALL;
274 END IF;
275 RETURN NULL;
276 END ITEMCFFORMULA;
277 FUNCTION ORGANIZATIONCFFORMULA RETURN VARCHAR2 IS
278 BEGIN
279 /*SRW.REFERENCE(ORGANIZATIONCP)*/NULL;
280 IF FROM_ORGN_CODE IS NOT NULL AND TO_ORGN_CODE IS NOT NULL THEN
281 ORGANIZATIONCP := ' and mp.organization_code >= ''' || FROM_ORGN_CODE || ''' and mp.organization_code <= ''' || TO_ORGN_CODE || '''';
282 ELSIF FROM_ORGN_CODE IS NULL AND TO_ORGN_CODE IS NULL THEN
283 ORGANIZATIONCP := 'and 1=1';
284 FROM_ORGN_CODE := SY_ALL;
285 TO_ORGN_CODE := SY_ALL;
286 ELSIF FROM_ORGN_CODE IS NOT NULL AND TO_ORGN_CODE IS NULL THEN
287 ORGANIZATIONCP := ' and mp.organization_code >= ''' || FROM_ORGN_CODE || '''';
288 TO_ORGN_CODE := SY_ALL;
289 ELSIF FROM_ORGN_CODE IS NULL AND TO_ORGN_CODE IS NOT NULL THEN
290 ORGANIZATIONCP := ' and mp.organization_code <= ''' || TO_ORGN_CODE || '''';
291 FROM_ORGN_CODE := SY_ALL;
292 END IF;
293 RETURN NULL;
294 END ORGANIZATIONCFFORMULA;
295 FUNCTION LEGAL_ENTITY_NAME_CFFORMULA RETURN VARCHAR2 IS
296 CURSOR CUR_LEGAL_ENTITY_NAME IS
297 SELECT
298 LEGAL_ENTITY_NAME
299 FROM
300 GMF_LEGAL_ENTITIES
301 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
302 L_LEGAL_ENTITY_NAME GMF_LEGAL_ENTITIES.LEGAL_ENTITY_NAME%TYPE;
303 BEGIN
304 OPEN CUR_LEGAL_ENTITY_NAME;
305 FETCH CUR_LEGAL_ENTITY_NAME
306 INTO L_LEGAL_ENTITY_NAME;
307 CLOSE CUR_LEGAL_ENTITY_NAME;
308 RETURN (L_LEGAL_ENTITY_NAME);
309 END LEGAL_ENTITY_NAME_CFFORMULA;
310 FUNCTION TOTALCOSTCP_P RETURN NUMBER IS
311 BEGIN
312 RETURN TOTALCOSTCP;
313 END TOTALCOSTCP_P;
314 FUNCTION UNITCOSTCP_P RETURN NUMBER IS
315 BEGIN
316 RETURN UNITCOSTCP;
317 END UNITCOSTCP_P;
318 FUNCTION BALANCECP_P RETURN VARCHAR2 IS
319 BEGIN
320 RETURN BALANCECP;
321 END BALANCECP_P;
322 FUNCTION ORGANIZATIONCP_P RETURN VARCHAR2 IS
323 BEGIN
324 RETURN ORGANIZATIONCP;
325 END ORGANIZATIONCP_P;
326 FUNCTION ITEMCP_P RETURN VARCHAR2 IS
327 BEGIN
328 RETURN ITEMCP;
329 END ITEMCP_P;
330 END GMF_GMFINVAL_XMLP_PKG;
331