[Home] [Help]
PACKAGE BODY: APPS.ASF_OSI_DENORM_REF
Source
1 PACKAGE BODY ASF_OSI_DENORM_REF as
2 /* $Header: custom_asxosidb.pls 115.5.1157.2 2002/02/21 09:11:43 pkm ship $ */
3
4 --
5 -- HISTORY
6 --
7 -- CHSIN 28-FEB-2000 CREATED
8
9
10 PROCEDURE Refresh_OSI_Denorm(ERRBUF OUT varchar2,
11 RETCODE OUT varchar2) IS
12
13 CURSOR c_osi_denorm IS
14 SELECT
15 contractLeads.last_update_date last_update_date,
16 contractLeads.last_updated_by last_updated_by,
17 contractLeads.creation_date creation_date,
18 contractLeads.created_by created_by,
19 nvl(contractLeads.last_update_login,1) last_update_login,
20 contractLeads.osi_lead_id osi_lead_id,
21 contractLeads.lead_id lead_id,
22 contractNames.contr_name contract_name,
23 contractLeads.cname_id contract_name_id,
24 contractVehicles.vehicle contract_vehicle,
25 contractLeads.cvehicle contract_vhcl_id,
26 contractLeads.contr_type contract_type,
27 contractLeads.bom_person_id bom_person_id,
28 contractLeads.legal_person_id legal_person_id,
29 contractLeads.senior_contr_person_id senior_contr_person_id,
30 contractLeads.contr_spec_person_id cont_spec_person_id,
31 contractLeads.CONTR_DRAFTING_REQ CONTR_DRAFTING_REQ,
32 contractLeads.PRIORITY PRIORITY,
33 contractLeads.HIGHEST_APVL HIGHEST_APVL,
34 contractLeads.CURRENT_APVL_STATUS CURRENT_APVL_STATUS,
35 contractLeads.SUPPORT_APVL SUPPORT_APVL,
36 contractLeads.INTERNATIONAL_APVL INTERNATIONAL_APVL,
37 contractLeads.CREDIT_APVL CREDIT_APVL,
38 contractLeads.FIN_ESCROW_REQ FIN_ESCROW_REQ,
39 contractLeads.FIN_ESCROW_STATUS FIN_ESCROW_STATUS,
40 contractLeads.CSI_ROLLIN CSI_ROLLIN,
41 contractLeads.LICENCE_CREDIT_VER LICENCE_CREDIT_VER,
42 contractLeads.SUPPORT_CREDIT_VER SUPPORT_CREDIT_VER,
43 contractLeads.MD_DEAL_SUMMARY MD_DEAL_SUMMARY,
44 contractLeads.PROD_AVAIL_VER PROD_AVAIL_VER,
45 contractLeads.SHIP_LOCATION SHIP_LOCATION,
46 contractLeads.TAX_EXEMPT_CERT TAX_EXEMPT_CERT,
47 contractLeads.NL_REV_ALLOC_REQ NL_REV_ALLOC_REQ,
48 contractLeads.SENIOR_CONTR_NOTES SENIOR_CONTR_NOTES,
49 contractLeads.LEGAL_NOTES LEGAL_NOTES,
50 contractLeads.BOM_NOTES BOM_NOTES,
51 contractLeads.CONTR_NOTES CONTR_NOTES,
52 contractLeads.PO_FROM PO_FROM,
53 nvl(poFrom.lkp_value,contractLeads.PO_FROM) PO_FROM_DISP,
54 nvl(contrType.lkp_value,contractLeads.CONTR_TYPE) CONTRACT_TYPE_DISP,
55 contractLeads.CONSULTING_CC CONSULTING_CC,
56 nvl(consCcs.center_name,contractLeads.CONSULTING_CC) CONSULTING_CC_DISP,
57 contractLeads.CONTR_STATUS CONTR_STATUS,
58 contractLeads.RTS_ID RTS_ID,
59 contractLeads.EXTRA_DOCS EXTRA_DOCS
60 FROM
61 AS_OSI_CONTR_NAMES_BASE contractNames,
62 AS_OSI_CONTR_VHCL_BASE contractVehicles,
63 AS_OSI_LEADS_ALL contractLeads
64 ,AS_OSI_LOOKUP poFrom
65 ,AS_OSI_LOOKUP contrType
66 ,as_osi_cons_ccs_base consCcs
67 WHERE contractNames.CNAME_ID(+)= contractLeads.CNAME_ID
68 AND contractVehicles.CVEHICLE(+) = contractLeads.CVEHICLE
69 AND poFrom.lkp_type(+)='PO_FROM'
70 AND poFrom.lkp_code(+)=contractLeads.PO_FROM
71 AND contrType.lkp_type(+)='CONTR_TYPE'
72 AND contrType.lkp_code(+)=contractLeads.CONTR_TYPE
73 AND consCcs.cc(+)=contractLeads.CONSULTING_CC
74 ;
75
76 curr_rec c_osi_denorm%ROWTYPE;
77
78 cursor c_osi_ovl_denorm (p_osi_lead_id in number) is
79 SELECT
80 contractDetails.ovm_code ovm_code,
81 contractOverlay.ovm_value ovm_value
82 FROM
83 AS_OSI_OVERLAY_BASE contractOverlay,
84 AS_OSI_LEAD_OVL_ALL contractDetails
85 WHERE contractDetails.OSI_LEAD_ID = p_osi_lead_id
86 AND contractOverlay.OVM_CODE(+) = contractDetails.OVM_CODE
87 order by 1;
88
89
90 TYPE rOvmRecType is record (ovm_code varchar2(30),ovm_value varchar2(30));
91 TYPE tOvmRecTabType is TABLE OF rOvmRecType INDEX BY BINARY_INTEGER;
92 tOvmTab tOvmRecTabType;
93 vOvmCount integer;
94 vCombinedOvmCode varchar2(2000);
95 vCombinedOvmValue varchar2(2000);
96 vOvmDelimiter varchar2(1);
97 v_CursorID NUMBER;
98 v_Stmt VARCHAR2(500);
99 v_Dummy INTEGER;
100
101
102 BEGIN
103
104 -- LOCK TABLE as_sales_credits_denorm in EXCLUSIVE mode;
105 RETCODE := '0';
106
107 v_CursorID := DBMS_SQL.OPEN_CURSOR;
108 v_Stmt := 'TRUNCATE TABLE OSM.AS_OSI_LEADS_DENORM reuse storage';
109 dbms_sql.parse(v_CursorID,v_Stmt ,dbms_sql.native);
110 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
111 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
112
113
114 OPEN c_osi_denorm;
115 LOOP
116
117 FETCH c_osi_denorm INTO curr_rec;
118 EXIT when c_osi_denorm%NOTFOUND;
119 -- vCombinedOvmCode := null;
120 vCombinedOvmValue := null;
121 vOvmDelimiter := null;
122 vOvmCount := 0;
123 for ovl in c_osi_ovl_denorm(curr_rec.osi_lead_id) loop
124 -- vCombinedOvmCode := vCombinedOvmCode || vOvmDelimiter || ovl.ovm_code;
125 vCombinedOvmValue := vCombinedOvmValue || vOvmDelimiter || ovl.ovm_value;
126 vOvmDelimiter := '\';
127 vOvmCount := vOvmCount + 1;
128 tOvmTab(vOvmCount).ovm_code := ovl.ovm_code;
129 tOvmTab(vOvmCount).ovm_value := ovl.ovm_value;
130 end loop;
131 if vOvmCount = 0 then
132 vOvmCount := 1;
133 tOvmTab(vOvmCount).ovm_code := null;
134 tOvmTab(vOvmCount).ovm_value := null;
135 end if;
136 BEGIN
137 for i in 1..vOvmCount loop
138 INSERT INTO AS_OSI_LEADS_DENORM
139 (last_update_date,
140 last_updated_by,
141 creation_date,
142 created_by,
143 last_update_login
144 ,OSI_LEAD_ID
145 ,LEAD_ID
146 ,CONTRACT_NAME
147 ,CONTRACT_NAME_ID
148 ,CONTRACT_VEHICLE
149 ,CONTRACT_VHCL_ID
150 ,CONTRACT_TYPE
151 ,OVM_CODE
152 ,OVM_VALUE
153 ,combined_OVM_VALUE
154 ,BOM_PERSON_ID
155 ,LEGAL_PERSON_ID
156 ,SENIOR_CONTR_PERSON_ID
157 ,CONT_SPEC_PERSON_ID
158 ,CONTR_DRAFTING_REQ
159 ,PRIORITY
160 ,HIGHEST_APVL
161 ,CURRENT_APVL_STATUS
162 ,SUPPORT_APVL
163 ,INTERNATIONAL_APVL
164 ,CREDIT_APVL
165 ,FIN_ESCROW_REQ
166 ,FIN_ESCROW_STATUS
167 ,CSI_ROLLIN
168 ,LICENCE_CREDIT_VER
169 ,SUPPORT_CREDIT_VER
170 ,MD_DEAL_SUMMARY
171 ,PROD_AVAIL_VER
172 ,SHIP_LOCATION
173 ,TAX_EXEMPT_CERT
174 ,NL_REV_ALLOC_REQ
175 ,SENIOR_CONTR_NOTES
176 ,LEGAL_NOTES
177 ,BOM_NOTES
178 ,CONTR_NOTES
179 ,PO_FROM
180 ,PO_FROM_DISP
181 ,CONTRACT_TYPE_DISP
182 ,CONSULTING_CC
183 ,CONSULTING_CC_DISP
184 ,CONTR_STATUS
185 ,RTS_ID
186 ,EXTRA_DOCS
187 )
188 VALUES
189 (curr_rec.last_update_date,
190 curr_rec.last_updated_by,
191 curr_rec.creation_date,
192 curr_rec.created_by,
193 curr_rec.last_update_login,
194 curr_rec.osi_lead_id,
195 curr_rec.lead_id,
196 curr_rec.contract_name,
197 curr_rec.contract_name_id,
198 curr_rec.contract_vehicle,
199 curr_rec.contract_vhcl_id,
200 curr_rec.contract_type,
201 tOvmTab(i).ovm_code,
202 tOvmTab(i).ovm_value,
203 vCombinedOvmValue,
204 curr_rec.bom_person_id,
205 curr_rec.legal_person_id,
206 curr_rec.senior_contr_person_id,
207 curr_rec.cont_spec_person_id
208 ,curr_rec.CONTR_DRAFTING_REQ
209 ,curr_rec.PRIORITY
210 ,curr_rec.HIGHEST_APVL
211 ,curr_rec.CURRENT_APVL_STATUS
212 ,curr_rec.SUPPORT_APVL
213 ,curr_rec.INTERNATIONAL_APVL
214 ,curr_rec.CREDIT_APVL
215 ,curr_rec.FIN_ESCROW_REQ
216 ,curr_rec.FIN_ESCROW_STATUS
217 ,curr_rec.CSI_ROLLIN
218 ,curr_rec.LICENCE_CREDIT_VER
219 ,curr_rec.SUPPORT_CREDIT_VER
220 ,curr_rec.MD_DEAL_SUMMARY
221 ,curr_rec.PROD_AVAIL_VER
222 ,curr_rec.SHIP_LOCATION
223 ,curr_rec.TAX_EXEMPT_CERT
224 ,curr_rec.NL_REV_ALLOC_REQ
225 ,curr_rec.SENIOR_CONTR_NOTES
226 ,curr_rec.LEGAL_NOTES
227 ,curr_rec.BOM_NOTES
228 ,curr_rec.CONTR_NOTES
229 ,curr_rec.PO_FROM
230 ,curr_rec.PO_FROM_DISP
231 ,curr_rec.CONTRACT_TYPE_DISP
232 ,curr_rec.CONSULTING_CC
233 ,curr_rec.CONSULTING_CC_DISP
234 ,curr_rec.CONTR_STATUS
235 ,curr_rec.RTS_ID
236 ,curr_rec.EXTRA_DOCS
237 );
238
239 end loop;
240 EXCEPTION
241 WHEN OTHERS THEN
242 ERRBUF := sqlerrm;
243 RETCODE := '1';
244 --dbms_output.put_line(SQLERRM);
245 END;
246 END LOOP;
247 COMMIT;
248 CLOSE c_osi_denorm;
249 EXCEPTION
250 WHEN OTHERS THEN
251 ERRBUF := 'Error to refresh amount:'||to_char(sqlcode);
252 --dbms_output.put_line(ERRBUF);
253 --dbms_output.put_line(SQLERRM);
254 RETCODE := '2';
255
256 END Refresh_OSI_Denorm;
257
258
259
260 END ASF_OSI_DENORM_REF;