1 PACKAGE BODY OKS_OKSCODET_XMLP_PKG AS
2 /* $Header: OKSCODETB.pls 120.2 2008/02/21 06:11:43 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 apf boolean;
5 BEGIN
6 apf := afterpform;
7 IF P_OPERATING_UNIT IS NOT NULL THEN
8 MO_GLOBAL.SET_POLICY_CONTEXT('S'
9 ,P_OPERATING_UNIT);
10 END IF;
11 RETURN (TRUE);
12 END BEFOREREPORT;
13
14 FUNCTION CF_PARTYROLEFORMULA(RLE_CODE IN VARCHAR2) RETURN CHAR IS
15 CURSOR C1 IS
16 SELECT
17 MEANING
18 FROM
19 FND_LOOKUPS
20 WHERE LOOKUP_TYPE = 'OKC_ROLE'
21 AND LOOKUP_CODE = RLE_CODE;
22 LV_PARTYROLE VARCHAR2(80);
23 BEGIN
24 OPEN C1;
25 FETCH C1
26 INTO LV_PARTYROLE;
27 CLOSE C1;
28 RETURN (LV_PARTYROLE);
29 END CF_PARTYROLEFORMULA;
30
31 FUNCTION CF_PARTYNAMEFORMULA(OBJECT1_ID1 IN VARCHAR2
32 ,JTOT_OBJECT1_CODE IN VARCHAR2) RETURN CHAR IS
33 CURSOR C1 IS
34 SELECT
35 PARTY_NUMBER || '-' || NAME
36 FROM
37 OKX_PARTIES_V
38 WHERE ID1 = OBJECT1_ID1;
39 CURSOR C2 IS
40 SELECT
41 NAME
42 FROM
43 OKX_ORGANIZATION_DEFS_V
44 WHERE ORGANIZATION_TYPE = 'OPERATING_UNIT'
45 AND INFORMATION_TYPE = 'Operating Unit Information'
46 AND ID1 = OBJECT1_ID1;
47 LV_PARTYNAME VARCHAR2(400);
48 BEGIN
49 IF JTOT_OBJECT1_CODE = 'OKX_PARTY' THEN
50 OPEN C1;
51 FETCH C1
52 INTO LV_PARTYNAME;
53 CLOSE C1;
54 ELSIF JTOT_OBJECT1_CODE = 'OKX_OPERUNIT' THEN
55 OPEN C2;
56 FETCH C2
57 INTO LV_PARTYNAME;
58 CLOSE C2;
59 END IF;
60 RETURN LV_PARTYNAME;
61 END CF_PARTYNAMEFORMULA;
62
63 FUNCTION CF_PRICE_NEGOTIATED_ROUNDFORMU(PRICE_NEGOTIATED IN NUMBER
64 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
65 LN_PROD_VALUE NUMBER;
66 LV_CURRENCY_CODE VARCHAR2(400);
67 LN_PROD_VALUE_ROUND NUMBER;
68 BEGIN
69 LN_PROD_VALUE := PRICE_NEGOTIATED;
70 LV_CURRENCY_CODE := CURRENCY_CODE;
71 LN_PROD_VALUE_ROUND := OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_PROD_VALUE
72 ,LV_CURRENCY_CODE);
73 RETURN (LN_PROD_VALUE_ROUND);
74 END CF_PRICE_NEGOTIATED_ROUNDFORMU;
75
76 FUNCTION CF_SRV_PRICE_ROUNDFORMULA(SL_PRICE_NEGOTIATED IN NUMBER
77 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
78 LN_PROD_VALUE NUMBER;
79 LV_CURRENCY_CODE VARCHAR2(400);
80 LN_PROD_VALUE_ROUND NUMBER;
81 BEGIN
82 LN_PROD_VALUE := SL_PRICE_NEGOTIATED;
83 LV_CURRENCY_CODE := CURRENCY_CODE;
84 LN_PROD_VALUE_ROUND := OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_PROD_VALUE
85 ,LV_CURRENCY_CODE);
86 RETURN (LN_PROD_VALUE_ROUND);
87 END CF_SRV_PRICE_ROUNDFORMULA;
88
89 FUNCTION CF_HDR_PRICE_ROUNDFORMULA(CS_HDR_PRICE IN NUMBER
90 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
91 LN_PROD_VALUE NUMBER;
92 LV_CURRENCY_CODE VARCHAR2(400);
93 LN_PROD_VALUE_ROUND NUMBER;
94 BEGIN
95 LN_PROD_VALUE := CS_HDR_PRICE;
96 LV_CURRENCY_CODE := CURRENCY_CODE;
97 LN_PROD_VALUE_ROUND := OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_PROD_VALUE
98 ,LV_CURRENCY_CODE);
99 RETURN (LN_PROD_VALUE_ROUND);
100 END CF_HDR_PRICE_ROUNDFORMULA;
101
102 FUNCTION CF_PRODUCT_DETAILSFORMULA(CL_ID IN NUMBER) RETURN NUMBER IS
103 CURSOR C1 IS
104 SELECT
105 XP.NAME,
106 XP.CURRENT_SERIAL_NUMBER,
107 XP.REFERENCE_NUMBER,
108 XP.QUANTITY,
109 XP.PRICING_ATTRIBUTE3,
110 XP.PRICING_ATTRIBUTE4,
111 XP.PRICING_ATTRIBUTE5,
112 XP.SYSTEM_ID
113 FROM
114 OKC_K_ITEMS IT,
115 OKX_CUSTOMER_PRODUCTS_V XP
116 WHERE IT.CLE_ID = CL_ID
117 AND IT.JTOT_OBJECT1_CODE = 'OKX_CUSTPROD'
118 AND XP.ID1 = IT.OBJECT1_ID1
119 AND XP.ID2 = IT.OBJECT1_ID2;
120 CURSOR C2(CN_SYSTEM_ID IN NUMBER) IS
121 SELECT
122 NAME
123 FROM
124 OKX_SYSTEMS_V
125 WHERE ID1 = CN_SYSTEM_ID
126 AND ID2 = '#';
127 LN_SYSTEM_ID NUMBER;
128 BEGIN
129 CP_PROD_NAME := NULL;
130 CP_CSI := NULL;
131 CP_PROD_SERIAL_NO := NULL;
132 CP_PROD_REF_NO := NULL;
133 CP_PROD_QTY := NULL;
134 CP_NUM_OF_USERS := NULL;
135 CP_PRICING_LIC_LVL := NULL;
136 CP_LIC_LVL := NULL;
137 OPEN C1;
138 FETCH C1
139 INTO CP_PROD_NAME,CP_PROD_SERIAL_NO,CP_PROD_REF_NO,CP_PROD_QTY,CP_NUM_OF_USERS,CP_PRICING_LIC_LVL,CP_LIC_LVL,LN_SYSTEM_ID;
140 CLOSE C1;
141 OPEN C2(LN_SYSTEM_ID);
142 FETCH C2
143 INTO CP_CSI;
144 CLOSE C2;
145 --select s1.nextval into seq_val from dual;
146 --autonomous_proc('val1', to_char(sysdate, 'dd-mon-yy hh:mi:ss')|| ' @ '||seq_val , 'Raj', 'Raj');
147 RETURN (1);
148 END CF_PRODUCT_DETAILSFORMULA;
149
150 FUNCTION CF_SERVICE_LEVELFORMULA(SL_LSE_ID IN NUMBER
151 ,SL_ID IN NUMBER) RETURN CHAR IS
152 CURSOR C1 IS
153 SELECT
154 LTRIM(RTRIM(NAME))
155 FROM
156 OKC_LINE_STYLES_V
157 WHERE ID = SL_LSE_ID;
158 CURSOR C2 IS
159 SELECT
160 NAME,
161 DESCRIPTION
162 FROM
163 OKC_K_ITEMS IT,
164 OKX_SYSTEM_ITEMS_V XI
165 WHERE IT.CLE_ID = SL_ID
166 AND XI.ID1 = IT.OBJECT1_ID1
167 AND XI.ID2 = IT.OBJECT1_ID2;
168 LV_LINE_STYLE VARCHAR2(200);
169 LV_SRV_NAME VARCHAR2(300);
170 LV_SRV_DESC VARCHAR2(300);
171 LV_PROF_SRV_NAME VARCHAR2(300);
172 LV_PROF_SRV_DESC VARCHAR2(300);
173 LV_PROF_NAME CONSTANT VARCHAR2(300) DEFAULT 'OKS_ITEM_DISPLAY_PREFERENCE';
174 LV_PROF_VALUE VARCHAR2(300);
175 LV_SERVICE_LEVEL VARCHAR2(500);
176 BEGIN
177 OPEN C1;
178 FETCH C1
179 INTO LV_LINE_STYLE;
180 CLOSE C1;
181 OPEN C2;
182 FETCH C2
183 INTO LV_SRV_NAME,LV_SRV_DESC;
184 CLOSE C2;
185 FND_PROFILE.GET(LV_PROF_NAME
186 ,LV_PROF_VALUE);
187 IF LV_PROF_VALUE = 'DISPLAY_NAME' THEN
188 LV_PROF_SRV_NAME := LV_SRV_NAME;
189 LV_PROF_SRV_DESC := LV_SRV_DESC;
190 ELSE
191 LV_PROF_SRV_NAME := LV_SRV_DESC;
192 LV_PROF_SRV_DESC := LV_SRV_NAME;
193 END IF;
194 LV_SERVICE_LEVEL := LV_LINE_STYLE || ' - ' || LV_PROF_SRV_NAME;
195 RETURN (LV_SERVICE_LEVEL);
196 END CF_SERVICE_LEVELFORMULA;
197
198 FUNCTION AFTERPFORM RETURN BOOLEAN IS
199 BEGIN
200 BEGIN
201 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
202 --select s1.nextval into seq_val from dual;
203 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
204 EXCEPTION
205 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
206 /*SRW.MESSAGE(1
207 ,'srw_init')*/NULL;
208 END;
209
210 --autonomous_proc('P_CONTRACT_ID', P_CONTRACT_ID, 'Raj', 'Raj');
211 --autonomous_proc('P_CONTRACT_ID_WHERE', P_CONTRACT_ID_WHERE, 'Raj', 'Raj');
212
213 IF P_CONTRACT_GROUP IS NOT NULL THEN
214 P_CONTRACT_GROUP_WHERE := ' and hr1.id in ( select INCLUDED_CHR_ID from okc_k_grpings
215 start with CGP_PARENT_ID = :p_contract_group
216 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) ';
217 END IF;
218 IF P_CONTRACT_ID IS NOT NULL THEN
219 P_CONTRACT_ID_WHERE := ' and hr1.id = :p_contract_id';
220 END IF;
221 IF P_CUSTOMER_ID IS NOT NULL THEN
222 P_CUSTOMER_ID_WHERE := ' and rl.object1_id1 = :p_customer_id
223 AND RL.Rle_Code = ''CUSTOMER'' AND RL.Jtot_Object1_Code = ''OKX_PARTY''';
224 END IF;
225 IF P_STATUS_TYPE IS NOT NULL THEN
226 P_STATUS_TYPE_WHERE := ' and st.ste_code = :p_status_type';
227 END IF;
228 IF P_STATUS_CODE IS NOT NULL THEN
229 P_STATUS_CODE_WHERE := ' and hr1.sts_code = :p_status_code';
230 END IF;
231 IF P_START_DATE_FROM IS NOT NULL THEN
232 P_START_DATE_FROM_WHERE := ' and hr1.start_date >= :p_start_date_from';
233 END IF;
234 IF P_START_DATE_TO IS NOT NULL THEN
235 P_START_DATE_TO_WHERE := ' and hr1.start_date <= :p_start_date_to';
236 END IF;
237 IF P_END_DATE_FROM IS NOT NULL THEN
238 P_END_DATE_FROM_WHERE := ' and hr1.end_date >= :p_end_date_from';
239 END IF;
240 IF P_END_DATE_TO IS NOT NULL THEN
241 P_END_DATE_TO_WHERE := ' and hr1.end_date <= :p_end_date_to';
242 END IF;
243 RETURN (TRUE);
244 END AFTERPFORM;
245
246 FUNCTION AFTERREPORT RETURN BOOLEAN IS
247 BEGIN
248 BEGIN
249 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
250 EXCEPTION
251 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
252 /*SRW.MESSAGE(1
253 ,'srw_exit')*/NULL;
254 END;
255 RETURN (TRUE);
256 END AFTERREPORT;
257
258 FUNCTION CF_TOTAL_VALUEFORMULA(CL_STE_CODE IN VARCHAR2
259 ,PRICE_NEGOTIATED IN NUMBER) RETURN NUMBER IS
260 BEGIN
261 IF CL_STE_CODE = 'CANCELLED' THEN
262 RETURN (0);
263 ELSE
264 RETURN (PRICE_NEGOTIATED);
265 END IF;
266 END CF_TOTAL_VALUEFORMULA;
267
268 FUNCTION CF_CANCELLED_VALUEFORMULA(CL_STE_CODE IN VARCHAR2
269 ,PRICE_NEGOTIATED IN NUMBER) RETURN NUMBER IS
270 BEGIN
271 IF CL_STE_CODE = 'CANCELLED' THEN
272 RETURN (PRICE_NEGOTIATED);
273 ELSE
274 RETURN (0);
275 END IF;
276 END CF_CANCELLED_VALUEFORMULA;
277
278 FUNCTION CF_CANCELLED_PRICE_ROUNDFORMUL(CS_CANCELLED_PRICE IN NUMBER
279 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
280 LN_CAN_VALUE NUMBER;
281 LV_CURRENCY_CODE VARCHAR2(400);
282 LN_CAN_VALUE_ROUND NUMBER;
283 BEGIN
284 LN_CAN_VALUE := CS_CANCELLED_PRICE;
285 LV_CURRENCY_CODE := CURRENCY_CODE;
286 LN_CAN_VALUE_ROUND := OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_CAN_VALUE
287 ,LV_CURRENCY_CODE);
288 RETURN (LN_CAN_VALUE_ROUND);
289 END CF_CANCELLED_PRICE_ROUNDFORMUL;
290
291 FUNCTION CP_PROD_NAME_P RETURN VARCHAR2 IS
292 BEGIN
293 --select s1.nextval into seq_val from dual;
294 --autonomous_proc('val2a', to_char(sysdate, 'dd-mon-yy hh:mi:ss')|| ' @ '|| seq_val, 'Raj', 'Raj');
295 --select s1.nextval into seq_val from dual;
296 --autonomous_proc('val2b', CP_PROD_NAME|| ' @ '|| seq_val, 'Raj', 'Raj');
297 RETURN CP_PROD_NAME;
298 END CP_PROD_NAME_P;
299
300 FUNCTION CP_CSI_P RETURN VARCHAR2 IS
301 BEGIN
302 RETURN CP_CSI;
303 END CP_CSI_P;
304
305 FUNCTION CP_PROD_SERIAL_NO_P RETURN VARCHAR2 IS
306 BEGIN
307 RETURN CP_PROD_SERIAL_NO;
308 END CP_PROD_SERIAL_NO_P;
309
310 FUNCTION CP_PROD_REF_NO_P RETURN VARCHAR2 IS
311 BEGIN
312 --select s1.nextval into seq_val from dual;
313 --autonomous_proc('val3a', to_char(sysdate, 'dd-mon-yy hh:mi:ss')|| ' @ '|| seq_val, 'Raj', 'Raj');
314 --select s1.nextval into seq_val from dual;
315 --autonomous_proc('val3b', CP_PROD_REF_NO|| ' @ '|| seq_val, 'Raj', 'Raj');
316 RETURN CP_PROD_REF_NO;
317 END CP_PROD_REF_NO_P;
318
319 FUNCTION CP_PROD_QTY_P RETURN NUMBER IS
320 BEGIN
321 RETURN CP_PROD_QTY;
322 END CP_PROD_QTY_P;
323
324 FUNCTION CP_NUM_OF_USERS_P RETURN VARCHAR2 IS
325 BEGIN
326 RETURN CP_NUM_OF_USERS;
327 END CP_NUM_OF_USERS_P;
328
329 FUNCTION CP_PRICING_LIC_LVL_P RETURN VARCHAR2 IS
330 BEGIN
331 RETURN CP_PRICING_LIC_LVL;
332 END CP_PRICING_LIC_LVL_P;
333
334 FUNCTION CP_LIC_LVL_P RETURN VARCHAR2 IS
335 BEGIN
336 RETURN CP_LIC_LVL;
337 END CP_LIC_LVL_P;
338
339 END OKS_OKSCODET_XMLP_PKG;
340
[Home] [Help]