1 PACKAGE BODY OKS_OKSSCOFM_XMLP_PKG AS
2 /* $Header: OKSSCOFMB.pls 120.2 2008/02/21 06:12:33 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 P_FORECAST_ST_PERIOD_V := to_char(P_FORECAST_ST_PERIOD,'DD-MON-YY');
12 P_FORECAST_END_PERIOD_V := TO_CHAR(P_FORECAST_END_PERIOD,'DD-MON-YY');
13 RETURN (TRUE);
14 END BEFOREREPORT;
15 FUNCTION CF_FORECAST_REVFORMULA(CF_CONTRACT_VALUE IN NUMBER
16 ,FORECAST_PER IN NUMBER
17 ,CF_REV_REC IN NUMBER) RETURN NUMBER IS
18 RET_VALUE1 NUMBER;
19 RET_VALUE2 NUMBER;
20 BEGIN
21 IF (CF_CONTRACT_VALUE = 0) THEN
22 RETURN (0);
23 ELSE
24 RET_VALUE1 := ((FORECAST_PER / 100) * CF_REV_REC);
25 RET_VALUE2 := RET_VALUE1;
26 RETURN (NVL(RET_VALUE2
27 ,0));
28 END IF;
29 END CF_FORECAST_REVFORMULA;
30 FUNCTION CF_SALESREP_NAMEFORMULA(SR_SALESREP_ID IN VARCHAR2
31 ,ORG_ID IN NUMBER) RETURN CHAR IS
32 P_SALESREP_NAME JTF_RS_SALESREPS.NAME%TYPE;
33 BEGIN
34 SELECT
35 NAME
36 INTO P_SALESREP_NAME
37 FROM
38 JTF_RS_SALESREPS
39 WHERE SALESREP_ID = SR_SALESREP_ID
40 AND ORG_ID = CF_SALESREP_NAMEFORMULA.ORG_ID
41 AND ROWNUM < 2;
42 RETURN (P_SALESREP_NAME);
43 END CF_SALESREP_NAMEFORMULA;
44 FUNCTION CF_PARTY_NAMEFORMULA(HZ_PARTY_ID IN VARCHAR2) RETURN CHAR IS
45 P_PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE;
46 BEGIN
47 SELECT
48 PARTY_NAME
49 INTO P_PARTY_NAME
50 FROM
51 HZ_PARTIES
52 WHERE PARTY_ID = HZ_PARTY_ID
53 AND ROWNUM = 1;
54 RETURN (P_PARTY_NAME);
55 END CF_PARTY_NAMEFORMULA;
56 FUNCTION CF_CONTRACT_VALUEFORMULA(CONTRACT_ID IN NUMBER) RETURN NUMBER IS
57 CURSOR L_CONTRACT_CSR IS
58 SELECT
59 NVL(SUM(PRICE_NEGOTIATED)
60 ,0)
61 FROM
62 OKC_K_LINES_B
63 WHERE DNZ_CHR_ID = CONTRACT_ID
64 AND LSE_ID in ( 25 , 7 , 9 , 10 , 8 , 35 , 11 )
65 AND DATE_CANCELLED is NULL
66 AND PRICE_NEGOTIATED between 0
67 AND 90999999
68 HAVING NVL(SUM(PRICE_NEGOTIATED)
69 ,0) >= NVL(P_MINIMUM_CONTRACT_VALUE
70 ,NVL(SUM(PRICE_NEGOTIATED)
71 ,0))
72 AND NVL(SUM(PRICE_NEGOTIATED)
73 ,0) <= NVL(P_MAXIMUM_CONTRACT_VALUE
74 ,NVL(SUM(PRICE_NEGOTIATED)
75 ,0));
76 L_AMT NUMBER := 0;
77 BEGIN
78 OPEN L_CONTRACT_CSR;
79 FETCH L_CONTRACT_CSR
80 INTO L_AMT;
81 CLOSE L_CONTRACT_CSR;
82 RETURN NVL(L_AMT
83 ,0);
84 EXCEPTION
85 WHEN OTHERS THEN
86 RETURN 0;
87 END CF_CONTRACT_VALUEFORMULA;
88 FUNCTION CF_CONTRACT_FORECASTFORMULA(CF_CONTRACT_VALUE IN NUMBER
89 ,FORECAST_PER IN NUMBER) RETURN NUMBER IS
90 BEGIN
91 IF (CF_CONTRACT_VALUE = 0) THEN
92 RETURN (0);
93 ELSE
94 RETURN (NVL((CF_CONTRACT_VALUE * FORECAST_PER / 100)
95 ,0));
96 END IF;
97 END CF_CONTRACT_FORECASTFORMULA;
98 FUNCTION CF_REV_RECFORMULA(CONTRACT_ID IN NUMBER
99 ,CF_CONTRACT_VALUE IN NUMBER) RETURN NUMBER IS
100 CURSOR L_FORECAST_CSR IS
101 SELECT
102 NVL(SUM((KL.PRICE_NEGOTIATED / CEIL(DECODE(SIGN(END_DATE - START_DATE)
103 ,0
104 ,1
105 ,(MONTHS_BETWEEN(KL.END_DATE
106 ,KL.START_DATE))))) * CEIL(DECODE(SIGN(P_REGZ_DATE - KL.START_DATE)
107 ,0
108 ,1
109 ,MONTHS_BETWEEN(P_REGZ_DATE
110 ,KL.START_DATE))))
111 ,0)
112 FROM
113 OKC_K_LINES_B KL
114 WHERE KL.DNZ_CHR_ID = CONTRACT_ID
115 AND KL.LSE_ID in ( 25 , 7 , 9 , 10 , 8 , 35 , 11 )
116 AND KL.PRICE_NEGOTIATED between 0
117 AND 90999999
118 AND KL.START_DATE <= P_REGZ_DATE
119 HAVING NVL(SUM(PRICE_NEGOTIATED)
120 ,0) >= NVL(P_MINIMUM_CONTRACT_VALUE
121 ,NVL(SUM(PRICE_NEGOTIATED)
122 ,0))
123 AND NVL(SUM(PRICE_NEGOTIATED)
124 ,0) <= NVL(P_MAXIMUM_CONTRACT_VALUE
125 ,NVL(SUM(PRICE_NEGOTIATED)
126 ,0));
127 L_AMT NUMBER := 0;
128 BEGIN
129 IF (CF_CONTRACT_VALUE = 0) THEN
130 RETURN (0);
131 ELSE
132 OPEN L_FORECAST_CSR;
133 FETCH L_FORECAST_CSR
134 INTO L_AMT;
135 CLOSE L_FORECAST_CSR;
136 RETURN NVL(L_AMT
137 ,0);
138 END IF;
139 EXCEPTION
140 WHEN OTHERS THEN
141 RETURN 0;
142 END CF_REV_RECFORMULA;
143 FUNCTION AFTERPFORM RETURN BOOLEAN IS
144 CURSOR C1(CN_SALESREPID IN NUMBER,CN_ORGID IN NUMBER) IS
145 SELECT
146 NAME
147 FROM
148 JTF_RS_SALESREPS
149 WHERE SALESREP_ID = CN_SALESREPID;
150 CURSOR C2(CN_ORG_ID IN NUMBER) IS
151 SELECT
152 NAME
153 FROM
154 HR_OPERATING_UNITS
155 WHERE ORGANIZATION_ID = CN_ORG_ID;
156 CURSOR C3(CN_CONTRACT_GROUP_ID IN NUMBER) IS
157 SELECT
158 NAME
159 FROM
160 OKC_K_GROUPS_V
161 WHERE ID = CN_CONTRACT_GROUP_ID;
162 BEGIN
163 BEGIN
164 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
165 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
166 EXCEPTION
167 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
168 /*SRW.MESSAGE(1
169 ,'srw_init')*/NULL;
170 END;
171 P_WHERE_CLAUSE := ' ';
172 IF (P_OPERATING_UNIT IS NOT NULL) THEN
173 P_ORG_NAME := NULL;
174 OPEN C2(P_OPERATING_UNIT);
175 FETCH C2
176 INTO P_ORG_NAME;
177 CLOSE C2;
178 END IF;
179 IF (P_SALESREP IS NOT NULL) THEN
180 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and ct.object1_id1 = :p_salesrep';
181 P_SALESREP_NAME := NULL;
182 OPEN C1(P_SALESREP,P_OPERATING_UNIT);
183 FETCH C1
184 INTO P_SALESREP_NAME;
185 CLOSE C1;
186 END IF;
187 IF (P_CURRENCY_CODE IS NOT NULL) THEN
188 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and kh.currency_code = :p_currency_code';
189 END IF;
190 IF (P_FORECAST_ST_PERIOD IS NOT NULL) THEN
191 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and KR.EST_REV_DATE >= :P_ForeCast_St_Period ';
192 END IF;
193 IF (P_FORECAST_END_PERIOD IS NOT NULL) THEN
194 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and KR.EST_REV_DATE <= :P_ForeCast_End_Period ';
195 END IF;
196 IF P_STATUS_CODE IS NOT NULL THEN
197 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and kh.sts_code = :p_status_code ';
198 END IF;
199 IF P_CONTRACT_GROUP IS NOT NULL THEN
200 P_CONTRACT_GROUP_WHERE := ' and kh.id in ( select INCLUDED_CHR_ID from okc_k_grpings
201 start with CGP_PARENT_ID = :p_contract_group
202 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) ';
203 P_CONTRACT_GROUP_NAME := NULL;
204 OPEN C3(P_CONTRACT_GROUP);
205 FETCH C3
206 INTO P_CONTRACT_GROUP_NAME;
207 CLOSE C3;
208 END IF;
209 RETURN (TRUE);
210 END AFTERPFORM;
211 FUNCTION AFTERREPORT RETURN BOOLEAN IS
212 BEGIN
213 BEGIN
214 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
215 EXCEPTION
216 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
217 /*SRW.MESSAGE(1
218 ,'srw_exit')*/NULL;
219 END;
220 RETURN (TRUE);
221 END AFTERREPORT;
222 END OKS_OKSSCOFM_XMLP_PKG;
223
[Home] [Help]