1 PACKAGE BODY MST_SNAPSHOT_TASK_UTIL AS
2 /* $Header: MSTSNTUB.pls 120.1 2005/10/04 22:37:55 saripira noship $ */
3
4 Function getCalendar(lLocationId in number,lCalendarType in VARCHAR2 ) return Varchar2
5 is
6 cursor getOwnerId is
7 select lo.owner_type, lo.OWNER_PARTY_ID
8 from wsh_location_owners lo
9 where lo.wsh_location_id = lLocationId;
10
11 cursor getCustomerId( lownerId in number) is
12 select hzc.CUST_ACCOUNT_ID
13 from hz_cust_accounts hzc
14 where party_id = lownerId
15 and rownum = 1;
16
17 cursor getSupplierId( lownerId in number) is
18 select hzpr.OBJECT_ID
19 from hz_relationships hzpr
20 where hzpr.SUBJECT_ID = lownerId
21 and hzpr.RELATIONSHIP_CODE = 'POS_VENDOR_PARTY'
22 and hzpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
23 AND hzpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
24 AND hzpr.DIRECTIONAL_FLAG = 'F'
25 and rownum = 1;
26
27 cursor hr_Calendar(lownerId in number) is
28 select assg.CALENDAR_CODE
29 from wsh_calendar_assignments assg
30 WHERE
31 CALENDAR_TYPE = lCalendarType --'SHIPPING' or 'RECEIVING'
32 AND ( (assg.LOCATION_ID = lLocationId AND
33 ASSOCIATION_TYPE = 'HR_LOCATION') OR
34 (assg.ORGANIZATION_ID = lownerId AND
35 ASSOCIATION_TYPE = 'ORGANIZATION')
36 )
37 Order By assg.LOCATION_ID;
38
39 cursor cust_loc_calendar (lCustId in number) is
40 select assg.CALENDAR_CODE
41 from wsh_calendar_assignments assg
42 WHERE assg.LOCATION_ID = lLocationId
43 AND CALENDAR_TYPE = lCalendarType --'SHIPPING' or 'RECEIVING'
44 AND ASSOCIATION_TYPE = 'CUSTOMER_SITE';
45
46 cursor cust_calendar(lCustId in number) is
47 select assg.CALENDAR_CODE
48 from wsh_calendar_assignments assg
49 WHERE assg.CUSTOMER_ID = lCustId
50 AND CALENDAR_TYPE = lCalendarType --'SHIPPING' or 'RECEIVING'
51 AND ASSOCIATION_TYPE = 'CUSTOMER';
52
53 cursor car_loc_calendar(lCarrierId in number) is
54 select assg.CALENDAR_CODE
55 from wsh_calendar_assignments assg
56 WHERE assg.LOCATION_ID = lLocationId
57 AND assg.carrier_id = lCarrierId
58 AND CALENDAR_TYPE = lCalendarType --'SHIPPING' or 'RECEIVING'
59 AND ASSOCIATION_TYPE = 'CARRIER_SITE';
60
61 cursor car_calendar(lCarrier_id in number) is
62 select assg.CALENDAR_CODE
63 from wsh_calendar_assignments assg
64 WHERE
65 assg.carrier_id = lCarrier_id
66 AND CALENDAR_TYPE = lCalendarType --'SHIPPING' or 'RECEIVING'
67 AND ASSOCIATION_TYPE = 'CARRIER';
68
69 cursor sup_loc_calendar(lSupplierId in number) is
70 select assg.CALENDAR_CODE
71 from wsh_calendar_assignments assg
72 WHERE assg.LOCATION_ID = lLocationId
73 AND assg.VENDOR_ID = lSupplierId
74 AND assg.CALENDAR_TYPE = lCalendarType
75 AND assg.ASSOCIATION_TYPE = 'VENDOR_SITE';
76
77 cursor sup_calendar(lSupplierId in number) is
78 select assg.CALENDAR_CODE
79 from wsh_calendar_assignments assg
80 WHERE assg.VENDOR_ID = lSupplierId
81 AND assg.CALENDAR_TYPE = lCalendarType
82 AND assg.ASSOCIATION_TYPE = 'VENDOR';
83
84 lOwnerType NUMBER;
85 lOwnerId NUMBER;
86 lCustomerId NUMBER;
87 lSupplierId NUMBER;
88 lCalendar VARCHAR2(30);
89
90 begin
91 open getOwnerId;
92 fetch getOwnerId into lOwnerType, lOwnerId;
93 close getOwnerId;
94
95 if lOwnerType = 1 then -- ORG
96 open hr_Calendar(lOwnerId);
97 fetch hr_Calendar into lCalendar;
98 if hr_Calendar%NOTFOUND then
99 lCalendar := NULL_CHAR_VALUE;
100 return(lCalendar);
101 end if;
102 close hr_Calendar;
103
104 elsif lOwnerType = 2 then --customer
105 open getCustomerId(lOwnerId);
106 fetch getCustomerId into lCustomerId;
107 if getCustomerId%NOTFOUND then
108 lCalendar := NULL_CHAR_VALUE;
109 return(lCalendar);
110 end if;
111 close getCustomerId;
112
113 open cust_loc_Calendar(lCustomerId);
114 fetch cust_loc_Calendar into lCalendar;
115 if cust_loc_Calendar%NOTFOUND then
116 open cust_calendar(lCustomerId) ;
117 fetch cust_calendar into lCalendar;
118 if cust_calendar%NOTFOUND then
119 lCalendar := NULL_CHAR_VALUE;
120 end if;
121 close cust_calendar;
122 end if;
123 close cust_loc_Calendar;
124 elsif lOwnerType = 3 then --carrier
125 open car_loc_Calendar(lOwnerId);
126 fetch car_loc_Calendar into lCalendar;
127 if car_loc_Calendar%NOTFOUND then
128 open car_calendar(lOwnerId) ;
129 fetch car_calendar into lCalendar;
130 if car_calendar%NOTFOUND then
131 lCalendar := NULL_CHAR_VALUE;
132 end if;
133 close car_calendar;
134 end if;
135 close car_loc_Calendar;
136 elsif lOwnerType = 4 then --suppliers
137 open getSupplierId(lOwnerId);
138 fetch getSupplierId into lSupplierId;
139 if getSupplierId%NOTFOUND then
140 lCalendar := NULL_CHAR_VALUE;
141 return(lCalendar);
142 end if;
143 close getSupplierId;
144
145 open sup_loc_Calendar(lSupplierId);
146 fetch sup_loc_Calendar into lCalendar;
147 if sup_loc_Calendar%NOTFOUND then
148 open sup_Calendar(lSupplierId) ;
149 fetch sup_Calendar into lCalendar;
150 if sup_Calendar%NOTFOUND then
151 lCalendar := NULL_CHAR_VALUE;
152 end if;
153 close sup_Calendar;
154 end if;
155 close sup_loc_Calendar;
156 else
157 lCalendar := NULL_CHAR_VALUE;
158 end if;
159
160 if lCalendar <> NULL_CHAR_VALUE then
161 /* calendar must be alredy build. */
162
163 select CALENDAR_CODE
164 into lCalendar
165 from bom_calendar_dates
166 where CALENDAR_CODE = lCalendar
167 and rownum = 1;
168
169 end if;
170 return(lCalendar);
171
172 exception
173 when no_Data_found then
174 lCalendar := NULL_CHAR_VALUE;
175 return(lCalendar);
176
177 end;
178
179
180
181 Function getDeliveryId (ldeliveryId in number,
182 lNullNumber in number) return NUMBER
183 is
184 lDelId Number ;
185 begin
186 select delivery_id
187 into lDelId
188 from wsh_new_Deliveries
189 where Delivery_id = ldeliveryId
190 AND planned_flag in ( 'F' ,'Y');
191
192 return(lDelId);
193 exception when others then
194 lDelId := lNullNumber;
195 return(lDelId);
196
197 return(lDelId);
198 end;
199
200
201
202 Function getCMVehicleType (lMoveId in number) return NUMBER
203 is
204 lVehicleTypeId NUMBER;
205 Cursor vehileType is
206 select fvt.vehicle_type_id
207 from wsh_trips t,
208 fte_trip_moves wt,
209 fte_vehicle_types fvt
210 WHERE t.trip_id = wt.TRIP_ID
211 and wt.MOVE_ID = lMoveId
212 and t.VEHICLE_ITEM_ID = fvt.INVENTORY_ITEM_ID
213 and t.VEHICLE_ORGANIZATION_ID = fvt.ORGANIZATION_ID;
214 begin
215 open vehileType;
216 fetch vehileType into lVehicleTypeId;
217 close vehileType;
218
219 return(lVehicleTypeId);
220 exception when others then
221 lVehicleTypeId := null;
222
223 return(lVehicleTypeId);
224 end;
225
226
227 Function GET_DEL_OSP_FLAG(ldelivery_id in NUMBER) return varchar2 is
228 l_osp_flag varchar2(1) ;
229 begin
230 select decode(det.source_line_type_code, 'OSP','Y','N')
231 into l_osp_flag
232 from wsh_Delivery_details det,
233 wsh_delivery_assignments assg,
234 wsh_new_deliveries del
235 where del.delivery_id= ldelivery_id
236 AND del.delivery_id = assg.delivery_id
237 AND assg.delivery_detail_id = det.delivery_detail_id
238 AND rownum = 1;
239 return(l_osp_flag);
240 exception
241 when no_data_found then
242 l_osp_flag := 'N';
243 return(l_osp_flag);
244 end;
245
246
247 Procedure LOG_MESSAGE( pBUFF IN VARCHAR2)
248 IS
249 l number := 0;
250 BEGIN
251 loop
252 if l > LENGTH(pBUFF) then
253 exit;
254 else
255 if l+80 > LENGTH(pBUFF) then
256 --dbms_output.put_line(substrb(pBUFF,l,LENGTH(pBUFF)- l));
257 null;
258 else
259 null;
260 --dbms_output.put_line(substrb(pBUFF,l,80));
261 end if;
262 l := l+80;
263 end if;
264 end loop;
265 --dbms_output.put_line(pBUFF);
266 END LOG_MESSAGE;
267
268
269 Procedure Get_Phase_Status_Code(p_rqst_id IN NUMBER, p_phase_code OUT NOCOPY VARCHAR2, p_status_code OUT NOCOPY VARCHAR2) is
270 pragma autonomous_transaction;
271
272 cursor Cur_Rqst_Details (l_rqst_Id IN NUMBER)
273 is
274 SELECT PHASE_CODE,STATUS_CODE
275 FROM Fnd_Concurrent_Requests
276 WHERE request_id = l_rqst_Id;
277
278 begin
279
280 open Cur_Rqst_Details (p_rqst_id);
281 fetch Cur_Rqst_Details into p_phase_code, p_status_code;
282 close Cur_Rqst_Details;
283
284 end Get_Phase_Status_Code;
285
286
287
288
289 END MST_SNAPSHOT_TASK_UTIL;
[Home] [Help]