1 package body qa_ss_osp as
2 /* $Header: qltssopb.plb 120.1 2005/10/02 02:51:52 bso noship $ */
3
4
5 function are_osp_plans_applicable (
6 P_Item_Number IN VARCHAR2 DEFAULT NULL,
7 P_Supplier IN VARCHAR2 DEFAULT NULL,
8 P_Wip_Entity_Name IN VARCHAR2 DEFAULT NULL,
9 P_Po_Number IN VARCHAR2 DEFAULT NULL,
10 P_Vendor_Item_Number IN VARCHAR2 DEFAULT NULL,
11 P_Wip_Operation_Seq_Num IN NUMBER DEFAULT NULL,
12 P_UOM_Name IN VARCHAR2 DEFAULT NULL,
13 P_Production_Line IN VARCHAR2 DEFAULT NULL,
14 P_Quantity_Ordered IN NUMBER DEFAULT NULL,
15 P_Item_Revision IN VARCHAR2 DEFAULT NULL,
16 P_Po_Release_Number IN NUMBER DEFAULT NULL,
17 P_Organization_Id IN NUMBER DEFAULT NULL,
18 P_Wip_Entity_Type IN NUMBER DEFAULT NULL)
19
20 Return VARCHAR2
21
22 IS
23 Ctx qa_ss_const.Ctx_Table;
24 p_category_id NUMBER;
25 p_category VARCHAR2(240);
26
27 BEGIN
28 -- dont think icx validate session is needed here, becos this is
29 -- a function called as part of wip's view definition
30 -- check the above
31
32 Ctx(qa_ss_const.Item) := P_Item_Number;
33 Ctx(qa_ss_const.Vendor_Name) := P_Supplier;
34 Ctx(qa_ss_const.Job_Name) := P_Wip_Entity_Name;
35 Ctx(qa_ss_const.Po_Number) := P_Po_Number;
36 Ctx(qa_ss_const.Vender_Item_Number) := P_Vendor_Item_Number;
37 -- Typo vender in seed data
38 Ctx(qa_ss_const.From_Op_Seq_Num) := to_char(P_Wip_Operation_Seq_Num);
39 Ctx(qa_ss_const.UOM_Name) := P_UOM_Name;
40 Ctx(qa_ss_const.Production_Line) := P_Production_Line;
41 Ctx(qa_ss_const.Ordered_Quantity) := to_char(P_Quantity_Ordered);
42 Ctx(qa_ss_const.Revision) := P_Item_Revision;
43 Ctx(qa_ss_const.PO_Release_Num) := to_char(P_Po_Release_Number);
44 Ctx(qa_ss_const.Organization_Id) := to_char(P_Organization_Id);
45 qa_ss_core.get_item_category_val(
46 p_org_id => p_organization_id,
47 p_item_val => p_item_number,
48 x_category_val => p_category,
49 x_category_id => p_category_id);
50 Ctx(qa_ss_const.item_category) := p_category;
51
52
53 -- P_Wip_entity_type can be used for any checking if needed
54
55
56
57 IF ( qa_ss_core.any_applicable_plans( Ctx, 100, P_Organization_Id))
58 THEN
59 return 'Y'; -- there are applicable plans
60 ELSE
61 return 'N'; -- No plans
62 End If;
63
64 -- argument 100 is txn_num
65 NULL;
66
67 END are_osp_plans_applicable;
68 --------------------------------------------------------------------------
69
70 procedure osp_to_quality (
71 PK1 IN VARCHAR2 DEFAULT NULL,
72 PK2 IN VARCHAR2 DEFAULT NULL,
73 PK3 IN VARCHAR2 DEFAULT NULL,
74 PK4 IN VARCHAR2 DEFAULT NULL,
75 PK5 IN VARCHAR2 DEFAULT NULL,
76 PK6 IN VARCHAR2 DEFAULT NULL,
77 PK7 IN VARCHAR2 DEFAULT NULL,
78 PK8 IN VARCHAR2 DEFAULT NULL,
79 PK9 IN VARCHAR2 DEFAULT NULL,
80 PK10 IN VARCHAR2 DEFAULT NULL,
81 c_outputs1 OUT NOCOPY VARCHAR2,
82 c_outputs2 OUT NOCOPY VARCHAR2,
83 c_outputs3 OUT NOCOPY VARCHAR2,
84 c_outputs4 OUT NOCOPY VARCHAR2,
85 c_outputs5 OUT NOCOPY VARCHAR2,
86 c_outputs6 OUT NOCOPY VARCHAR2,
87 c_outputs7 OUT NOCOPY VARCHAR2,
88 c_outputs8 OUT NOCOPY VARCHAR2,
89 c_outputs9 OUT NOCOPY VARCHAR2,
90 c_outputs10 OUT NOCOPY VARCHAR2)
91
92 IS
93
94
95 BEGIN
96
97 if (icx_sec.validateSession) then
98 qa_ss_core.plan_list_frames(100, pk1, pk2, pk3, pk4, pk5, pk6, pk7, pk8, pk9, pk10);
99 end if; -- end icx validate session
100
101 EXCEPTION
102 WHEN OTHERS THEN
103
104 htp.p('Exception in procedure osp_to_quality');
105 htp.p(SQLERRM);
106
107 END osp_to_quality;
108
109 ------------------------------------------------------------------------------------------
110
111 procedure default_osp_values (Ctx IN OUT NOCOPY qa_ss_const.Ctx_Table,
112 Txn_Num IN NUMBER DEFAULT NULL,
113 PK1 IN VARCHAR2 DEFAULT NULL,
114 PK2 IN VARCHAR2 DEFAULT NULL,
115 PK3 IN VARCHAR2 DEFAULT NULL,
116 PK4 IN VARCHAR2 DEFAULT NULL,
117 PK5 IN VARCHAR2 DEFAULT NULL,
118 PK6 IN VARCHAR2 DEFAULT NULL,
119 PK7 IN VARCHAR2 DEFAULT NULL,
120 PK8 IN VARCHAR2 DEFAULT NULL,
121 PK9 IN VARCHAR2 DEFAULT NULL,
122 PK10 IN VARCHAR2 DEFAULT NULL,
123 X_PO_AGENT_ID OUT NOCOPY NUMBER,
124 X_Item_Id OUT NOCOPY NUMBER,
125 X_PO_HEADER_ID OUT NOCOPY NUMBER,
126 X_Wip_Entity_Type OUT NOCOPY NUMBER,
127 X_Wip_Rep_Sch_Id OUT NOCOPY NUMBER,
128 X_Po_Release_Id OUT NOCOPY NUMBER,
129 X_Po_Line_Id OUT NOCOPY NUMBER,
130 X_Line_Location_Id OUT NOCOPY NUMBER,
131 X_Po_Distribution_Id OUT NOCOPY NUMBER,
132 X_Wip_Entity_Id OUT NOCOPY NUMBER,
133 X_Wip_Line_Id OUT NOCOPY NUMBER,
134 X_Po_Shipment_Id OUT NOCOPY NUMBER,
135 X_Organization_Id OUT NOCOPY NUMBER)
136
137 IS
138 l_po_header_id NUMBER := NULL;
139 l_po_release_id NUMBER := NULL;
140
141 -- Po Header Id and release id columns are added to the below cursor
142 -- only for the purpose of finding out po_agent_id
143 CURSOR def_osp_cur IS
144 SELECT Assembly_Item_Number, Vendor_Name, Wip_Entity_Name,
145 Base_Po_Num, Supplier_Item_Number,
146 Wip_Operation_Seq_Num, Assembly_Primary_UOM,
147 Wip_Line_code, Assembly_Quantity_Ordered,
148 Assembly_Item_Revision, Po_Release_Number,
149 Po_Header_Id, Po_Release_Id, Assembly_Item_Id,
150 Wip_Entity_Type, Wip_Repetitive_Schedule_Id,
151 Po_Line_Id, Line_Location_Id,
152 Po_Distribution_Id, Wip_Entity_Id,
153 Wip_Line_Id, Organization_Id
154 FROM WIP_ICX_OSP_WORKBENCH_V
155 Where Po_Distribution_Id = to_number(PK1);
156
157 CURSOR buyer1_cur(p_h_id IN NUMBER) IS
158 SELECT AGENT_ID
159 FROM PO_HEADERS_ALL
160 where po_header_id = p_h_id;
161
162 CURSOR buyer2_cur(p_rel_id IN NUMBER) IS
163 SELECT AGENT_ID
164 FROM PO_RELEASES_ALL
165 where PO_RELEASE_ID = p_rel_id;
166
167 -- R12 Project MOAC 4637896
168 -- Completely removed cursor operating_unit_cur
169
170 BEGIN
171
172 -- R12 Project MOAC 4637896
173 -- Completely removed set_org_context
174
175 -- dont need icx validate session here, becos this is already done
176 -- before this procedure is invoked, this is called starting from
177 -- draw_table procedure and thro the generic procedure to default values
178
179
180 -- only context element values are being selected
181 -- this stmt does not select ids like wip_entity_id and so on
182 -- the lov code should figure this
183 -- if there are problems then we can modify the architecture
184 -- to maybe pickout the ids here. test and see
185
186 -- View Name is WIP_ICX_OSP_WORKBENCH_V
187 -- Use base_po_num column from this view
188 -- TYPO "Vender"_Item_Number in seed data
189
190 OPEN def_osp_cur;
191
192 FETCH def_osp_cur
193 INTO Ctx(qa_ss_const.Item), Ctx(qa_ss_const.Vendor_Name), Ctx(qa_ss_const.Job_Name),
194 Ctx(qa_ss_const.Po_Number), Ctx(qa_ss_const.Vender_Item_Number),
195 Ctx(qa_ss_const.From_op_seq_num), Ctx(qa_ss_const.uom_name),
196 Ctx(qa_ss_const.Production_Line), Ctx(qa_ss_const.Ordered_Quantity),
197 Ctx(qa_ss_const.Revision), Ctx(qa_ss_const.Po_Release_Num),
198 l_po_header_id, l_po_release_id, X_Item_ID,
199 X_Wip_Entity_Type, X_Wip_Rep_Sch_Id,
200 X_Po_Line_Id, X_Line_Location_Id,
201 X_Po_Distribution_Id, X_Wip_Entity_ID,
202 X_Wip_Line_Id, X_Organization_Id ;
203
204 CLOSE def_osp_cur;
205 X_Po_Header_ID := l_po_header_id;
206 X_Po_Release_ID := l_po_release_id;
207 X_Po_Shipment_ID := NULL; -- not relevant in OSP
208
209 -- Adding code to find buyer ie. Po_Agent_Id
210 If (l_po_release_id is NOT NULL) Then
211 OPEN buyer2_cur(l_po_release_id);
212 FETCH buyer2_cur INTO X_PO_AGENT_ID;
213 CLOSE buyer2_cur;
214 Elsif (l_po_header_id is NOT NULL) Then
215 OPEN buyer1_cur(l_po_header_id);
216 FETCH buyer1_cur INTO X_PO_AGENT_ID;
217 CLOSE buyer1_cur;
218 Else
219 X_PO_AGENT_ID := NULL;
220 END IF; -- end buyer processing
221
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 IF def_osp_cur%ISOPEN THEN
226 CLOSE def_osp_cur;
227 End If;
228 IF buyer1_cur%ISOPEN THEN
229 CLOSE buyer1_cur;
230 End If;
231 IF buyer2_cur%ISOPEN THEN
232 CLOSE buyer2_cur;
233 End If;
234 htp.p('Exception in procedure default_osp_values');
235 htp.p(SQLERRM);
236
237 END default_osp_values;
238 ----------------------------------------------------------------------------------------------
239 -- Below procedure to be called from qa_ss_core.plan_list_frames
240 procedure osp_plans (
241 PK1 IN VARCHAR2 DEFAULT NULL,
242 PK2 IN VARCHAR2 DEFAULT NULL,
243 PK3 IN VARCHAR2 DEFAULT NULL,
244 PK4 IN VARCHAR2 DEFAULT NULL,
245 PK5 IN VARCHAR2 DEFAULT NULL,
246 PK6 IN VARCHAR2 DEFAULT NULL,
247 PK7 IN VARCHAR2 DEFAULT NULL,
248 PK8 IN VARCHAR2 DEFAULT NULL,
249 PK9 IN VARCHAR2 DEFAULT NULL,
250 PK10 IN VARCHAR2 DEFAULT NULL )
251
252 IS
253 P_Organization_Id NUMBER;
254 Ctx qa_ss_const.Ctx_Table;
255
256 CURSOR osp_cur IS
257 SELECT Assembly_Item_Number, Vendor_Name, Wip_Entity_Name,
258 Base_Po_Num, Supplier_Item_Number,
259 Wip_Operation_Seq_Num, Assembly_Primary_UOM,
260 Wip_Line_code, Assembly_Quantity_Ordered,
261 Assembly_Item_Revision, Po_Release_Number,
262 Organization_Id
263 FROM WIP_ICX_OSP_WORKBENCH_V
264 Where Po_Distribution_Id = to_number(PK1);
265 BEGIN
266
267 -- View Name is WIP_ICX_OSP_WORKBENCH_V
268 -- Use base_po_num column from this view
269 -- Typo "Vender"_Item_Number in seed data
270 if (icx_sec.validatesession) then
271 OPEN osp_cur;
272
273 FETCH osp_cur
274 INTO Ctx(qa_ss_const.Item), Ctx(qa_ss_const.Vendor_Name), Ctx(qa_ss_const.Job_Name),
275 Ctx(qa_ss_const.Po_Number), Ctx(qa_ss_const.Vender_Item_Number),
276 Ctx(qa_ss_const.From_op_seq_num), Ctx(qa_ss_const.uom_name),
277 Ctx(qa_ss_const.Production_Line), Ctx(qa_ss_const.Ordered_Quantity),
278 Ctx(qa_ss_const.Revision), Ctx(qa_ss_const.Po_Release_Num),
279 P_Organization_Id;
280
281 CLOSE osp_cur;
282 -- Now Ctx is populated. Also, populated P_Organization_Id above
283
284 qa_ss_core.all_applicable_plans( Ctx, 100, P_Organization_Id, PK1, PK2,Pk3,
285 Pk4,PK5,PK6,PK7,PK8,PK9,PK10);
286
287 -- This is all we do here. all_applicable_plans will call necessary procedures
288 -- to take care from here onwards
289
290 end if;-- end if for icx session
291 EXCEPTION
292 WHEN OTHERS THEN
293 IF osp_cur%ISOPEN THEN
294 CLOSE osp_cur;
295 End If;
296 htp.p('Exception in procedure qa_ss_osp.osp_plans');
297 htp.p(SQLERRM);
298 END osp_plans;
299 ----------------------------------------------------------------------------------------------
300
301 end qa_ss_osp;
302
303
[Home] [Help]