[Home] [Help]
PACKAGE BODY: APPS.CSP_PART_SEARCH_PVT_W
Source
1 package body csp_part_search_pvt_w as
2 /* $Header: cspvsrcwb.pls 120.0.12010000.2 2009/08/20 12:13:12 htank noship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5 rosetta_g_mistake_date_high date := to_date('01/01/+4710', 'MM/DD/SYYYY');
6 rosetta_g_mistake_date_low date := to_date('01/01/-4710', 'MM/DD/SYYYY');
7
8 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
9 function rosetta_g_miss_date_in_map(d date) return date as
10 begin
11 if d > rosetta_g_mistake_date_high then return fnd_api.g_miss_date; end if;
12 if d < rosetta_g_mistake_date_low then return fnd_api.g_miss_date; end if;
13 return d;
14 end;
15
16 procedure rosetta_table_copy_in_p2(t out nocopy csp_part_search_pvt.required_parts_tbl, a0 JTF_NUMBER_TABLE
17 , a1 JTF_VARCHAR2_TABLE_100
18 , a2 JTF_NUMBER_TABLE
19 ) as
20 ddindx binary_integer; indx binary_integer;
21 begin
22 if a0 is not null and a0.count > 0 then
23 if a0.count > 0 then
24 indx := a0.first;
25 ddindx := 1;
26 while true loop
27 t(ddindx).inventory_item_id := a0(indx);
28 t(ddindx).revision := a1(indx);
29 t(ddindx).quantity := a2(indx);
30 ddindx := ddindx+1;
31 if a0.last =indx
32 then exit;
33 end if;
34 indx := a0.next(indx);
35 end loop;
36 end if;
37 end if;
38 end rosetta_table_copy_in_p2;
39 procedure rosetta_table_copy_out_p2(t csp_part_search_pvt.required_parts_tbl, a0 out nocopy JTF_NUMBER_TABLE
40 , a1 out nocopy JTF_VARCHAR2_TABLE_100
41 , a2 out nocopy JTF_NUMBER_TABLE
42 ) as
43 ddindx binary_integer; indx binary_integer;
44 begin
45 if t is null or t.count = 0 then
46 a0 := JTF_NUMBER_TABLE();
47 a1 := JTF_VARCHAR2_TABLE_100();
48 a2 := JTF_NUMBER_TABLE();
49 else
50 a0 := JTF_NUMBER_TABLE();
51 a1 := JTF_VARCHAR2_TABLE_100();
52 a2 := JTF_NUMBER_TABLE();
53 if t.count > 0 then
54 a0.extend(t.count);
55 a1.extend(t.count);
56 a2.extend(t.count);
57 ddindx := t.first;
58 indx := 1;
59 while true loop
60 a0(indx) := t(ddindx).inventory_item_id;
61 a1(indx) := t(ddindx).revision;
62 a2(indx) := t(ddindx).quantity;
63 indx := indx+1;
64 if t.last =ddindx
65 then exit;
66 end if;
67 ddindx := t.next(ddindx);
68 end loop;
69 end if;
70 end if;
71 end rosetta_table_copy_out_p2;
72
73 procedure search(p0_a0 JTF_NUMBER_TABLE
74 , p0_a1 JTF_VARCHAR2_TABLE_100
75 , p0_a2 JTF_NUMBER_TABLE
76 , p1_a0 VARCHAR2
77 , p1_a1 NUMBER
78 , p1_a2 NUMBER
79 , p1_a3 NUMBER
80 , p1_a4 NUMBER
81 , p1_a5 NUMBER
82 , p1_a6 NUMBER
83 , p1_a7 VARCHAR2
84 , p1_a8 NUMBER
85 , p1_a9 DATE
86 , p1_a10 VARCHAR2
87 , p1_a11 NUMBER
88 , p1_a12 NUMBER
89 , p1_a13 VARCHAR2
90 , p1_a14 NUMBER
91 , p1_a15 VARCHAR2
92 , p1_a16 NUMBER
93 , p1_a17 NUMBER
94 , p1_a18 NUMBER
95 , p1_a19 NUMBER
96 , x_return_status out nocopy VARCHAR2
97 , x_msg_data out nocopy VARCHAR2
98 , x_msg_count out nocopy VARCHAR2
99 )
100
101 as
102 ddp_required_parts csp_part_search_pvt.required_parts_tbl;
103 ddp_search_params csp_part_search_pvt.search_params_rec;
104 ddindx binary_integer; indx binary_integer;
105 begin
106
107 -- copy data to the local IN or IN-OUT args, if any
108 csp_part_search_pvt_w.rosetta_table_copy_in_p2(ddp_required_parts, p0_a0
109 , p0_a1
110 , p0_a2
111 );
112
113 ddp_search_params.search_method := p1_a0;
114 if p1_a1 is null
115 then ddp_search_params.my_inventory := null;
116 elsif p1_a1 = 0
117 then ddp_search_params.my_inventory := false;
118 else ddp_search_params.my_inventory := true;
119 end if;
120 if p1_a2 is null
121 then ddp_search_params.technicians := null;
122 elsif p1_a2 = 0
123 then ddp_search_params.technicians := false;
124 else ddp_search_params.technicians := true;
125 end if;
126 if p1_a3 is null
127 then ddp_search_params.manned_warehouses := null;
128 elsif p1_a3 = 0
129 then ddp_search_params.manned_warehouses := false;
130 else ddp_search_params.manned_warehouses := true;
131 end if;
132 if p1_a4 is null
133 then ddp_search_params.unmanned_warehouses := null;
134 elsif p1_a4 = 0
135 then ddp_search_params.unmanned_warehouses := false;
136 else ddp_search_params.unmanned_warehouses := true;
137 end if;
138 if p1_a5 is null
139 then ddp_search_params.include_alternates := null;
140 elsif p1_a5 = 0
141 then ddp_search_params.include_alternates := false;
142 else ddp_search_params.include_alternates := true;
143 end if;
144 if p1_a6 is null
145 then ddp_search_params.include_closed := null;
146 elsif p1_a6 = 0
147 then ddp_search_params.include_closed := false;
148 else ddp_search_params.include_closed := true;
149 end if;
150 ddp_search_params.quantity_type := p1_a7;
151 if p1_a8 is null
152 then ddp_search_params.ship_set := null;
153 elsif p1_a8 = 0
154 then ddp_search_params.ship_set := false;
155 else ddp_search_params.ship_set := true;
156 end if;
157 ddp_search_params.need_by_date := rosetta_g_miss_date_in_map(p1_a9);
158 ddp_search_params.resource_type := p1_a10;
159 ddp_search_params.resource_id := p1_a11;
160 ddp_search_params.distance := p1_a12;
161 ddp_search_params.distance_uom := p1_a13;
162 ddp_search_params.source_organization_id := p1_a14;
163 ddp_search_params.source_subinventory := p1_a15;
164 ddp_search_params.to_location_id := p1_a16;
165 ddp_search_params.to_hz_location_id := p1_a17;
166 if p1_a18 is null
167 then ddp_search_params.current_location := null;
168 elsif p1_a18 = 0
169 then ddp_search_params.current_location := false;
170 else ddp_search_params.current_location := true;
171 end if;
172 ddp_search_params.requirement_header_id := p1_a19;
173
174
175
176
177 -- here's the delegated call to the old PL/SQL routine
178 csp_part_search_pvt.search(ddp_required_parts,
179 ddp_search_params,
180 x_return_status,
181 x_msg_data,
182 x_msg_count);
183
184 -- copy data back from the local variables to OUT or IN-OUT args, if any
185
186
187
188
189 end;
190
191 end csp_part_search_pvt_w;