1 PACKAGE BODY INV_ITEM_DATA_SCRIPTS AS
2 /* $Header: INVIDSEB.pls 120.2 2011/08/30 10:59:56 ccsingh noship $ */
3
4 G_ERROR CONSTANT NUMBER := 2;
5
6
7 /* Procedure to modify internal_ordered_flag, internal_order_enabled flag and shippable flag.
8 This datafix was originally given through bug 7572178 (datafix) and was necessiated
9 due to the functional change introduced through bug 5533216 */
10 PROCEDURE proc_io_shippable_flags(
11 errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY NUMBER)
13 IS
14
15 l_sql_stmt_num NUMBER;
16
17 BEGIN
18 FND_FILE.put_line (FND_FILE.log, 'Entered procedure proc_io_shippable_flags ');
19
20 l_sql_stmt_num := 1;
21 UPDATE MTL_SYSTEM_ITEMS_B
22 SET INTERNAL_ORDER_ENABLED_FLAG ='N'
23 WHERE INTERNAL_ORDER_ENABLED_FLAG ='Y'
24 AND INTERNAL_ORDER_FLAG ='N';
25
26 l_sql_stmt_num := 2;
27 UPDATE MTL_SYSTEM_ITEMS_B
28 SET INTERNAL_ORDER_ENABLED_FLAG ='N',
29 INTERNAL_ORDER_FLAG ='N'
30 WHERE INTERNAL_ORDER_FLAG ='Y'
31 AND NVL(CONTRACT_ITEM_TYPE_CODE, 'SUBSCRIPTION') IN
32 ('WARRANTY','SERVICE','USAGE');
33
34 l_sql_stmt_num := 3;
35 UPDATE MTL_SYSTEM_ITEMS_B
36 SET SHIPPABLE_ITEM_FLAG ='Y'
37 WHERE INTERNAL_ORDER_FLAG ='Y'
38 AND SHIPPABLE_ITEM_FLAG ='N'
39 AND NVL(CONTRACT_ITEM_TYPE_CODE, 'SUBSCRIPTION') NOT IN
40 ('WARRANTY','SERVICE','USAGE');
41
42 FND_FILE.put_line (FND_FILE.log, 'End of procedure proc_io_shippable_flags ');
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 FND_FILE.put_line (FND_FILE.log, 'Exception occured during proc_io_shippable_flags ');
47 FND_FILE.put_line (FND_FILE.log, 'while executing sql statement '||l_sql_stmt_num);
48 FND_FILE.put_line (FND_FILE.log, SQLERRM);
49 errbuf := SQLERRM;
50 retcode := G_ERROR;
51 END proc_io_shippable_flags;
52
53 -- added as part of bug 12927243
54 /* Procedure to call generic script that updates the mtl_item_categories table
55 to correct the following conditions
56 Note: This is valid only for Master Controlled Category Sets and is valid
57 for both single and multi assignment category sets.
58 Case 1: There are category assignments at the master and child
59 organizations, but the values are not the same.
60 Case 2: There are category assignments at the master and child organizations
61 that have the same values, but there are also an additional category
62 assigment record for the child organization.
63 Case 3: There are category assignments in the master organization for which
64 there is no corresponding child organization category assignment. */
65
66 PROCEDURE proc_cat_datafix(
67 errbuf OUT NOCOPY VARCHAR2,
68 retcode OUT NOCOPY NUMBER)
69 IS
70
71 /*Retrieves all Master Orgs*/
72 CURSOR master_org_cur IS
73 SELECT master_organization_id
74 FROM mtl_parameters
75 WHERE organization_id = master_organization_id;
76 /*Retrieves all Master Items that have Categories of Master controlled Category Sets associated*/
77 CURSOR items_list(master_org_id NUMBER) IS
78 SELECT inventory_item_id,
79 organization_id,
80 category_set_id,
81 category_id
82 FROM mtl_item_categories a
83 WHERE EXISTS (SELECT 'X'
84 FROM mtl_category_sets_b
85 WHERE control_level = 1
86 AND category_set_id = a.category_set_id)
87 AND a.organization_id = master_org_id;
88 /*Retrieves all the child Organizations for the the specific item and master org combination */
89 CURSOR child_orgs(item_id NUMBER,
90 master_org_id NUMBER) IS
91 SELECT msib.organization_id
92 FROM mtl_system_items_b msib,
93 mtl_parameters mp
94 WHERE msib.organization_id = mp.organization_id
95 AND msib.inventory_item_id = item_id
96 AND mp.master_organization_id = master_org_id
97 AND mp.organization_id <> mp.master_organization_id;
98
99 l_cat_set_exists VARCHAR2(8);
100 BEGIN
101
102 FND_FILE.put_line (FND_FILE.log, 'Start of procedure proc_cat_datafix ');
103
104 FOR rec_cur IN master_org_cur LOOP
105 /*Case 1 and 2: This will delete additional category records from the child organization
106 that are not present in the Master Organization*/
107 DELETE FROM mtl_item_categories a
108 WHERE a.organization_id IN (SELECT m.organization_id
109 FROM mtl_parameters m
110 WHERE m.master_organization_id = rec_cur.master_organization_id
111 AND m.organization_id <> m.master_organization_id)
112 AND EXISTS (SELECT 'X'
113 FROM mtl_category_sets_b
114 WHERE control_level = 1
115 AND category_set_id = a.category_set_id)
116 AND NOT EXISTS (SELECT 'x'
117 FROM mtl_item_categories b
118 WHERE b.category_set_id = a.category_set_id
119 AND b.inventory_item_id = a.inventory_item_id
120 AND b.category_id = a.category_id
121 AND b.organization_id = rec_cur.master_organization_id);
122
123 /*Case 3: This will insert categories that are not present in the child org*/
124 FOR i IN items_list(rec_cur.master_organization_id) LOOP
125 /*Fetch the list of child orgs to which the item is assigned*/
126 FOR j IN child_orgs(i.inventory_item_id,i.organization_id) LOOP
127 /*Check whether the give item under a child org is assigned to
128 the same category set and category as that of the master org*/
129 BEGIN
130 SELECT 'FOUND'
131 INTO l_cat_set_exists
132 FROM mtl_item_categories
133 WHERE inventory_item_id = i.inventory_item_id
134 AND organization_id = j.organization_id
135 AND category_set_id = i.category_set_id
136 AND category_id = i.category_id;
137 EXCEPTION
138 WHEN no_data_found THEN
139 l_cat_set_exists := 'NOTFOUND';
140 END;
141
142 /*If not assigned at the child org level then insert details into the mtl_item_categories table*/
143 IF l_cat_set_exists = 'NOTFOUND' THEN
144 INSERT INTO mtl_item_categories
145 (inventory_item_id,
146 organization_id,
147 category_set_id,
148 category_id,
149 last_update_date,
150 last_updated_by,
151 creation_date,
152 created_by,
153 last_update_login,
154 request_id,
155 program_application_id,
156 program_id,
157 program_update_date,
158 wh_update_date)
159 SELECT inventory_item_id,
160 j.organization_id,
161 category_set_id,
162 category_id,
163 last_update_date,
164 last_updated_by,
165 creation_date,
166 created_by,
167 last_update_login,
168 request_id,
169 program_application_id,
170 program_id,
171 program_update_date,
172 wh_update_date
173 FROM mtl_item_categories
174 WHERE inventory_item_id = i.inventory_item_id
175 AND organization_id = i.organization_id
176 AND category_set_id = i.category_set_id
177 AND category_id = i.category_id;
178 END IF;
179 END LOOP;
180 END LOOP;
181 END LOOP;
182
183 FND_FILE.put_line (FND_FILE.log, 'End of procedure proc_cat_datafix ');
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 FND_FILE.put_line (FND_FILE.log, 'Exception occured during proc_cat_datafix ');
188 FND_FILE.put_line (FND_FILE.log, SQLERRM);
189 errbuf := SQLERRM;
190 retcode := G_ERROR;
191 END proc_cat_datafix;
192
193
194 /* Main procedure that gets called from the concurrent program
195 'Items Data Scripts Execution' (INVIDSEP)*/
196
197 PROCEDURE run_data_scripts(
198 errbuf OUT NOCOPY VARCHAR2,
199 retcode OUT NOCOPY NUMBER)
200 IS
201
202 BEGIN
203 FND_FILE.put_line (FND_FILE.log, 'Entered run_data_scripts of Items Data Scripts Execution Concurrent Program ');
204
205 /* call procedure proc_io_shippable_flags to correct the IO,IOE,shippable flags */
206 proc_io_shippable_flags(errbuf,retcode);
207
208 -- added as part of bug 12927243
209 /* call procedure proc_cat_datafix to correct Master-Child cases for categories */
210 proc_cat_datafix(errbuf,retcode);
211
212
213 FND_FILE.put_line (FND_FILE.log, 'End run_data_scripts of Items Data Scripts Execution Concurrent Program ');
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 FND_FILE.put_line (FND_FILE.log, 'Exception occured during Items Data Scripts Execution Concurrent Program ');
218 FND_FILE.put_line (FND_FILE.log, SQLERRM);
219 errbuf := SQLERRM;
220 retcode := G_ERROR;
221 END run_data_scripts;
222
223 END INV_ITEM_DATA_SCRIPTS;
[Home] [Help]