1 PACKAGE BODY OKS_OKSSUMCO_XMLP_PKG AS
2 /* $Header: OKSSUMCOB.pls 120.2 2008/01/04 14:37:20 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 END BEFOREREPORT;
7
8 FUNCTION CF_SALESREP_NAMEFORMULA(REP_NAME IN VARCHAR2
9 ,ORG_ID IN NUMBER) RETURN CHAR IS
10 CURSOR C1(CN_SALESREP_ID IN NUMBER,CN_ORG_ID IN NUMBER) IS
11 SELECT
12 NAME
13 FROM
14 JTF_RS_SALESREPS SREP
15 WHERE SALESREP_ID = CN_SALESREP_ID
16 AND ORG_ID = CN_ORG_ID;
17 LN_SALESREP_ID NUMBER;
18 LV_SALESREP_NAME VARCHAR2(500);
19 BEGIN
20 LN_SALESREP_ID := TO_NUMBER(REP_NAME);
21 OPEN C1(LN_SALESREP_ID,ORG_ID);
22 FETCH C1
23 INTO LV_SALESREP_NAME;
24 CLOSE C1;
25 RETURN (LV_SALESREP_NAME);
26 END CF_SALESREP_NAMEFORMULA;
27
28 FUNCTION AFTERPFORM RETURN BOOLEAN IS
29 X_REP_NAME VARCHAR2(800);
30 X_CUSTOMER_NAME VARCHAR2(800);
31 X_CUSTOMER_NUMBER VARCHAR2(800);
32 X_ORG_ID VARCHAR2(800);
33 X_CURRENCY_CODE VARCHAR2(800);
34 BEGIN
35 BEGIN
36 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
37 LP_END_TO_DATE := to_char(P_END_TO_DATE, 'DD-MM-YYYY');
38 LP_END_FROM_DATE := to_char(P_END_FROM_DATE, 'DD-MM-YYYY');
39 LP_START_TO_DATE := to_char(P_START_TO_DATE, 'DD-MM-YYYY');
40 LP_START_FROM_DATE := to_char(P_START_FROM_DATE, 'DD-MM-YYYY');
41 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
42 EXCEPTION
43 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
44 /*SRW.MESSAGE(1
45 ,'srw_init')*/NULL;
46 END;
47 P_VENDOR_CONT_ROLE := NVL(FND_PROFILE.VALUE('OKS_VENDOR_CONTACT_ROLE')
48 ,'SUP_SALES');
49 IF P_ORG_ID IS NULL AND FND_PROFILE.VALUE('OKC_VIEW_K_BY_ORG') = 'Y' THEN
50 P_ORG_ID := FND_PROFILE.VALUE('ORG_ID');
51 END IF;
52 IF P_ORG_ID IS NOT NULL THEN
53 X_ORG_ID := ' and KHD.Authoring_Org_Id = :p_org_id ';
54 P_ORG_ID_WHERE := X_ORG_ID;
55 END IF;
56 IF P_REP_NAME IS NOT NULL THEN
57 P_REP_ID_CHAR := TO_CHAR(P_REP_NAME);
58 X_REP_NAME := ' and KCTS.Object1_Id1 = :p_rep_id_char';
59 P_REP_NAME_WHERE := X_REP_NAME;
60 END IF;
61 IF P_CUSTOMER_NAME IS NOT NULL THEN
62 P_PARTY_ID_NAME_CHAR := TO_CHAR(P_CUSTOMER_NAME);
63 X_CUSTOMER_NAME := ' and KPRC.Object1_Id1 = :p_party_id_name_char';
64 P_CUSTOMER_NAME_WHERE := X_CUSTOMER_NAME;
65 END IF;
66 IF P_CUSTOMER_NUMBER IS NOT NULL THEN
67 P_PARTY_ID_NUM_CHAR := TO_CHAR(P_CUSTOMER_NUMBER);
68 X_CUSTOMER_NUMBER := ' and KPRC.Object1_Id1 = :p_party_id_num_char';
69 P_CUSTOMER_NUMBER_WHERE := X_CUSTOMER_NUMBER;
70 END IF;
71 IF P_STATUS_CODE IS NOT NULL THEN
72 P_STATUS_CODE_WHERE := ' and KHD.Sts_Code = :p_status_code ';
73 END IF;
74 IF P_STATUS_TYPE IS NOT NULL THEN
75 P_STATUS_TYPE_WHERE := ' and KSTB.STE_Code = :p_status_type ';
76 END IF;
77 IF P_CURRENCY_CODE IS NOT NULL THEN
78 X_CURRENCY_CODE := ' and KHD.Currency_Code = :p_currency_code';
79 P_CURRENCY_CODE_WHERE := X_CURRENCY_CODE;
80 END IF;
81 P_START_END_DATE_WHERE := ' ';
82 IF P_START_FROM_DATE IS NOT NULL THEN
83 P_START_END_DATE_WHERE := P_START_END_DATE_WHERE || ' and KHD.Start_Date >= to_date(:LP_START_FROM_DATE,''DD-MM-YYYY'') ';
84 END IF;
85 IF P_START_TO_DATE IS NOT NULL THEN
86 P_START_END_DATE_WHERE := P_START_END_DATE_WHERE || ' and KHD.Start_Date <= to_date(:lP_START_TO_DATE,''DD-MM-YYYY'') ';
87 END IF;
88 IF P_END_FROM_DATE IS NOT NULL THEN
89 P_START_END_DATE_WHERE := P_START_END_DATE_WHERE || ' and KHD.End_Date >= to_date(:lP_END_FROM_DATE,''DD-MM-YYYY'') ';
90 END IF;
91 IF P_END_TO_DATE IS NOT NULL THEN
92 P_START_END_DATE_WHERE := P_START_END_DATE_WHERE || ' and KHD.End_Date <= to_date(:lP_END_TO_DATE,''DD-MM-YYYY'') ';
93 END IF;
94 IF P_CONTRACT_GROUP IS NOT NULL THEN
95 P_CONTRACT_GROUP_FROM := ' , ( select INCLUDED_CHR_ID
96 from okc_k_grpings
97 start with INCLUDED_CHR_ID IN
98 ( select /*+ cardinality (b,1) */ id
99 from okc_k_headers_b b
100 where start_date between to_date(:lP_START_FROM_DATE,''DD-MM-YYYY'') and to_date(:lP_START_TO_DATE,''DD-MM-YYYY'')
101 and scs_code in ( ''SERVICE'' , ''WARRANTY'' ) )
102 and CGP_PARENT_ID = :p_contract_group
103 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) cgrp ';
104 P_CONTRACT_GROUP_WHERE := 'and KHD.ID = cgrp.included_chr_id ';
105 END IF;
106 RETURN (TRUE);
107 END AFTERPFORM;
108
109 FUNCTION CF_TYPEFORMULA(STATUS_TYPE_SUBSTR IN VARCHAR2) RETURN CHAR IS
110 BEGIN
111 RETURN (STATUS_TYPE_SUBSTR);
112 END CF_TYPEFORMULA;
113
114 FUNCTION CF_STATUSCODEFORMULA(STATUS_CODE_SUBSTR IN VARCHAR2) RETURN CHAR IS
115 BEGIN
116 RETURN (STATUS_CODE_SUBSTR);
117 END CF_STATUSCODEFORMULA;
118
119 FUNCTION CF_PRODLINEVALUEFORMULA(CONTRACT_ID IN NUMBER
120 ,STATUS_CODE IN VARCHAR2
121 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
122 CURSOR C1(CN_CONTRACT_ID IN NUMBER,CV_STS_CODE IN VARCHAR2) IS
123 SELECT
124 SUM(PRICE_NEGOTIATED)
125 FROM
126 OKC_K_LINES_B
127 WHERE DNZ_CHR_ID = CN_CONTRACT_ID
128 AND LSE_ID IN ( 7 , 8 , 9 , 10 , 11 , 35 , 13 , 18 , 25 )
129 AND STS_CODE = CV_STS_CODE;
130 LN_CONTRACT_ID NUMBER;
131 LV_STATUS_CODE VARCHAR2(200);
132 LV_CURRENCY_CODE VARCHAR2(200);
133 LN_PROD_VALUE NUMBER;
134 LN_PROD_VALUE_ROUND NUMBER;
135 BEGIN
136 LN_CONTRACT_ID := CONTRACT_ID;
137 LV_STATUS_CODE := LTRIM(RTRIM(STATUS_CODE));
138 LV_CURRENCY_CODE := LTRIM(RTRIM(CURRENCY_CODE));
139 OPEN C1(LN_CONTRACT_ID,LV_STATUS_CODE);
140 FETCH C1
141 INTO LN_PROD_VALUE;
142 CLOSE C1;
143 LN_PROD_VALUE_ROUND := ROUND(OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_PROD_VALUE
144 ,LV_CURRENCY_CODE)
145 ,0);
146 RETURN (LN_PROD_VALUE_ROUND);
147 END CF_PRODLINEVALUEFORMULA;
148
149 FUNCTION CF_RECCOUNTFORMULA RETURN NUMBER IS
150 BEGIN
151 RETURN (1);
152 END CF_RECCOUNTFORMULA;
153
154 FUNCTION CF_PRODLINE_VALUEPERDAYFORMULA(CONTRACT_ID IN NUMBER
155 ,STATUS_CODE IN VARCHAR2
156 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
157 CURSOR C1(CN_CONTRACT_ID IN NUMBER,CV_STS_CODE IN VARCHAR2) IS
158 SELECT
159 SUM(PRICE_NEGOTIATED / (END_DATE - START_DATE + 1))
160 FROM
161 OKC_K_LINES_B
162 WHERE DNZ_CHR_ID = CN_CONTRACT_ID
163 AND LSE_ID IN ( 7 , 8 , 9 , 10 , 11 , 35 , 13 , 18 , 25 )
164 AND STS_CODE = CV_STS_CODE;
165 LN_CONTRACT_ID NUMBER;
166 LV_STATUS_CODE VARCHAR2(200);
167 LV_CURRENCY_CODE VARCHAR2(200);
168 LN_PROD_VALUEPERDAY NUMBER;
169 LN_PROD_VALUEPERDAY1 NUMBER;
170 BEGIN
171 LN_CONTRACT_ID := CONTRACT_ID;
172 LV_STATUS_CODE := LTRIM(RTRIM(STATUS_CODE));
173 LV_CURRENCY_CODE := LTRIM(RTRIM(CURRENCY_CODE));
174 OPEN C1(LN_CONTRACT_ID,LV_STATUS_CODE);
175 FETCH C1
176 INTO LN_PROD_VALUEPERDAY;
177 CLOSE C1;
178 LN_PROD_VALUEPERDAY1 := ROUND(OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LN_PROD_VALUEPERDAY
179 ,LV_CURRENCY_CODE)
180 ,2);
181 RETURN (LN_PROD_VALUEPERDAY1);
182 END CF_PRODLINE_VALUEPERDAYFORMULA;
183
184 FUNCTION CF_PRODLINE_ANNRATEFORMULA(CF_PRODLINE_VALUEPERDAY IN NUMBER) RETURN NUMBER IS
185 BEGIN
186 RETURN (ROUND(CF_PRODLINE_VALUEPERDAY * 365
187 ,0));
188 END CF_PRODLINE_ANNRATEFORMULA;
189
190 FUNCTION AFTERREPORT RETURN BOOLEAN IS
191 BEGIN
192 BEGIN
193 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
194 EXCEPTION
195 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
196 /*SRW.MESSAGE(1
197 ,'srw_exit')*/NULL;
198 END;
199 RETURN (TRUE);
200 END AFTERREPORT;
201
202 END OKS_OKSSUMCO_XMLP_PKG;
203
[Home] [Help]