1 PACKAGE BODY GMF_GMFINVAL_XMLP_PKG AS
2 /* $Header: GMFINVALB.pls 120.0 2007/12/24 13:31:48 nchinnam noship $ */
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 L_RETURN := GMF_CMCOMMON.GET_PROCESS_ITEM_COST(P_API_VERSION => 1.0
104 ,P_INIT_MSG_LIST => NULL
105 ,X_RETURN_STATUS => L_RETURN_STATUS
106 ,X_MSG_COUNT => L_MSG_COUNT
107 ,X_MSG_DATA => L_MSG_DATA
108 ,P_INVENTORY_ITEM_ID => INVENTORY_ITEM_ID
109 ,P_ORGANIZATION_ID => ORGANIZATION_ID
110 ,P_TRANSACTION_DATE => TRANSDATE
111 ,P_DETAIL_FLAG => 1
112 ,P_COST_METHOD => L_COST_METHOD
113 ,P_COST_COMPONENT_CLASS_ID => L_COST_COMPONENT_CLASS_ID
114 ,P_COST_ANALYSIS_CODE => L_COST_ANALYSIS_CODE
115 ,X_TOTAL_COST => L_UNIT_COST
116 ,X_NO_OF_ROWS => L_NO_OF_ROWS);
117 OPEN CUR_CMWHSE_ASC(ORGANIZATION_ID,TRANSDATE);
118 FETCH CUR_CMWHSE_ASC
119 INTO X_COST_ORGANIZATION_ID;
120 IF (CUR_CMWHSE_ASC%NOTFOUND) THEN
121 X_COST_ORGANIZATION_ID := UNITCOSTCFFORMULA.ORGANIZATION_ID;
122 ELSE
123 IF NVL(X_COST_ORGANIZATION_ID
124 ,-1) <> UNITCOSTCFFORMULA.ORGANIZATION_ID THEN
125 BEGIN
126 SELECT
127 PRIMARY_UNIT_OF_MEASURE
128 INTO L_FROM_UOM
129 FROM
130 MTL_SYSTEM_ITEMS
131 WHERE INVENTORY_ITEM_ID = UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
132 AND ORGANIZATION_ID = X_COST_ORGANIZATION_ID;
133 EXCEPTION
134 WHEN OTHERS THEN
135 L_FROM_UOM := NULL;
136 END;
137 BEGIN
138 SELECT
139 PRIMARY_UNIT_OF_MEASURE
140 INTO L_TO_UOM
141 FROM
142 MTL_SYSTEM_ITEMS
143 WHERE INVENTORY_ITEM_ID = UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
144 AND ORGANIZATION_ID = UNITCOSTCFFORMULA.ORGANIZATION_ID;
145 EXCEPTION
146 WHEN OTHERS THEN
147 L_TO_UOM := NULL;
148 END;
149 INV_CONVERT.INV_UM_CONVERSION(FROM_UNIT => L_FROM_UOM
150 ,TO_UNIT => L_TO_UOM
151 ,ITEM_ID => UNITCOSTCFFORMULA.INVENTORY_ITEM_ID
152 ,LOT_NUMBER => NULL
153 ,ORGANIZATION_ID => UNITCOSTCFFORMULA.ORGANIZATION_ID
154 ,UOM_RATE => L_UOM_RATE);
155 END IF;
156 END IF;
157 CLOSE CUR_CMWHSE_ASC;
158 IF L_UOM_RATE IS NOT NULL THEN
159 L_UNIT_COST := L_UNIT_COST / L_UOM_RATE;
160 END IF;
161 UNITCOSTCP := ROUND(NVL(L_UNIT_COST,0) * P_EXCHANGE_RATE
162 ,8);
163 /* temp_gmt_insert('L_UNIT_COST',' '||L_UNIT_COST);
164 temp_gmt_insert('P_EXCHANGE_RATE',' '||P_EXCHANGE_RATE);
165 temp_gmt_insert('UNITCOSTCP',' '||UNITCOSTCP);*/
166 RETURN (UNITCOSTCP);
167 END UNITCOSTCFFORMULA;
168 FUNCTION TOTALCOSTCFFORMULA(ITEM_PRIMARY_QUANTITY_CS IN NUMBER) RETURN NUMBER IS
169 BEGIN
170 TOTALCOSTCP := ROUND(UNITCOSTCP * ITEM_PRIMARY_QUANTITY_CS
171 ,8);
172 RETURN (TOTALCOSTCP);
173 END TOTALCOSTCFFORMULA;
174 FUNCTION BALANCECFFORMULA RETURN VARCHAR2 IS
175 BEGIN
176 /*SRW.REFERENCE(BALANCECP)*/NULL;
177 IF Q1ORQ2 = '1' THEN
178 BALANCECP := 'Current Balances';
179 ELSIF Q1ORQ2 = '2' THEN
180 BALANCECP := 'Ending Balances for year ' || P_PERIOD_YEAR || ' and period ' || P_PERIOD_NUM;
181 END IF;
182 RETURN (BALANCECP);
183 END BALANCECFFORMULA;
184 FUNCTION CF_BASE_CURRENCYFORMULA RETURN VARCHAR2 IS
185 L_BASE_CURRENCY_CODE GL_PLCY_MST.BASE_CURRENCY_CODE%TYPE;
186 CURSOR CUR_CURRENCY_CODE(CP_LEGAL_ENTITY_ID IN GMF_FISCAL_POLICIES.LEGAL_ENTITY_ID%TYPE) IS
187 SELECT
188 BASE_CURRENCY_CODE
189 FROM
190 GMF_FISCAL_POLICIES
191 WHERE LEGAL_ENTITY_ID = CP_LEGAL_ENTITY_ID;
192 BEGIN
193 OPEN CUR_CURRENCY_CODE(P_LEGAL_ENTITY_ID);
194 FETCH CUR_CURRENCY_CODE
195 INTO L_BASE_CURRENCY_CODE;
196 CLOSE CUR_CURRENCY_CODE;
197 RETURN (L_BASE_CURRENCY_CODE);
198 END CF_BASE_CURRENCYFORMULA;
199 FUNCTION AFTERREPORT RETURN BOOLEAN IS
200 BEGIN
201 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
202 RETURN (TRUE);
203 END AFTERREPORT;
204 FUNCTION ORGANIZATIONCPFORMULA(ORGANIZATIONCF IN VARCHAR2) RETURN VARCHAR2 IS
205 BEGIN
206 /*SRW.REFERENCE(ORGANIZATIONCF)*/NULL;
207 END ORGANIZATIONCPFORMULA;
208 FUNCTION ITEMCPFORMULA(ITEMCF IN VARCHAR2) RETURN CHAR IS
209 BEGIN
210 /*SRW.REFERENCE(ITEMCF)*/NULL;
211 END ITEMCPFORMULA;
212 FUNCTION ITEMCFFORMULA RETURN CHAR IS
213 BEGIN
214 /*SRW.REFERENCE(ITEMCP)*/NULL;
215 IF FROM_ITEM_NUM IS NOT NULL AND TO_ITEM_NUM IS NOT NULL THEN
216 ITEMCP := ' and iim.item_number >= ''' || FROM_ITEM_NUM || ''' and iim.item_number <= ''' || TO_ITEM_NUM || '''';
217 ELSIF FROM_ITEM_NUM IS NULL AND TO_ITEM_NUM IS NULL THEN
218 ITEMCP := 'and 1=1';
219 FROM_ITEM_NUM := SY_ALL;
220 TO_ITEM_NUM := SY_ALL;
221 ELSIF FROM_ITEM_NUM IS NOT NULL AND TO_ITEM_NUM IS NULL THEN
222 ITEMCP := ' and iim.item_number >= ''' || FROM_ITEM_NUM || '''';
223 TO_ITEM_NUM := SY_ALL;
224 ELSIF FROM_ITEM_NUM IS NULL AND TO_ITEM_NUM IS NOT NULL THEN
225 ITEMCP := ' and iim.item_number <= ''' || TO_ITEM_NUM || '''';
226 FROM_ITEM_NUM := SY_ALL;
227 END IF;
228 RETURN NULL;
229 END ITEMCFFORMULA;
230 FUNCTION ORGANIZATIONCFFORMULA RETURN VARCHAR2 IS
231 BEGIN
232 /*SRW.REFERENCE(ORGANIZATIONCP)*/NULL;
233 IF FROM_ORGN_CODE IS NOT NULL AND TO_ORGN_CODE IS NOT NULL THEN
234 ORGANIZATIONCP := ' and mp.organization_code >= ''' || FROM_ORGN_CODE || ''' and mp.organization_code <= ''' || TO_ORGN_CODE || '''';
235 ELSIF FROM_ORGN_CODE IS NULL AND TO_ORGN_CODE IS NULL THEN
236 ORGANIZATIONCP := 'and 1=1';
237 FROM_ORGN_CODE := SY_ALL;
238 TO_ORGN_CODE := SY_ALL;
239 ELSIF FROM_ORGN_CODE IS NOT NULL AND TO_ORGN_CODE IS NULL THEN
240 ORGANIZATIONCP := ' and mp.organization_code >= ''' || FROM_ORGN_CODE || '''';
241 TO_ORGN_CODE := SY_ALL;
242 ELSIF FROM_ORGN_CODE IS NULL AND TO_ORGN_CODE IS NOT NULL THEN
243 ORGANIZATIONCP := ' and mp.organization_code <= ''' || TO_ORGN_CODE || '''';
244 FROM_ORGN_CODE := SY_ALL;
245 END IF;
246 RETURN NULL;
247 END ORGANIZATIONCFFORMULA;
248 FUNCTION LEGAL_ENTITY_NAME_CFFORMULA RETURN VARCHAR2 IS
249 CURSOR CUR_LEGAL_ENTITY_NAME IS
250 SELECT
251 LEGAL_ENTITY_NAME
252 FROM
253 GMF_LEGAL_ENTITIES
254 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
255 L_LEGAL_ENTITY_NAME GMF_LEGAL_ENTITIES.LEGAL_ENTITY_NAME%TYPE;
256 BEGIN
257 OPEN CUR_LEGAL_ENTITY_NAME;
258 FETCH CUR_LEGAL_ENTITY_NAME
259 INTO L_LEGAL_ENTITY_NAME;
260 CLOSE CUR_LEGAL_ENTITY_NAME;
261 RETURN (L_LEGAL_ENTITY_NAME);
262 END LEGAL_ENTITY_NAME_CFFORMULA;
263 FUNCTION TOTALCOSTCP_P RETURN NUMBER IS
264 BEGIN
265 RETURN TOTALCOSTCP;
266 END TOTALCOSTCP_P;
267 FUNCTION UNITCOSTCP_P RETURN NUMBER IS
268 BEGIN
269 RETURN UNITCOSTCP;
270 END UNITCOSTCP_P;
271 FUNCTION BALANCECP_P RETURN VARCHAR2 IS
272 BEGIN
273 RETURN BALANCECP;
274 END BALANCECP_P;
275 FUNCTION ORGANIZATIONCP_P RETURN VARCHAR2 IS
276 BEGIN
277 RETURN ORGANIZATIONCP;
278 END ORGANIZATIONCP_P;
279 FUNCTION ITEMCP_P RETURN VARCHAR2 IS
280 BEGIN
281 RETURN ITEMCP;
282 END ITEMCP_P;
283 END GMF_GMFINVAL_XMLP_PKG;
284
[Home] [Help]