[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_FUNCTION
Source
1 PACKAGE BODY MSC_CL_FUNCTION AS -- body
2 /* $Header: MSCCLFNB.pls 120.1 2005/10/20 01:42:49 abhikuma noship $ */
3
4 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
5
6 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
7 IS
8 BEGIN
9 IF fnd_global.conc_request_id > 0 THEN
10 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
11 null;
12 ELSE
13 null;
14 --DBMS_OUTPUT.PUT_LINE( pBUFF);
15 END IF;
16 EXCEPTION
17 WHEN OTHERS THEN
18 RETURN;
19 END LOG_MESSAGE;
20
21 FUNCTION GET_ALL_ORGS ( p_org_group IN VARCHAR2,
22 p_instance_id IN NUMBER)
23 RETURN VARCHAR2 AS
24
25 TYPE mastcurtyp IS REF CURSOR;
26 c mastcurtyp;
27
28 lv_sql_stmt VARCHAR2(1000) := null;
29 lv_in_org_str VARCHAR2(1000) := null;
30 lc_ins_org VARCHAR2(10) :=null;
31 BEGIN
32
33 SELECT DECODE( M2A_DBLINK,
34 NULL, NULL_DBLINK,
35 '@'||M2A_DBLINK)
36 INTO v_dblink
37 FROM MSC_APPS_INSTANCES
38 WHERE INSTANCE_ID= p_instance_id;
39
40
41 lv_sql_stmt := 'select mp.organization_code org_code '
42 ||' from msc_instance_orgs ins , mtl_parameters'||v_dblink||' mp '
43 ||' where ins.organization_id=mp.organization_id '
44 ||' and ins.enabled_flag=1 '
45 ||' and ins.org_group = :p_org_group '
46 ||' and ins.sr_instance_id = :p_instance_id ';
47
48
49 OPEN c FOR lv_sql_stmt using p_org_group,p_instance_id;
50
51 LOOP
52
53 FETCH c into lc_ins_org;
54 EXIT WHEN c%NOTFOUND;
55
56 IF c%rowcount = 1 THEN
57 lv_in_org_str:= lc_ins_org;
58 ELSE
59 lv_in_org_str := lv_in_org_str||','||lc_ins_org;
60 END IF;
61
62 END LOOP;
63
64 CLOSE c;
65
66
67 RETURN lv_in_org_str ;
68
69 END;
70
71
72 PROCEDURE UPDATE_DATE_COLUMNS(ERRBUF OUT NOCOPY VARCHAR2,
73 RETCODE OUT NOCOPY NUMBER,
74 pINSTANCE_ID IN NUMBER,
75 pNUM_OF_DAYS IN NUMBER)
76 IS
77
78 lv_sql_tmp_stmt varchar2(4000);
79
80 Cursor c1(lv_msc_schema varchar2) is
81 SELECT flv.attribute2 table_name, atc.COLUMN_NAME column_name
82 FROM all_tab_columns atc , fnd_lookup_values flv
83 WHERE
84 flv.lookup_type='MSC_ODS_TABLE'
85 and flv.enabled_flag = 'Y' AND flv.view_application_id = 700
86 and flv.attribute11 is null and flv.attribute12 is null
87 and upper(atc.TABLE_NAME) = flv.attribute2
88 AND atc.OWNER = lv_msc_schema
89 AND atc.DATA_TYPE = 'DATE'
90 and flv.language=userenv('lang');
91
92 Cursor c2(lv_msc_schema varchar2) is
93 SELECT flv.attribute2 table_name, atc.COLUMN_NAME column_name
94 FROM all_tab_columns atc , fnd_lookup_values flv
95 WHERE
96 flv.lookup_type='MSC_ODS_TABLE'
97 and flv.enabled_flag = 'Y' AND flv.view_application_id = 700
98 and flv.attribute11='Y' and flv.attribute12 is null
99 and upper(atc.TABLE_NAME) = flv.attribute2
100 AND atc.OWNER = lv_msc_schema
101 AND atc.DATA_TYPE = 'DATE'
102 and flv.language=userenv('lang');
103
104
105 Cursor c3(lv_msc_schema varchar2) is
106 SELECT flv.attribute2 table_name, atc.COLUMN_NAME column_name
107 FROM all_tab_columns atc , fnd_lookup_values flv
108 WHERE
109 flv.lookup_type='MSC_ODS_TABLE'
110 and flv.enabled_flag = 'Y' AND flv.view_application_id = 700
111 and flv.attribute11='Y' and flv.attribute12='Y'
112 and upper(atc.TABLE_NAME) = flv.attribute2
113 AND atc.OWNER = lv_msc_schema
114 AND atc.DATA_TYPE = 'DATE'
115 and flv.language=userenv('lang');
116
117 lv_table_name varchar2(100);
118 lv_retval boolean;
119 lv_dummy1 varchar2(32);
120 lv_dummy2 varchar2(32);
121 lv_msc_schema varchar2(32);
122 lv_prod_short_name varchar2(32);
123
124 BEGIN
125 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(724);
126 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2,lv_msc_schema);
127 lv_sql_tmp_stmt := null;
128 lv_table_name := null ;
129 FOR c_rec IN c1(lv_msc_schema) LOOP
130 IF lv_table_name is null or lv_table_name <> c_rec.table_name then
131 IF lv_table_name is not null then
132 EXECUTE IMMEDIATE
133 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
134 ||' SET ' || lv_sql_tmp_stmt;
135 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
136 commit;
137 lv_sql_tmp_stmt := null;
138 end if ;
139 lv_table_name := c_rec.table_name;
140 END IF ;
141 IF lv_sql_tmp_stmt IS NULL THEN
142 lv_sql_tmp_stmt := ' ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
143 ELSE
144 lv_sql_tmp_stmt := lv_sql_tmp_stmt || ' , ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
145 END IF;
146 END LOOP;
147 EXECUTE IMMEDIATE
148 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
149 ||' SET ' || lv_sql_tmp_stmt;
150 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
151 commit;
152
153 lv_sql_tmp_stmt := null;
154 lv_table_name := null ;
155
156 FOR c_rec IN c2(lv_msc_schema) LOOP
157 IF lv_table_name is null or lv_table_name <> c_rec.table_name then
158 IF lv_table_name is not null then
159 EXECUTE IMMEDIATE
160 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
161 ||' SET ' || lv_sql_tmp_stmt
162 ||' WHERE SR_INSTANCE_ID = :pINSTANCE_ID '
163 USING pINSTANCE_ID;
164 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
165 commit;
166 lv_sql_tmp_stmt := null;
167 end if ;
168 lv_table_name := c_rec.table_name;
169 END IF ;
170
171 IF lv_sql_tmp_stmt IS NULL THEN
172 lv_sql_tmp_stmt := ' ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
173 ELSE
174 lv_sql_tmp_stmt := lv_sql_tmp_stmt || ' , ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
175 END IF;
176 END LOOP;
177 EXECUTE IMMEDIATE
178 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
179 ||' SET ' || lv_sql_tmp_stmt
180 ||' WHERE SR_INSTANCE_ID = :pINSTANCE_ID '
181 USING pINSTANCE_ID;
182 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
183 commit;
184
185
186 lv_sql_tmp_stmt := null;
187 lv_table_name := null ;
188
189 FOR c_rec IN c3(lv_msc_schema) LOOP
190 IF lv_table_name is null or lv_table_name <> c_rec.table_name then
191 IF lv_table_name is not null then
192 EXECUTE IMMEDIATE
193 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
194 ||' SET ' || lv_sql_tmp_stmt
195 ||' WHERE SR_INSTANCE_ID = :pINSTANCE_ID '
196 ||' AND PLAN_ID = -1 '
197 USING pINSTANCE_ID;
198 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
199 commit;
200 lv_sql_tmp_stmt := null;
201 end if ;
202 lv_table_name := c_rec.table_name;
203 END IF ;
204
205 IF lv_sql_tmp_stmt IS NULL THEN
206 lv_sql_tmp_stmt := ' ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
207 ELSE
208 lv_sql_tmp_stmt := lv_sql_tmp_stmt || ' , ' || c_rec.column_name || ' = '||c_rec.column_name || ' + ' || pNUM_OF_DAYS;
209 END IF;
210 END LOOP;
211 EXECUTE IMMEDIATE
212 ' UPDATE /*+ PARALLEL('|| lv_table_name || ') */ ' || lv_table_name
213 ||' SET ' || lv_sql_tmp_stmt
214 ||' WHERE SR_INSTANCE_ID = :pINSTANCE_ID '
215 ||' AND PLAN_ID = -1 '
216 USING pINSTANCE_ID;
217 LOG_MESSAGE('Table Updated : ' || lv_table_name || ' row updated : '|| SQL%ROWCOUNT );
218 commit;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 ROLLBACK;
223 LOG_MESSAGE('An error has occurred when updating the Date columns.');
224 LOG_MESSAGE(SQLERRM);
225 RETCODE := G_ERROR;
226
227 END UPDATE_DATE_COLUMNS;
228
229
230 END MSC_CL_FUNCTION;