[Home] [Help]
PACKAGE: APPS.INL_INTEGRATION_GRP
Source
1 PACKAGE INL_INTEGRATION_GRP AUTHID CURRENT_USER AS
2 /* $Header: INLGITGS.pls 120.31.12020000.2 2013/01/31 16:45:04 acferrei ship $ */
3
4 g_module_name VARCHAR2(100) := 'INL_INTEGRATION_GRP';
5 g_pkg_name CONSTANT VARCHAR2(30) := 'INL_INTEGRATION_GRP';
6
7 g_records_processed NUMBER := 0 ; --Bug#9279355
8 g_lines_processed VARCHAR2(2000) ; --Bug#9279355
9 g_records_inserted NUMBER := 0 ; --Bug#9279355
10 g_lines_inserted VARCHAR2(2000) ; --Bug#9279355
11
12 TYPE lci_rec IS RECORD (
13 shipment_header_id NUMBER,
14 transaction_type VARCHAR2(25),
15 processing_status_code VARCHAR2(25),
16 interface_source_code VARCHAR2(25),
17 hdr_interface_source_table VARCHAR2(30),
18 hdr_interface_source_line_id NUMBER,
19 validation_flag VARCHAR2(1),
20 receipt_num VARCHAR2(500),
21 ship_num VARCHAR2(25), --Bug#8971617
22 ship_date DATE,
23 ship_type_id NUMBER,
24 ship_type_code VARCHAR2(15),
25 --legal_entity_id NUMBER,
26 --legal_entity_name VARCHAR2(50),
27 organization_id NUMBER,
28 organization_code VARCHAR2(3),
29 location_id NUMBER,
30 location_code VARCHAR2(60),
31 --org_id NUMBER,
32 taxation_country VARCHAR2(30),
33 document_sub_type VARCHAR2(150),
34 ship_header_id NUMBER,
35 last_task_code VARCHAR2(25),
36 ship_line_group_reference VARCHAR2(30),
37 party_id NUMBER,
38 party_number VARCHAR2(30),
39 party_site_id NUMBER,
40 party_site_number VARCHAR2(30),
41 source_organization_id NUMBER,
42 source_organization_code VARCHAR2(3),
43 ship_line_num NUMBER,
44 ship_line_type_id NUMBER,
45 ship_line_type_code VARCHAR2(15),
46 ship_line_src_type_code VARCHAR2(30),
47 ship_line_source_id NUMBER,
48 currency_code VARCHAR2(15),
49 currency_conversion_type VARCHAR2(30),
50 currency_conversion_date DATE,
51 currency_conversion_rate NUMBER,
55 txn_unit_price NUMBER,
52 inventory_item_id NUMBER,
53 txn_qty NUMBER,
54 txn_uom_code VARCHAR2(3),
56 primary_qty NUMBER,
57 primary_uom_code VARCHAR2(3),
58 primary_unit_price NUMBER,
59 secondary_qty NUMBER,
60 secondary_uom_code VARCHAR2(3),
61 secondary_unit_price NUMBER,
62 landed_cost_flag VARCHAR2(1),
63 allocation_enabled_flag VARCHAR2(1),
64 trx_business_category VARCHAR2(240),
65 intended_use VARCHAR2(30),
66 product_fiscal_class VARCHAR2(240),
67 product_category VARCHAR2(240),
68 product_type VARCHAR2(240),
69 user_def_fiscal_class VARCHAR2(240),
70 tax_classification_code VARCHAR2(30),
71 assessable_value NUMBER,
72 ship_from_party_id NUMBER,
73 ship_from_party_number VARCHAR2(30),
74 ship_from_party_site_id NUMBER,
75 ship_from_party_site_number VARCHAR2(30),
76 ship_to_organization_id NUMBER,
77 ship_to_organization_code VARCHAR2(3),
78 ship_to_location_id NUMBER,
79 ship_to_location_code VARCHAR2(60),
80 bill_from_party_id NUMBER,
81 bill_from_party_number VARCHAR2(30),
82 bill_from_party_site_id NUMBER,
83 bill_from_party_site_number VARCHAR2(30),
84 bill_to_organization_id NUMBER,
85 bill_to_organization_code VARCHAR2(3),
86 bill_to_location_id NUMBER,
87 bill_to_location_code VARCHAR2(60),
88 poa_party_id NUMBER,
89 poa_party_number VARCHAR2(30),
90 poa_party_site_id NUMBER,
91 poa_party_site_number VARCHAR2(30),
92 poo_organization_id NUMBER,
93 poo_to_organization_code VARCHAR2(3),
94 poo_location_id NUMBER,
95 poo_location_code VARCHAR2(60),
96 ship_line_id NUMBER,
97 line_interface_source_table VARCHAR2(30),
98 line_interface_source_line_id NUMBER,
99 header_interface_id NUMBER,
100 rcv_enabled_flag VARCHAR2(1), --Bug#9279355
101 group_id NUMBER --Bug#9279355
102 );
103
104 TYPE lci_table IS TABLE OF lci_rec INDEX BY BINARY_INTEGER;
105
106 CURSOR c_ship_lines(l_ship_header_id IN NUMBER,
107 l_src_type_code IN VARCHAR2,
108 l_interface_source_table IN VARCHAR2) IS
109 SELECT sl.ship_line_id,
110 sl.ship_line_num,
111 sl.ship_line_source_id,
112 sl.inventory_item_id,
113 sl.txn_qty,
114 sl.txn_uom_code,
115 sl.primary_qty,
116 sl.primary_uom_code,
117 sl.secondary_qty, -- Bug 8911750
118 sl.secondary_uom_code, -- Bug 8911750
119 sl.currency_code,
120 sl.currency_conversion_type,
121 sl.currency_conversion_date,
122 sl.currency_conversion_rate,
123 slg.party_id,
124 slg.party_site_id,
125 slg.src_type_code,
126 slg.ship_line_group_id,
127 sh.organization_id,
128 sh.location_id,
129 sh.org_id,
130 msi.description AS item_description,
131 msi.segment1 AS item,
132 sh.interface_source_code,
133 sl.interface_source_table,
134 sl.interface_source_line_id,
135 lc.unit_landed_cost
136 FROM inl_ship_lines sl,
137 inl_ship_line_groups slg,
138 inl_ship_headers sh,
139 inl_shipln_landed_costs_v lc,
140 mtl_system_items msi
141 WHERE msi.inventory_item_id = sl.inventory_item_id
142 AND msi.organization_id = sh.organization_id
143 AND sl.ship_header_id = slg.ship_header_id
144 AND sl.ship_line_group_id = slg.ship_line_group_id
145 AND slg.ship_header_id = sh.ship_header_id
146 AND lc.ship_line_id = sl.ship_line_id
147 AND sh.ship_header_id = l_ship_header_id
148 AND (slg.src_type_code = l_src_type_code
149 OR l_src_type_code IS NULL)
150 AND (sl.interface_source_table = l_interface_source_table
151 OR l_interface_source_table IS NULL)
152 ORDER BY slg.ship_line_group_id, sl.ship_line_num; --open source
153
154 TYPE ship_lines_table IS TABLE OF c_ship_lines%ROWTYPE;
155
156 -- Bug #9279355
157 TYPE po_hdr_rec IS RECORD(
158 po_header_id NUMBER,
159 po_release_id NUMBER, -- Bug 14280113
160 segment1 VARCHAR2(20),
161 vendor_id NUMBER,
162 vendor_site_id NUMBER,
163 ship_via_lookup_code VARCHAR2(25),
164 currency_code VARCHAR2(15),
165 rate_type VARCHAR2(30),
166 rate_date DATE,
167 rate NUMBER,
168 revision_num NUMBER,
169 approved_date DATE,
170 org_id NUMBER,
171 simulation_id NUMBER);
172
173 PROCEDURE Import_FromRCV (
174 p_int_rec IN RCV_CALL_LCM_WS.rti_rec,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2
178 );
179
180 PROCEDURE Get_LandedCost(
181 p_rti_rec IN RCV_LCM_WEB_SERVICE.rti_cur_table,
182 p_group_id IN NUMBER,
183 p_processing_mode IN VARCHAR2
184 );
185
186 PROCEDURE Call_StampLC (
187 p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
189 p_commit IN VARCHAR2 := FND_API.G_FALSE,
190 p_ship_header_id IN NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2
194 );
195
196 PROCEDURE Export_ToRCV (
197 p_api_version IN NUMBER,
198 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
199 p_commit IN VARCHAR2 := FND_API.G_FALSE,
200 p_ship_header_id IN NUMBER,
201 x_return_status OUT NOCOPY VARCHAR2,
202 x_msg_count OUT NOCOPY NUMBER,
203 x_msg_data OUT NOCOPY VARCHAR2
204 );
205
206 PROCEDURE Export_ToCST (
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
209 p_commit IN VARCHAR2 := FND_API.G_FALSE,
210 p_ship_header_id IN NUMBER,
211 p_max_allocation_id IN NUMBER, --Bug#10032820
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 );
216
217 PROCEDURE Insert_LCMInterface (
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
220 p_commit IN VARCHAR2 := FND_API.G_FALSE,
221 p_lci_table IN OUT NOCOPY lci_table,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2);
225
226 PROCEDURE Get_CurrencyInfo(
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
229 p_commit IN VARCHAR2 := FND_API.G_FALSE,
230 p_ship_line_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2,
234 x_currency_code OUT NOCOPY VARCHAR2,
235 x_currency_conversion_type OUT NOCOPY VARCHAR2,
236 x_currency_conversion_date OUT NOCOPY DATE,
237 x_currency_conversion_rate OUT NOCOPY NUMBER);
238
239 PROCEDURE Create_POSimulation(
240 p_api_version IN NUMBER,
241 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
242 p_commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_po_header_id IN NUMBER,
244 p_po_release_id IN NUMBER, -- Bug 14280113
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2);
248
249
250 FUNCTION Get_ExtPrecFormatMask(
251 p_currency_code IN VARCHAR2,
252 p_field_length IN NUMBER
253 )
254 return VARCHAR2;
255
256 FUNCTION Check_POLcmSynch (
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
260 p_simulation_id IN NUMBER,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2)RETURN VARCHAR2;
264
265 FUNCTION Check_POEligibility(p_po_header_id IN NUMBER) RETURN VARCHAR2;
266
267 END INL_INTEGRATION_GRP;