[Home] [Help]
57:
58: PROCEDURE Enable_Triggers IS
59: BEGIN
60: --Enable sales lead line trigger..
61: execute immediate('alter trigger AS_SALES_LEAD_LINES_BIUD enable');
62: END;
63:
64: PROCEDURE Disable_Triggers IS
65: BEGIN
63:
64: PROCEDURE Disable_Triggers IS
65: BEGIN
66: --Disable sales lead line trigger..
67: execute immediate('alter trigger AS_SALES_LEAD_LINES_BIUD disable');
68: END;
69:
70: PROCEDURE Create_temp_index(p_table IN VARCHAR2,
71: p_index_columns IN VARCHAR2) IS
76: l_user varchar2(2000);
77: l_index_name varchar2(100);
78:
79: begin
80: -- Temp index is created on as_sales_lead_lines table.
81: l_user := USER;
82:
83: -- Name for temporary index created for migration
84: l_index_name := p_table || G_INDEX_SUFFIX;
150: AND s.product_category_id IS NULL;
151:
152: CURSOR c_lead_line_values IS
153: SELECT distinct to_char(interest.interest_type_id) code ,interest.description meaning
154: FROM as_sales_lead_lines line, as_interest_types_vl interest
155: WHERE line.interest_type_id = interest.interest_type_id
156: AND line.primary_interest_code_id is null
157: AND line.secondary_interest_code_id is null
158: AND interest.product_category_id is null
158: AND interest.product_category_id is null
159: UNION
160: SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id code,
161: interest.description||'/'||pic.description meaning
162: FROM as_sales_lead_lines line, as_interest_codes_vl pic, as_interest_types_vl interest
163: WHERE line.primary_interest_code_id = pic.interest_code_id
164: AND pic.interest_type_id = interest.interest_type_id
165: AND pic.parent_interest_code_id is null
166: AND line.secondary_interest_code_id is null
167: AND pic.product_category_id is null
168: UNION
169: SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id||'/'||sic.interest_code_id code,
170: interest.description||'/'||pic.description||'/'||sic.description meaning
171: FROM as_sales_lead_lines line, as_interest_codes_vl sic, as_interest_codes_vl pic, as_interest_types_vl interest
172: WHERE line.secondary_interest_code_id = sic.interest_code_id
173: AND line.primary_interest_code_id = sic.parent_interest_code_id
174: AND sic.interest_type_id = interest.interest_type_id
175: and sic.parent_interest_code_id = pic.interest_code_id
176: and pic.product_category_id is null;
177:
178: CURSOR c_sales_lead_line_int IS
179: SELECT to_char(lead.interest_type_id) code
180: FROM as_sales_lead_lines lead
181: WHERE lead.interest_type_id not in (SELECT int.interest_type_id
182: FROM as_interest_types_b int)
183: UNION
184: SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id code
181: WHERE lead.interest_type_id not in (SELECT int.interest_type_id
182: FROM as_interest_types_b int)
183: UNION
184: SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id code
185: FROM as_sales_lead_lines lead
186: WHERE lead.primary_interest_code_id not in (SELECT pic.interest_code_id
187: FROM as_interest_codes_b pic
188: WHERE pic.parent_interest_code_id IS null)
189: UNION
187: FROM as_interest_codes_b pic
188: WHERE pic.parent_interest_code_id IS null)
189: UNION
190: SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id||'/'||lead.secondary_interest_code_id code
191: FROM as_sales_lead_lines lead
192: WHERE lead.secondary_interest_code_id not in (SELECT sic.interest_code_id
193: FROM as_interest_codes_b sic
194: WHERE sic.parent_interest_code_id is not null) ;
195:
221:
222: END IF;
223:
224: -- -----------------------------------------------------------------------
225: -- Check if all the data in as_sales_lead_lines have proper mapping.
226: -- -----------------------------------------------------------------------
227: l_no_data_found := TRUE;
228:
229: AML_DEBUG('-');
228:
229: AML_DEBUG('-');
230: AML_DEBUG('-');
231: AML_DEBUG('==============================================================');
232: AML_DEBUG('Checking data mapping in as_sales_lead_lines table...');
233: AML_DEBUG('==============================================================');
234: AML_DEBUG('-');
235: AML_DEBUG('-');
236: AML_DEBUG('(2).');
233: AML_DEBUG('==============================================================');
234: AML_DEBUG('-');
235: AML_DEBUG('-');
236: AML_DEBUG('(2).');
237: AML_DEBUG('The following data in as_sales_lead_lines do not have a mapping');
238: AML_DEBUG('to Single Product Hierarchy.');
239: AML_DEBUG('-');
240: AML_DEBUG(' Code Meaning');
241: AML_DEBUG('-------------------------------------------------------------------------------------');
254: END IF;
255:
256:
257: AML_DEBUG('==============================================================');
258: AML_DEBUG('Checking Stale/Invalid Interest Typed/Codes in as_sales_lead_lines table...');
259: AML_DEBUG('==============================================================');
260: AML_DEBUG('-');
261: AML_DEBUG('-');
262: AML_DEBUG('(3).');
294: l_count NUMBER := 0;
295:
296: CURSOR get_min_id IS
297: SELECT min(sales_lead_line_id)
298: FROM as_sales_lead_lines;
299:
300: CURSOR get_max_id IS
301: SELECT max(sales_lead_line_id)
302: FROM as_sales_lead_lines;
298: FROM as_sales_lead_lines;
299:
300: CURSOR get_max_id IS
301: SELECT max(sales_lead_line_id)
302: FROM as_sales_lead_lines;
303:
304: CURSOR get_next_val IS
305: SELECT as_sales_lead_lines_s.nextval
306: FROM dual;
301: SELECT max(sales_lead_line_id)
302: FROM as_sales_lead_lines;
303:
304: CURSOR get_next_val IS
305: SELECT as_sales_lead_lines_s.nextval
306: FROM dual;
307:
308: CURSOR Get_Disabled_Triggers(c_schema_name VARCHAR2) IS
309: SELECT trigger_name
308: CURSOR Get_Disabled_Triggers(c_schema_name VARCHAR2) IS
309: SELECT trigger_name
310: FROM all_triggers
311: WHERE table_owner = c_schema_name
312: AND trigger_name = 'AS_SALES_LEAD_LINES_BIUD'
313: AND nvl(status,'DISABLED') = 'ENABLED';
314:
315:
316: BEGIN
336: Disable_Triggers;
337: END IF;
338:
339: --Create temp index..
340: Create_Temp_Index('AS_SALES_LEAD_LINES','SALES_LEAD_LINE_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID');
341:
342: --Get the min sales_lead_line id..
343: OPEN Get_Min_Id;
344: FETCH Get_Min_Id into l_min_id;
362:
363: WHILE (l_count <= l_max_id)
364: LOOP
365: -- Update interest type
366: update as_sales_lead_lines l
367: set (category_id, category_set_id) =
368: (select int.product_category_id, int.product_cat_set_id
369: from as_interest_types_b int
370: where l.interest_type_id = int.interest_type_id)
374: and l.primary_interest_code_id is null
375: and l.secondary_interest_code_id is null;
376:
377: -- Update primary interest code
378: update as_sales_lead_lines l
379: set (category_id, category_set_id) =
380: (select int.product_category_id, int.product_cat_set_id
381: from as_interest_codes_b int
382: where l.primary_interest_code_id = int.interest_code_id)
385: and l.primary_interest_code_id is not null
386: and l.secondary_interest_code_id is null;
387:
388: -- Update secondary interest code
389: update as_sales_lead_lines l
390: set (category_id, category_set_id) =
391: (select int.product_category_id, int.product_cat_set_id
392: from as_interest_codes_b int
393: where l.secondary_interest_code_id = int.interest_code_id)
402: END LOOP;
403: COMMIT;
404:
405: -- Drop temporary index
406: Drop_Temp_Index('AS_SALES_LEAD_LINES');
407:
408: -- Enable All the triggers
409: If l_sales_lead_lines_biud then
410: Enable_Triggers;
429: If l_sales_lead_lines_biud then
430: Enable_Triggers;
431: End if;
432:
433: Drop_Temp_Index('AS_SALES_LEAD_LINES');
434: --
435: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
436: end MIGRATE_LEAD_LINES;
437: