[Home] [Help]
PACKAGE BODY: APPS.QA_SS_SHIP
Source
1 package body qa_ss_ship as
2 /* $Header: qltssshb.plb 120.2 2006/02/09 05:43:20 saugupta noship $ */
3
4 function are_ship_plans_applicable (
5 P_Po_Number IN VARCHAR2 DEFAULT NULL,
6 P_Po_Line_Num IN VARCHAR2 DEFAULT NULL,
7 P_Po_Release_Num IN VARCHAR2 DEFAULT NULL,
8 P_Shipment_Num IN VARCHAR2 DEFAULT NULL,
9 P_Location IN VARCHAR2 DEFAULT NULL,
10 P_Supplier_Item IN VARCHAR2 DEFAULT NULL,
11 P_Ord_Qty IN VARCHAR2 DEFAULT NULL,
12 P_Uom_Name IN VARCHAR2 DEFAULT NULL,
13 P_Vendor IN VARCHAR2 DEFAULT NULL,
14 P_Vendor_Site IN VARCHAR2 DEFAULT NULL,
15 P_Organization IN VARCHAR2 DEFAULT NULL,
16 P_Item IN VARCHAR2 DEFAULT NULL,
17 P_Item_Rev IN VARCHAR2 DEFAULT NULL,
18 P_Item_Cat IN VARCHAR2 DEFAULT NULL
19 )
20 -- Ideally, i need one more parameter called p_organization_id
21 -- do this next time to improve performance
22 -- This needs change to view icx_pos_qa_shipments_V to pass extra argument
23 Return VARCHAR2
24
25 IS
26 Ctx qa_ss_const.Ctx_Table;
27 l_organization_id NUMBER := NULL;
28 p_category_id NUMBER;
29 p_category VARCHAR2(240);
30
31 --anagarwa Wed Nov 14 12:30:30 PST 2001
32 -- cursor modified to have corret case for Organizations
33
34 -- Bug 4958773. SQL Repository Fix SQL ID: 15008412
35 CURSOR org_cur(x_org_name IN VARCHAR2) IS
36 SELECT
37 organization_id
38 FROM inv_organization_name_v
39 WHERE lower(organization_name) = lower(x_org_name);
40 /*
41 SELECT organization_id
42 FROM ORG_ORGANIZATION_DEFINITIONS
43 WHERE lower(organization_name) = lower(x_org_name);
44 */
45
46 BEGIN
47 -- dont think icx validate session is needed here, becos this is
48 -- a function called as part of po view definition
49 -- check the above
50
51 Ctx(qa_ss_const.Po_Number) := P_Po_Number;
52 Ctx(qa_ss_const.Po_Line_Num) := P_Po_Line_Num;
53 Ctx(qa_ss_const.Po_Release_Num) := P_Po_Release_Num;
54 Ctx(qa_ss_const.Po_Shipment_Num) := P_Shipment_Num;
55 Ctx(qa_ss_const.Ship_To_Location) := P_Location;
56 Ctx(qa_ss_const.Vender_Item_Number) := P_Supplier_Item;
57 Ctx(qa_ss_const.Ordered_Quantity) := P_Ord_Qty;
58 Ctx(qa_ss_const.UOM_Name) := P_Uom_Name;
59 Ctx(qa_ss_const.Vendor_Name) := P_Vendor;
60 Ctx(qa_ss_const.Vendor_Site_code) := P_vendor_site;
61 Ctx(qa_ss_const.Ship_To) := P_Organization;
62 Ctx(qa_ss_const.Item) := P_Item;
63 Ctx(qa_ss_const.Revision) := P_Item_Rev;
64
65 If (P_Organization is NOT NULL) THEN
66 OPEN org_cur(P_Organization);
67 FETCH org_cur INTO l_organization_id;
68 CLOSE org_cur;
69 END IF;
70
71 -- anagarwa Tue Nov 13 17:26:18 PST 2001
72 -- PO calls this function in their view pos_po_qa_shipments_v .
73 -- The last parameter they pass us is item_category. But the
74 -- supplied value is incorrect as they do not check for the profile
75 -- FND_PROFILE.VALUE('QA_CATEGORY_SET').
76 -- To circumvent this dependency and to avoid changes in POS code,
77 -- I'm adding following logic to derive item category at our end
78 -- and ignore supplied value.
79 qa_ss_core.get_item_category_val(
80 p_org_id => l_Organization_Id,
81 p_item_val => P_Item,
82 x_category_val => p_category,
83 x_category_id => p_category_id);
84 Ctx(qa_ss_const.Item_Category) := p_category;
85
86 IF ( qa_ss_core.any_applicable_plans( Ctx, 110, l_Organization_Id))
87 THEN
88 return 'Y'; -- there are applicable plans
89 ELSE
90 return 'N'; -- No plans
91 End If;
92
93 -- argument 110 is txn_num
94
95
96 END are_ship_plans_applicable;
97 --------------------------------------------------------------------------
98
99 procedure ship_to_quality (
100 PK1 IN VARCHAR2 DEFAULT NULL,
101 PK2 IN VARCHAR2 DEFAULT NULL,
102 PK3 IN VARCHAR2 DEFAULT NULL,
103 PK4 IN VARCHAR2 DEFAULT NULL,
104 PK5 IN VARCHAR2 DEFAULT NULL,
105 PK6 IN VARCHAR2 DEFAULT NULL,
106 PK7 IN VARCHAR2 DEFAULT NULL,
107 PK8 IN VARCHAR2 DEFAULT NULL,
108 PK9 IN VARCHAR2 DEFAULT NULL,
109 PK10 IN VARCHAR2 DEFAULT NULL,
110 c_outputs1 OUT NOCOPY VARCHAR2,
111 c_outputs2 OUT NOCOPY VARCHAR2,
112 c_outputs3 OUT NOCOPY VARCHAR2,
113 c_outputs4 OUT NOCOPY VARCHAR2,
114 c_outputs5 OUT NOCOPY VARCHAR2,
115 c_outputs6 OUT NOCOPY VARCHAR2,
116 c_outputs7 OUT NOCOPY VARCHAR2,
117 c_outputs8 OUT NOCOPY VARCHAR2,
118 c_outputs9 OUT NOCOPY VARCHAR2,
119 c_outputs10 OUT NOCOPY VARCHAR2)
120
121 IS
122
123
124 BEGIN
125 if (icx_sec.validatesession) then
126
127 qa_ss_core.plan_list_frames(110, PK1, PK2, PK3, PK4, PK5, PK6, PK7, PK8, PK9, PK10);
128
129
130 end if; -- end icx validate session
131 EXCEPTION
132 WHEN OTHERS THEN
133 htp.p('Exception in procedure ship_to_quality');
134 htp.p(SQLERRM);
135
136 END ship_to_quality;
137
138 ------------------------------------------------------------------------------------------
139
140 procedure default_ship_values (Ctx IN OUT NOCOPY qa_ss_const.Ctx_Table,
141 Txn_Num IN NUMBER DEFAULT NULL,
142 PK1 IN VARCHAR2 DEFAULT NULL,
143 PK2 IN VARCHAR2 DEFAULT NULL,
144 PK3 IN VARCHAR2 DEFAULT NULL,
145 PK4 IN VARCHAR2 DEFAULT NULL,
146 PK5 IN VARCHAR2 DEFAULT NULL,
147 PK6 IN VARCHAR2 DEFAULT NULL,
148 PK7 IN VARCHAR2 DEFAULT NULL,
149 PK8 IN VARCHAR2 DEFAULT NULL,
150 PK9 IN VARCHAR2 DEFAULT NULL,
151 PK10 IN VARCHAR2 DEFAULT NULL,
152 X_PO_AGENT_ID OUT NOCOPY NUMBER,
153 X_Item_Id OUT NOCOPY NUMBER,
154 X_PO_HEADER_ID OUT NOCOPY NUMBER,
155 X_Wip_Entity_Type OUT NOCOPY NUMBER,
156 X_Wip_Rep_Sch_Id OUT NOCOPY NUMBER,
157 X_Po_Release_Id OUT NOCOPY NUMBER,
158 X_Po_Line_Id OUT NOCOPY NUMBER,
159 X_Line_Location_Id OUT NOCOPY NUMBER,
160 X_Po_Distribution_Id OUT NOCOPY NUMBER,
161 X_Wip_Entity_Id OUT NOCOPY NUMBER,
162 X_Wip_Line_Id OUT NOCOPY NUMBER,
163 X_Po_Shipment_Id OUT NOCOPY NUMBER,
164 X_Organization_Id OUT NOCOPY NUMBER)
165
166 IS
167 l_po_header_id NUMBER := NULL;
168 l_po_release_id NUMBER := NULL;
169
170 -- Po Header Id and release id columns are added to the below cursor
171 -- only for the purpose of finding out po_agent_id
172 -- fixing base_po_num below for Bug 1241396
173
174 -- R12 Project MOAC 4637896
175 -- Changed view ICX_POS_QA_SHIPMENTS_V to POS_PO_QA_SHIPMENTS_V
176 CURSOR def_ship_cur IS
177 SELECT BASE_PO_NUM, LINE_NUMBER,
178 PO_RELEASE_ID, SHIPMENT_NUMBER,
179 Ship_To_Location_Code, Supplier_Item_Number,
180 Quantity_Ordered, Unit_of_Measure_code,
181 supplier_name, supplier_site_code,
182 ship_to_organization_name,
183 Item_Number, Item_Revision,
184 Category, PO_HEADER_ID, Item_ID, -- already selected po_release_id above
185 Po_Line_Id, Po_Shipment_Id, Ship_To_Organization_Id
186 FROM pos_po_qa_shipments_v
187 Where Po_Shipment_Id = to_number(PK1);
188
189 CURSOR rel_num_cur (x_rel_id IN NUMBER) IS
190 SELECT RELEASE_NUM
191 FROM PO_RELEASES_ALL
192 WHERE PO_RELEASE_ID = x_rel_id;
193
194 CURSOR buyer1_cur(p_h_id IN NUMBER) IS
195 SELECT AGENT_ID
196 FROM PO_HEADERS_ALL
197 where po_header_id = p_h_id;
198
199 CURSOR buyer2_cur(p_rel_id IN NUMBER) IS
200 SELECT AGENT_ID
201 FROM PO_RELEASES_ALL
202 where PO_RELEASE_ID = p_rel_id;
203
204 -- R12 Project MOAC 4637896
205 -- Completely removed cursor operating_unit_cur
206
207 BEGIN
208
209 OPEN def_ship_cur;
210 FETCH def_ship_cur
211 INTO Ctx(qa_ss_const.Po_Number),
212 Ctx(qa_ss_const.Po_Line_Num),
213 Ctx(qa_ss_const.Po_Release_Num),
214 Ctx(qa_ss_const.Po_Shipment_Num),
215 Ctx(qa_ss_const.Ship_To_Location),
216 Ctx(qa_ss_const.Vender_Item_Number),
217 Ctx(qa_ss_const.Ordered_Quantity),
218 Ctx(qa_ss_const.UOM_Name),
219 Ctx(qa_ss_const.Vendor_Name),
220 Ctx(qa_ss_const.Vendor_Site_code),
221 Ctx(qa_ss_const.Ship_To),
222 Ctx(qa_ss_const.Item),
223 Ctx(qa_ss_const.Revision),
224 Ctx(qa_ss_const.Item_Category),
225 l_po_header_id, X_Item_Id,
226 X_Po_Line_Id, X_Po_Shipment_Id, X_Organization_Id;
227
228 -- Assign l_po_release_id
229 l_po_release_id := Ctx(qa_ss_const.Po_Release_Num);
230 -- Dont change this. We do X_po_release_id assignment
231 -- below
232 X_Po_Header_ID := l_po_header_id; -- for sake of out variable
233 X_Po_Release_ID := l_po_release_id;
234
235 X_Wip_Entity_Type := NULL; -- These have no value in Shipments
236 X_Wip_Rep_Sch_Id := NULL;
237 X_Line_Location_Id := NULL;
238 X_Po_Distribution_Id := NULL;
239 X_Wip_Entity_Id := NULL;
240 X_Wip_Line_Id := NULL;
241
242 CLOSE def_ship_cur;
243 -- now ctx(release_num) actually has release id. lets get release num
244 IF ( Ctx(qa_ss_const.Po_Release_Num) is NOT NULL) Then
245 OPEN rel_num_cur( Ctx(qa_ss_const.Po_Release_Num) );
246 FETCH rel_num_cur INTO Ctx(qa_ss_const.Po_Release_Num);
247 CLOSE rel_num_cur;
248 END IF;
249
250 -- Adding code to find buyer ie. Po_Agent_Id
251 If (l_po_release_id is NOT NULL) Then
252 OPEN buyer2_cur(l_po_release_id);
253 FETCH buyer2_cur INTO X_PO_AGENT_ID;
254 CLOSE buyer2_cur;
255 Elsif (l_po_header_id is NOT NULL) Then
256 OPEN buyer1_cur(l_po_header_id);
257 FETCH buyer1_cur INTO X_PO_AGENT_ID;
258 CLOSE buyer1_cur;
259 Else
260 X_PO_AGENT_ID := NULL;
261 END IF; -- end buyer processing
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF def_ship_cur%ISOPEN THEN
266 CLOSE def_ship_cur;
267 End If;
268 IF rel_num_cur%ISOPEN THEN
269 CLOSE rel_num_cur;
270 End If;
271 IF buyer1_cur%ISOPEN THEN
272 CLOSE buyer1_cur;
273 End If;
274 IF buyer2_cur%ISOPEN THEN
275 CLOSE buyer2_cur;
276 End If;
277 htp.p('Exception in procedure default_ship_values');
278 htp.p(SQLERRM);
279
280 END default_ship_values;
281 ----------------------------------------------------------------------------------------------
282 procedure shipping_plans (
283 PK1 IN VARCHAR2 DEFAULT NULL,
284 PK2 IN VARCHAR2 DEFAULT NULL,
285 PK3 IN VARCHAR2 DEFAULT NULL,
286 PK4 IN VARCHAR2 DEFAULT NULL,
287 PK5 IN VARCHAR2 DEFAULT NULL,
288 PK6 IN VARCHAR2 DEFAULT NULL,
289 PK7 IN VARCHAR2 DEFAULT NULL,
290 PK8 IN VARCHAR2 DEFAULT NULL,
291 PK9 IN VARCHAR2 DEFAULT NULL,
292 PK10 IN VARCHAR2 DEFAULT NULL )
293 IS
294 P_Organization_Id NUMBER;
295 Ctx qa_ss_const.Ctx_Table;
296
297 -- fixing base_po_num for bug 1241396
298
299 -- R12 Project MOAC 4637896
300 -- Changed view ICX_POS_QA_SHIPMENTS_V to POS_PO_QA_SHIPMENTS_V
301 CURSOR ship_cur IS
302 SELECT BASE_PO_NUM, LINE_NUMBER,
303 PO_RELEASE_ID, SHIPMENT_NUMBER,
304 Ship_To_Location_Code, Supplier_Item_Number,
305 Quantity_Ordered, Unit_of_Measure_code,
306 supplier_name, supplier_site_code,
307 ship_to_organization_name,
308 Item_Number, Item_Revision,
309 Category, Ship_To_Organization_ID
310 FROM pos_po_qa_shipments_v
311 Where Po_Shipment_Id = to_number(PK1);
312
313 CURSOR rel_num_cur (x_rel_id IN NUMBER) IS
314 SELECT RELEASE_NUM
315 FROM PO_RELEASES_ALL
316 WHERE PO_RELEASE_ID = x_rel_id;
317
318 BEGIN
319
320 IF (icx_sec.validateSession) THEN
321 -- htp.p(' PK1 = ' || PK1); htp.nl;
322 -- htp.p('PK2 = ' || PK2) ; htp.nl;
323 -- htp.p('PK3 = ' || PK3); htp.nl;
324 -- htp.p('PK10 = ' || PK10); htp.nl;
325 OPEN ship_cur;
326 -- htp.p('ship_cur opened'); htp.nl;
327 FETCH ship_cur
328 INTO Ctx(qa_ss_const.Po_Number),
329 Ctx(qa_ss_const.Po_Line_Num),
330 Ctx(qa_ss_const.Po_Release_Num),
331 Ctx(qa_ss_const.Po_Shipment_Num),
332 Ctx(qa_ss_const.Ship_To_Location),
333 Ctx(qa_ss_const.Vender_Item_Number),
334 Ctx(qa_ss_const.Ordered_Quantity),
335 Ctx(qa_ss_const.UOM_Name),
336 Ctx(qa_ss_const.Vendor_Name),
337 Ctx(qa_ss_const.Vendor_Site_code),
338 Ctx(qa_ss_const.Ship_To),
339 Ctx(qa_ss_const.Item),
340 Ctx(qa_ss_const.Revision),
341 Ctx(qa_ss_const.Item_Category),
342 P_Organization_Id;
343 -- htp.p('ship_cur fetched successfully'); htp.nl;
344 CLOSE ship_cur;
345 -- htp.p('ship_cur closed successfully'); htp.nl;
346 -- now ctx(release_num) actually has release id. lets get release num
347
348 IF ( Ctx(qa_ss_const.Po_Release_Num) is NOT NULL) Then
349 -- htp.p('attempt to fetch release num'); htp.nl;
350 OPEN rel_num_cur( Ctx(qa_ss_const.Po_Release_Num) );
351 FETCH rel_num_cur INTO Ctx(qa_ss_const.Po_Release_Num);
352 CLOSE rel_num_cur;
353 -- htp.p('successful rel num fetch'); htp.nl;
354 END IF;
355 -- Now Ctx is populated. Also, populated P_Organization_Id above
356 -- htp.p('before call to all applicable plans'); htp.nl;
357 qa_ss_core.all_applicable_plans( Ctx, 110, P_Organization_Id, PK1, PK2,Pk3,
358 Pk4,PK5,PK6,PK7,PK8,PK9,PK10);
359 -- htp.p('after call to all_applicable_plans'); htp.nl;
360
361 END IF; -- end icx validate session
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 IF ship_cur%ISOPEN THEN
366 CLOSE ship_cur;
367 End If;
368 IF rel_num_cur%ISOPEN THEN
369 CLOSE rel_num_cur;
370 End If;
371
372 htp.p('Exception in procedure qa_ss_ship.shipping plans');
373 htp.p(SQLERRM);
374
375
376 END shipping_plans;
377 ----------------------------------------------------------------------------------------------
378
379
380
381 end qa_ss_ship;
382