[Home] [Help]
PACKAGE BODY: APPS.MSC_IMPORT_FORECAST
Source
1 PACKAGE BODY MSC_IMPORT_FORECAST AS
2 /* $Header: MSCIFSTB.pls 120.1 2005/06/21 02:55:40 appldev ship $ */
3
4 PROCEDURE Import_Forecast(
5 ERRBUF OUT NOCOPY VARCHAR2,
6 RETCODE OUT NOCOPY NUMBER,
7 v_req_id IN NUMBER) -- not used for now
8 IS
9 l_user_id number := fnd_global.user_id;
10 BEGIN
11 -- Validate Seller Code
12 update msc_st_demands
13 set ATTRIBUTE11 = 'ERROR',
14 ATTRIBUTE15 = 'Invalid Seller Code'
15 where not exists (select 'exists'
16 from HZ_PARTIES HP,
17 MSC_ST_DEMANDS MSD
18 where HP.PARTY_NAME = MSD.ATTRIBUTE1
19 and HP.PARTY_TYPE = 'ORGANIZATION')
20 and ATTRIBUTE11 = 'NEW'
21 and ATTRIBUTE8 in ('FORECAST');
22
23 -- Validate Buyer Code
24 update msc_st_demands
25 set ATTRIBUTE11 = 'ERROR',
26 ATTRIBUTE15 = 'Invalid Buyer Code'
27 where not exists ( select 'exists'
28 from HZ_PARTIES HP,
29 MSC_ST_DEMANDS MSD
30 where HP.PARTY_NAME = MSD.ATTRIBUTE2
31 and HP.PARTY_TYPE = 'ORGANIZATION')
32 and ATTRIBUTE11 = 'NEW'
33 and ATTRIBUTE8 in ('FORECAST');
34
35 -- Validate Quantity
36 update msc_st_demands
37 set ATTRIBUTE11 = 'ERROR',
38 ATTRIBUTE15 = 'Invalid Forecast Quantity'
39 where ATTRIBUTE11 = 'NEW'
40 and ATTRIBUTE8 = 'FORECAST'
41 and ATTRIBUTE7 < 0 ;
42
43 -- Insert any new items into MSC_ITEMS
44 insert into MSC_ITEMS( INVENTORY_ITEM_ID,
45 ITEM_NAME,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 CREATION_DATE,
49 CREATED_BY )
50 select MSC_ITEMS_S.NEXTVAL,
51 MSD1.ATTRIBUTE3,
52 SYSDATE,
53 l_user_id,
54 SYSDATE,
55 l_user_id
56 from (select distinct MSD.ATTRIBUTE3
57 from msc_st_demands MSD
58 where MSD.ATTRIBUTE11 = 'NEW'
59 and MSD.ATTRIBUTE8 = 'FORECAST'
60 and not exists (select 'exists'
61 from msc_items item
62 where item.item_name = MSD.ATTRIBUTE3)) MSD1;
63
64 -- Do monster insert into msc_system_items for Seller
65
66 insert into MSC_SYSTEM_ITEMS(
67 PLAN_ID, ORGANIZATION_ID,
68 INVENTORY_ITEM_ID, SR_INSTANCE_ID,
69 SR_INVENTORY_ITEM_ID,ITEM_NAME,
70 LOT_CONTROL_CODE, ROUNDING_CONTROL_TYPE,
71 IN_SOURCE_PLAN, MRP_PLANNING_CODE,
72 FULL_LEAD_TIME, UOM_CODE,
73 ATP_COMPONENTS_FLAG, BUILD_IN_WIP_FLAG,
74 PURCHASING_ENABLED_FLAG, PLANNING_MAKE_BUY_CODE,
75 REPETITIVE_TYPE, ENGINEERING_ITEM_FLAG,
76 WIP_SUPPLY_TYPE, SAFETY_STOCK_CODE,
77 EFFECTIVITY_CONTROL, INVENTORY_PLANNING_CODE,
78 CALCULATE_ATP, ATP_FLAG,
79 LAST_UPDATE_DATE, LAST_UPDATED_BY,
80 CREATION_DATE, CREATED_BY)
81 select TO_NUMBER(MSD1.ATTRIBUTE9),
82 MSD1.PARTY_ID,
83 MSD1.INVENTORY_ITEM_ID,
84 TO_NUMBER(MSD1.ATTRIBUTE10),
85 MSD1.INVENTORY_ITEM_ID,
86 MSD1.ATTRIBUTE3,
87 '-1', '-1',
88 '-1', '-1',
89 '-1', MSD1.ATTRIBUTE4,
90 'z', '-1',
91 '-1', '-1',
92 '-1', '-1',
93 '-1', '-1',
94 '-1', '-1',
95 '-1', 'z',
96 SYSDATE, l_user_id,
97 SYSDATE, l_user_id
98 from (select distinct
99 MSD.ATTRIBUTE1,
100 MSD.ATTRIBUTE3,
101 MSD.ATTRIBUTE4,
102 MSD.ATTRIBUTE9,
103 MSD.ATTRIBUTE10,
104 hp.party_id,
105 mi.inventory_item_id
106 from msc_st_demands MSD,
107 hz_parties hp,
108 msc_items mi
109 where mi.item_name = MSD.ATTRIBUTE3
110 and MSD.ATTRIBUTE1 = hp.party_name
111 and hp.party_type = 'ORGANIZATION'
112 and MSD.ATTRIBUTE11 = 'NEW'
113 and MSD.ATTRIBUTE8 = 'FORECAST'
114 and not exists
115 ( select 'exists'
116 from msc_st_demands,
117 hz_parties,
118 msc_system_items msi
119 where msi.organization_id = hp.party_id
120 and hp.party_name = MSD.ATTRIBUTE1
121 and hp.party_type = 'ORGANIZATION'
122 and msi.item_name = MSD.ATTRIBUTE3)) MSD1;
123
124 -- Do monster insert into msc_system_items for Buyer
125
126 insert into MSC_SYSTEM_ITEMS(
127 PLAN_ID, ORGANIZATION_ID,
128 INVENTORY_ITEM_ID, SR_INSTANCE_ID,
129 SR_INVENTORY_ITEM_ID,ITEM_NAME,
130 LOT_CONTROL_CODE, ROUNDING_CONTROL_TYPE,
131 IN_SOURCE_PLAN, MRP_PLANNING_CODE,
132 FULL_LEAD_TIME, UOM_CODE,
133 ATP_COMPONENTS_FLAG, BUILD_IN_WIP_FLAG,
134 PURCHASING_ENABLED_FLAG, PLANNING_MAKE_BUY_CODE,
135 REPETITIVE_TYPE, ENGINEERING_ITEM_FLAG,
136 WIP_SUPPLY_TYPE, SAFETY_STOCK_CODE,
137 EFFECTIVITY_CONTROL, INVENTORY_PLANNING_CODE,
138 CALCULATE_ATP, ATP_FLAG,
139 LAST_UPDATE_DATE, LAST_UPDATED_BY,
140 CREATION_DATE, CREATED_BY)
141 select TO_NUMBER(MSD1.ATTRIBUTE9),
142 MSD1.PARTY_ID,
143 MSD1.INVENTORY_ITEM_ID,
144 TO_NUMBER(MSD1.ATTRIBUTE10),
145 MSD1.INVENTORY_ITEM_ID,
146 MSD1.ATTRIBUTE3,
147 '-1', '-1',
148 '-1', '-1',
149 '-1', MSD1.ATTRIBUTE4,
150 'z', '-1',
151 '-1', '-1',
152 '-1', '-1',
153 '-1', '-1',
154 '-1', '-1',
155 '-1', 'z',
156 SYSDATE, '-1',
157 SYSDATE, '-1'
158 from (select distinct
159 MSD.ATTRIBUTE2,
160 MSD.ATTRIBUTE3,
161 MSD.ATTRIBUTE4,
162 MSD.ATTRIBUTE9,
163 MSD.ATTRIBUTE10,
164 hp.party_id,
165 mi.inventory_item_id
166 from msc_st_demands MSD,
167 hz_parties hp,
168 msc_items mi
169 where mi.item_name = MSD.ATTRIBUTE3
170 and MSD.ATTRIBUTE2 = hp.party_name
171 and hp.party_type = 'ORGANIZATION'
172 and MSD.ATTRIBUTE11 = 'NEW'
173 and MSD.ATTRIBUTE8 = 'FORECAST'
174 and not exists ( select 'exists'
175 from msc_st_demands,
176 hz_parties,
177 msc_system_items msi
178 where msi.organization_id = hp.party_id
179 and hp.party_name = MSD.ATTRIBUTE2
180 and hp.party_type = 'ORGANIZATION'
181 and msi.item_name = MSD.ATTRIBUTE3)) MSD1;
182
183 -- Insert forecast entries
184 insert into msc_demands(
185 DEMAND_ID,
186 USING_REQUIREMENT_QUANTITY,
187 ASSEMBLY_DEMAND_COMP_DATE,
188 USING_ASSEMBLY_DEMAND_DATE,
189 DEMAND_TYPE,
190 USING_ASSEMBLY_ITEM_ID,
191 PLAN_ID,
192 ORGANIZATION_ID,
193 CUSTOMER_ID,
194 INVENTORY_ITEM_ID,
195 SR_INSTANCE_ID,
196 LAST_UPDATE_DATE,
197 LAST_UPDATED_BY,
198 CREATION_DATE,
199 CREATED_BY)
200 select MSC_DEMANDS_S.NEXTVAL,
201 TO_NUMBER(MSD.ATTRIBUTE7),
202 TO_DATE(MSD.ATTRIBUTE6, 'MM/DD/YYYY'),
203 TO_DATE(MSD.ATTRIBUTE5, 'MM/DD/YYYY'),
204 TO_NUMBER(MSD.ATTRIBUTE12),
205 mi.inventory_item_id,
206 TO_NUMBER(MSD.ATTRIBUTE9),
207 seller.party_id,
208 buyer.party_id,
209 mi.inventory_item_id,
210 TO_NUMBER(MSD.ATTRIBUTE10),
211 SYSDATE,
212 '-1',
213 SYSDATE,
214 '-1'
215 from hz_parties seller,
216 hz_parties buyer,
217 msc_items mi,
218 msc_st_demands MSD
219 where seller.party_name = msd.attribute1
220 and seller.party_type = 'ORGANIZATION'
221 and buyer.party_name = msd.attribute2
222 and buyer.party_type = 'ORGANIZATION'
223 and MSD.ATTRIBUTE3 = mi.item_name
224 and MSD.ATTRIBUTE11 = 'NEW'
225 and MSD.ATTRIBUTE8 = 'FORECAST';
226
227 UPDATE msc_st_demands
228 SET ATTRIBUTE11 = 'IMPORTED'
229 where ATTRIBUTE11 = 'NEW'
230 and ATTRIBUTE8 = 'FORECAST';
231
232 retcode := 0;
233 errbuf := null;
234 COMMIT;
235 dbms_mview.refresh('msc_hz_bucketed_demands_mv');
236 exception
237 when others then
238 errbuf := 'Error:' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60);
239 retcode := 2; -- error;
240 return;
241 END Import_Forecast;
242
243 END MSC_IMPORT_FORECAST;