[Home] [Help]
PACKAGE BODY: APPS.MSC_IMPORT_ONHAND
Source
1 PACKAGE BODY MSC_IMPORT_ONHAND AS
2 /* $Header: MSCIOHDB.pls 120.1 2005/06/21 02:45:02 appldev ship $ */
3
4 PROCEDURE Import_Onhand(
5 ERRBUF OUT NOCOPY VARCHAR2,
6 RETCODE OUT NOCOPY NUMBER,
7 v_req_id IN NUMBER) -- not used this release
8
9 IS
10 l_user_id number := fnd_global.user_id;
11 BEGIN
12 -- Now for another kludge. First thing that we will do is to update
13 -- all the fields in the msc_st_demands tables with the mapped supplier
14 -- codes on the exchange
15 -- Rob, please check and then run into the database as soon as Bala
16 -- Desikan gives us the mapping codes
17 /*
18 update msc_st_demands
19 set attribute1 = decode(attribute1,'F159B','',
20 'T407H','',
21 'U0WRC','',
22 'EE02A','',
23 'CC05A','',
24 'AE2DA','',NULL),
25 attribute2 = decode(attribute2,'F159B','',
26 'T407H','',
27 'U0WRC','',
28 'EE02A','',
29 'CC05A','',
30 'AE2DA','',NULL)
31 where attribute11 = 'NEW'
32 and attribute8 in ('ONHAND','FORECAST');
33 */
34 -- Validate Buyer Code
35 update MSC_ST_DEMANDS
36 set ATTRIBUTE11 = 'ERROR',
37 ATTRIBUTE15 = 'Invalid Buyer Code'
38 where not exists ( select 'exists'
39 from HZ_PARTIES HP,
40 MSC_ST_DEMANDS MSD
41 where HP.PARTY_NAME = MSD.ATTRIBUTE1
42 and HP.PARTY_TYPE = 'ORGANIZATION')
43 and ATTRIBUTE11 = 'NEW'
44 and ATTRIBUTE8 = 'ONHAND';
45
46 -- Validate Quantity
47 update MSC_ST_DEMANDS
48 set ATTRIBUTE11 = 'ERROR',
49 ATTRIBUTE15 = 'Invalid Quantity'
50 where ATTRIBUTE11 = 'NEW'
51 and ATTRIBUTE8 = 'ONHAND'
52 and ATTRIBUTE7 is null;
53
54 -- Insert any new items into MSC_ITEMS
55 insert into MSC_ITEMS( INVENTORY_ITEM_ID,
56 ITEM_NAME,
57 DESCRIPTION,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 CREATION_DATE,
61 CREATED_BY )
62 select MSC_ITEMS_S.NEXTVAL,
63 MSD1.ATTRIBUTE3,
64 MSD1.ATTRIBUTE13,
65 SYSDATE,
66 l_user_id,
67 SYSDATE,
68 l_user_id
69 from (select distinct MSD.ATTRIBUTE3 , MSD.ATTRIBUTE13
70 from MSC_ST_DEMANDS MSD
71 where MSD.ATTRIBUTE11 = 'NEW'
72 and MSD.ATTRIBUTE8 = 'ONHAND'
73 and not exists (select 'exists'
74 from msc_items item
75 where item.item_name = MSD.ATTRIBUTE3)) MSD1;
76
77 -- Do monster insert into msc_system_items for Buyer
78
79 insert into MSC_SYSTEM_ITEMS(
80 PLAN_ID, ORGANIZATION_ID,
81 INVENTORY_ITEM_ID, SR_INSTANCE_ID,
82 SR_INVENTORY_ITEM_ID,ITEM_NAME,
83 LOT_CONTROL_CODE, ROUNDING_CONTROL_TYPE,
84 IN_SOURCE_PLAN, MRP_PLANNING_CODE,
85 FULL_LEAD_TIME, UOM_CODE,
86 ATP_COMPONENTS_FLAG, BUILD_IN_WIP_FLAG,
87 PURCHASING_ENABLED_FLAG, PLANNING_MAKE_BUY_CODE,
88 REPETITIVE_TYPE, ENGINEERING_ITEM_FLAG,
89 WIP_SUPPLY_TYPE, SAFETY_STOCK_CODE,
90 EFFECTIVITY_CONTROL, INVENTORY_PLANNING_CODE,
91 CALCULATE_ATP, ATP_FLAG,
92 LAST_UPDATE_DATE, LAST_UPDATED_BY,
93 CREATION_DATE, CREATED_BY)
94 select TO_NUMBER(MSD1.ATTRIBUTE9),
95 MSD1.PARTY_ID,
96 MSD1.INVENTORY_ITEM_ID,
97 TO_NUMBER(MSD1.ATTRIBUTE10),
98 MSD1.INVENTORY_ITEM_ID,
99 MSD1.ATTRIBUTE3,
100 '-1', '-1',
101 '-1', '-1',
102 '-1', MSD1.ATTRIBUTE4,
103 'z', '-1',
104 '-1', '-1',
105 '-1', '-1',
106 '-1', '-1',
107 '-1', '-1',
108 '-1', 'z',
109 SYSDATE, l_user_id,
110 SYSDATE, l_user_id
111 from (select distinct
112 MSD.ATTRIBUTE1,
113 MSD.ATTRIBUTE3,
114 MSD.ATTRIBUTE4,
115 MSD.ATTRIBUTE9,
116 MSD.ATTRIBUTE10,
117 hp.party_id,
118 mi.inventory_item_id
119 from MSC_ST_DEMANDS MSD,
120 hz_parties hp,
121 msc_items mi
122 where mi.item_name = MSD.ATTRIBUTE3
123 and MSD.ATTRIBUTE1 = hp.party_name
124 and hp.party_type = 'ORGANIZATION'
125 and MSD.ATTRIBUTE11 = 'NEW'
126 and MSD.ATTRIBUTE8 = 'ONHAND'
127 and not exists
128 ( select 'exists'
129 from MSC_ST_DEMANDS,
130 hz_parties,
131 msc_system_items msi
132 where msi.organization_id = hp.party_id
133 and hp.party_name = MSD.ATTRIBUTE1
134 and hp.party_type = 'ORGANIZATION'
135 and msi.item_name = MSD.ATTRIBUTE3)) MSD1;
136
137 -- Insert Onhand into msc_supplies
138
139 insert into msc_supplies(
140 PLAN_ID,
141 TRANSACTION_ID,
142 ORGANIZATION_ID,
143 SR_INSTANCE_ID,
144 INVENTORY_ITEM_ID,
145 NEW_SCHEDULE_DATE,
146 ORDER_TYPE,
147 NEW_ORDER_QUANTITY,
148 FIRM_PLANNED_TYPE,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 CREATION_DATE,
152 CREATED_BY)
153 select TO_NUMBER(MSD.ATTRIBUTE9),
154 MSC_SUPPLIES_S.NEXTVAL,
155 hz.party_id,
156 TO_NUMBER(MSD.ATTRIBUTE10),
157 mi.inventory_item_id,
158 TO_DATE(MSD.ATTRIBUTE5, 'MM/DD/YYYY'),
159 to_number(MSD.ATTRIBUTE14), -- 18
160 TO_NUMBER(LTRIM(MSD.ATTRIBUTE7,'0')),
161 '-1',
162 SYSDATE,
163 l_user_id,
164 SYSDATE,
165 l_user_id
166 from hz_parties hz,
167 MSC_ST_DEMANDS MSD,
168 msc_items mi
169 where MSD.ATTRIBUTE3 = mi.item_name
170 and MSD.ATTRIBUTE11 = 'NEW'
171 and MSD.ATTRIBUTE8 = 'ONHAND'
172 and hz.party_name = MSD.ATTRIBUTE1
173 and hz.party_type = 'ORGANIZATION';
174
175 UPDATE MSC_ST_DEMANDS
176 SET ATTRIBUTE11 = 'IMPORTED'
177 where ATTRIBUTE11 = 'NEW'
178 and ATTRIBUTE8 = 'ONHAND';
179
180
181 retcode := 0;
182 errbuf := null;
183 COMMIT;
184 exception
185 when others then
186 errbuf := 'Error:' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60);
187 retcode := 2; -- error;
188 return;
189
190 END Import_ONHAND;
191
192 FUNCTION get_onhand(
193 arg_plan_id IN NUMBER,
194 arg_org_id IN NUMBER,
195 arg_instance IN NUMBER,
196 arg_item_id IN NUMBER)
197 return NUMBER
198 IS
199 l_onhand number := 0;
200 begin
201 select sum(nvl(new_order_quantity,0))
202 into l_onhand
203 from msc_supplies
204 where plan_id = arg_plan_id
205 and organization_id = arg_org_id
206 and sr_instance_id = arg_instance
207 and inventory_item_id = arg_item_id
208 and order_type = 18;
209
210 return(l_onhand);
211 EXCEPTION
212 when no_data_found then
213 return(0);
214 END get_onhand;
215
216 END MSC_IMPORT_ONHAND;