1 PACKAGE BODY PO_R12_CAT_UPG_DEF_PVT AS
2 /* $Header: PO_R12_CAT_UPG_DEF_PVT.plb 120.7 2006/08/12 00:13:49 pthapliy noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_DEF_PVT';
8 g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
5 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6
7 g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
9
10 -- BEGIN: Forward function declarations
11
12 PROCEDURE default_info_from_vendor
13 (
14 p_key IN NUMBER,
15 p_headers_rec IN PO_R12_CAT_UPG_PVT.record_of_headers_type,
16 x_invoice_currency_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15,
17 x_terms_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
18 );
19
20 PROCEDURE default_vendor_sites
21 (
22 p_key IN NUMBER,
23 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
24 );
25
26 PROCEDURE default_info_from_vendor_site
27 (
28 p_key IN NUMBER,
29 p_headers_rec IN PO_R12_CAT_UPG_PVT.record_of_headers_type,
30 x_fob_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
31 x_ship_via_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
32 x_freight_terms_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
33 x_ship_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
34 x_bill_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
35 x_invoice_currency_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15,
36 x_terms_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
37 x_pay_on_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
38 x_shipping_controls OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30
39 );
40
41 PROCEDURE default_vendor_contact_info
42 (
43 p_key IN NUMBER,
44 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
45 );
46
47 PROCEDURE default_buyer
48 (
49 p_key IN NUMBER
50 , x_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
51 );
52
53 -- END: Forward function declarations
54
55 --------------------------------------------------------------------------------
56 --Start of Comments
57 --Name: default_headers
58 --Pre-reqs:
59 -- The iP catalog data is populated in input pl/sql tables.
60 --Modifies:
61 -- a) Input pl/sql table: Defaults a value in some columns.
62 -- b) FND_MSG_PUB on unhandled exceptions.
63 --Locks:
64 -- None.
65 --Function:
66 -- Defaults the header level column values, if no value is given in the
67 -- interface tables.
68 -- This API should be called during the upgrade phase only.
69 --Parameters:
70 --IN OUT:
71 -- p_headers_rec
72 -- A record of plsql tables containing a batch of headers.
73 --OUT:
74 --End of Comments
75 --------------------------------------------------------------------------------
76 PROCEDURE default_headers
77 (
78 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
79 )
80 IS
81 l_api_name CONSTANT VARCHAR2(30) := 'default_headers';
82 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
83 l_progress VARCHAR2(3) := '000';
84
85 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
86
87 l_vendor_site_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
88 l_vendor_currency_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
89 l_vendor_terms_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
90
91 l_site_fob_lookup_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
92 l_site_ship_via_lookup_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
93 l_site_freight_terms_luc PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
94 l_site_ship_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
95 l_site_bill_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
96 l_site_currency_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
97 l_site_terms_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
98 l_site_shipping_controls PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
99 l_site_pay_on_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
100
101 l_vendor_contact_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
102 l_is_fixed_rate VARCHAR2(10);
103
104 l_key PO_SESSION_GT.key%TYPE;
105 i NUMBER;
106 rate NUMBER;
107 BEGIN
108 l_progress := '010';
109 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
110
111 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.interface_header_id.COUNT='||p_headers_rec.interface_header_id.COUNT); END IF;
112
113 -- pick a new key from temp table which will be used in all default logic
114 --l_key := PO_CORE_S.get_session_gt_nextval;
115 SELECT PO_SESSION_GT_S.nextval
116 INTO l_key
117 FROM DUAL;
118
119 l_progress := '020';
120 -- default information from vendor
121 default_info_from_vendor
122 (
123 p_key => l_key,
124 p_headers_rec => p_headers_rec,
125 x_invoice_currency_codes => l_vendor_currency_codes,
126 x_terms_ids => l_vendor_terms_ids
127 );
128
129 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.interface_header_id.COUNT='||p_headers_rec.interface_header_id.COUNT); END IF;
130
131 l_progress := '030';
132 -- Default Vendor Site, if only 1 site exist for the vendor
133 -- The vendor site id will be populated in the p_header_rec
134 default_vendor_sites
135 (
136 p_key => l_key, -- IN
137 p_headers_rec => p_headers_rec -- IN OUT
138 );
139
140 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.interface_header_id.COUNT='||p_headers_rec.interface_header_id.COUNT); END IF;
141
142 l_progress := '040';
143 -- default information from vendor site
144 default_info_from_vendor_site
145 (
146 p_key => l_key,
147 p_headers_rec => p_headers_rec,
148 x_fob_lookup_codes => l_site_fob_lookup_codes,
149 x_ship_via_lookup_codes => l_site_ship_via_lookup_codes,
150 x_freight_terms_lookup_codes => l_site_freight_terms_luc,
151 x_ship_to_location_ids => l_site_ship_to_location_ids,
152 x_bill_to_location_ids => l_site_bill_to_location_ids,
153 x_invoice_currency_codes => l_site_currency_codes,
154 x_terms_ids => l_site_terms_ids,
155 x_pay_on_codes => l_site_pay_on_codes,
156 x_shipping_controls => l_site_shipping_controls
157 );
158
159 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.interface_header_id.COUNT='||p_headers_rec.interface_header_id.COUNT); END IF;
160
161 l_progress := '050';
162 -- default vendor contact information
163 default_vendor_contact_info
164 (
165 p_key => l_key, -- IN
166 p_headers_rec => p_headers_rec -- IN OUT
167 );
168
169 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.interface_header_id.COUNT='||p_headers_rec.interface_header_id.COUNT); END IF;
170
171 l_progress := '055';
172 -- default buyer
173 default_buyer
174 (
175 p_key => l_key, -- IN
176 x_headers_rec => p_headers_rec -- IN OUT
177 );
178
179 l_progress := '060';
180 -- set default value on a row base
181 FOR i IN 1 .. p_headers_rec.vendor_id.COUNT
182 LOOP
183 l_progress := '070';
184 IF (--p_headers_rec.has_errors(i) = 'Y' OR
185 p_headers_rec.action(i) <> PO_R12_CAT_UPG_PVT.g_action_header_create) THEN
186 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Skipping rec#'||i||' has_errors='||p_headers_rec.has_errors(i)||'action='||p_headers_rec.action(i)); END IF;
187 goto END_OF_HEADERS_LOOP;
188 END IF;
189
190 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'i='||i); END IF;
191
192 --p_headers_rec.po_header_id(i) := NULL; -- From sequence PO_HEADERS_S
193
194 l_progress := '071';
195
196 --p_headers_rec.agent_id(i) -- TODO: Open issue with PM
197 p_headers_rec.document_type_code(i) := 'PO'; -- Only present in interface table, not in txn table
198 p_headers_rec.document_subtype(i) := 'BLANKET'; -- TYPE_LOOKUP_CODE in txn tables
199 p_headers_rec.last_update_date(i) := sysdate; -- who column
200 p_headers_rec.last_updated_by(i) := FND_GLOBAL.user_id; -- who column
201
202 l_progress := '072';
203 --p_headers_rec.summary_flag(i) := 'N'; -- Key flexfield related, for future use. Not present in interface tables
204 --p_headers_rec.enabled_flag(i) := 'Y'; -- Key flexfield related, for future use. Not present in interface tables
205 --p_headers_rec.segment1(i) := NULL; -- will be populated in the end: Auto: From seq Manual: Open issue
206 --p_headers_rec.segment2(i) := NULL; Not present in interface tables
207 --p_headers_rec.segment3(i) := NULL;
208 --p_headers_rec.segment4(i) := NULL;
209 --p_headers_rec.segment5(i) := NULL;
210 --p_headers_rec.start_date_active(i) := NULL; -- Key Flexfield start date. Not present in interface tables
211 --p_headers_rec.end_date_active(i) := NULL; -- Key Flexfield start date. Not present in interface tables
212 p_headers_rec.last_update_login(i) := FND_GLOBAL.login_id; -- who column
213 p_headers_rec.creation_date(i) := sysdate; -- who column
214 p_headers_rec.created_by(i) := PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER; -- -12
215
216 l_progress := '073';
217 --p_headers_rec.vendor_id(i) -- Copy value from interface table
218 --p_headers_rec.vendor_site_id(i) -- Copy value from interface table. If NULL, default as in PDOI
219 --p_headers_rec.vendor_contact_id(i) -- Default as in PDOI (iP will not provide)
220 --p_headers_rec.ship_to_location_id(i) -- Default as in PDOI (iP will not provide)
221 --p_headers_rec.bill_to_location_id(i) -- Default as in PDOI (iP will not provide)
225 --p_headers_rec.freight_terms_lookup_code(i) -- Default as in PDOI (iP will not provide)
222 --p_headers_rec.terms_id(i) -- Default as in PDOI (iP will not provide)
223 --p_headers_rec.ship_via_lookup_code(i) -- Default as in PDOI (iP will not provide)
224 --p_headers_rec.fob_lookup_code(i) -- Default as in PDOI (iP will not provide)
226 --p_headers_rec.status_lookup_code(i) := NULL; -- Only used for Quotations. Not present in interface tables
227 --p_headers_rec.currency_code(i) -- Copy value from interface table. If NULL, default as in PDOI
228 --p_headers_rec.rate_type(i) -- Default as in PDOI (iP will not provide)
229 --p_headers_rec.rate_date(i) -- Default as in PDOI (iP will not provide)
230 --p_headers_rec.rate(i) -- Default as in PDOI (iP will not provide)
231 p_headers_rec.from_header_id(i) := NULL;
232 p_headers_rec.from_type_lookup_code(i) := NULL;
233 --p_headers_rec.start_date(i) -- Copy value from interface table
234 --p_headers_rec.end_date(i) -- Copy value from interface table
235 --p_headers_rec.blanket_total_amount(i) -- Copy value from interface table
236 p_headers_rec.approval_status(i) := 'IN PROCESS'; -- AUTHORIZATION_STATUS in txn tables
237 p_headers_rec.revision_num(i) := 0;
238 p_headers_rec.revised_date(i) := sysdate;
239 --p_headers_rec.approved_flag(i) := 'N'; Not present in interface tables
240 p_headers_rec.approved_date(i) := NULL;
241 --p_headers_rec.amount_limit(i) -- Copy value from interface table
242 --p_headers_rec.min_release_amount(i) -- Copy value from interface table
243 --p_headers_rec.note_to_authorizer(i) := NULL; -- Not present in interface tables
244 p_headers_rec.note_to_vendor(i) := NULL;
245 p_headers_rec.note_to_receiver(i) := NULL;
246 p_headers_rec.print_count(i) := 0;
247 p_headers_rec.printed_date(i) := NULL;
248
249 l_progress := '074';
250 --p_headers_rec.vendor_order_num(i) := NULL; Not present in interface tables
251 p_headers_rec.confirming_order_flag(i) := 'N';
252 --p_headers_rec.comments(i) := NULL;
253 p_headers_rec.reply_date(i) := NULL;
254 --p_headers_rec.reply_method_lookup_code(i) := NULL; Not present in interface tables
255 p_headers_rec.rfq_close_date(i) := NULL;
256 --p_headers_rec.quote_type_lookup_code(i) := NULL; Not present in interface tables
257 --p_headers_rec.quotation_class_code(i) := NULL; Not present in interface tables
258 --p_headers_rec.quote_warning_delay_unit(i) := NULL; Not present in interface tables
259 p_headers_rec.quote_warning_delay(i) := NULL;
260 --p_headers_rec.quote_vendor_quote_number(i) := NULL; Not present in interface tables
261 p_headers_rec.acceptance_required_flag(i) := 'N';
262 p_headers_rec.acceptance_due_date(i) := NULL;
263 p_headers_rec.closed_date(i) := NULL;
264 --p_headers_rec.user_hold_flag(i) := NULL; Not present in interface tables
265 p_headers_rec.approval_required_flag(i) := NULL;
266 --p_headers_rec.cancel_flag(i) := 'N'; Not present in interface tables
267 --p_headers_rec.firm_status_lookup_code(i) := 'N'; Not present in interface tables
268 --p_headers_rec.firm_date(i) := NULL; Not present in interface tables
269 p_headers_rec.frozen_flag(i) := 'N';
270
271 l_progress := '075';
272 p_headers_rec.attribute_category(i) := NULL;
273 p_headers_rec.attribute1(i) := NULL;
274 p_headers_rec.attribute2(i) := NULL;
275 p_headers_rec.attribute3(i) := NULL;
276 p_headers_rec.attribute4(i) := NULL;
277 p_headers_rec.attribute5(i) := NULL;
278 p_headers_rec.attribute6(i) := NULL;
279 p_headers_rec.attribute7(i) := NULL;
280 p_headers_rec.attribute8(i) := NULL;
281 p_headers_rec.attribute9(i) := NULL;
282 p_headers_rec.attribute10(i) := NULL;
283 p_headers_rec.attribute11(i) := NULL;
284 p_headers_rec.attribute12(i) := NULL;
285 p_headers_rec.attribute13(i) := NULL;
286 p_headers_rec.attribute14(i) := NULL;
287 p_headers_rec.attribute15(i) := NULL;
288 p_headers_rec.closed_code(i) := NULL;
289 p_headers_rec.ussgl_transaction_code(i) := NULL;
290 l_progress := '076';
291
292 --p_headers_rec.government_context(i) := NULL; Not present in interface tables
293 p_headers_rec.request_id(i) := FND_GLOBAL.conc_request_id; -- NUMBER iPs conc program request id
294 p_headers_rec.program_application_id(i) := FND_GLOBAL.prog_appl_id; -- NUMBER
295 p_headers_rec.program_id(i) := FND_GLOBAL.conc_program_id; -- NUMBER
296 p_headers_rec.program_update_date(i) := sysdate; -- DATE
297
298 l_progress := '077';
299 --p_headers_rec.org_id(i) -- Copy value from interface table.
300 --p_headers_rec.supply_agreement_flag(i) := 'N'; Not present in interface tables
301 --p_headers_rec.edi_processed_flag(i) := NULL; Not present in interface tables
302 --p_headers_rec.edi_processed_status(i) := NULL; Not present in interface tables
303 --p_headers_rec.global_attribute_category(i) := NULL; Not present in interface tables
304 --p_headers_rec.global_attribute1(i) := NULL; Not present in interface tables
308 --p_headers_rec.global_attribute5(i) := NULL; Not present in interface tables
305 --p_headers_rec.global_attribute2(i) := NULL; Not present in interface tables
306 --p_headers_rec.global_attribute3(i) := NULL; Not present in interface tables
307 --p_headers_rec.global_attribute4(i) := NULL; Not present in interface tables
309 --p_headers_rec.global_attribute6(i) := NULL; Not present in interface tables
310 --p_headers_rec.global_attribute7(i) := NULL; Not present in interface tables
311 --p_headers_rec.global_attribute8(i) := NULL; Not present in interface tables
312 --p_headers_rec.global_attribute9(i) := NULL; Not present in interface tables
313 --p_headers_rec.global_attribute10(i) := NULL; Not present in interface tables
314 --p_headers_rec.global_attribute11(i) := NULL; Not present in interface tables
315 --p_headers_rec.global_attribute12(i) := NULL; Not present in interface tables
316 --p_headers_rec.global_attribute13(i) := NULL; Not present in interface tables
317 --p_headers_rec.global_attribute14(i) := NULL; Not present in interface tables
318 --p_headers_rec.global_attribute15(i) := NULL; Not present in interface tables
319 --p_headers_rec.global_attribute16(i) := NULL; Not present in interface tables
320 --p_headers_rec.global_attribute17(i) := NULL; Not present in interface tables
321 --p_headers_rec.global_attribute18(i) := NULL; Not present in interface tables
322 --p_headers_rec.global_attribute19(i) := NULL; Not present in interface tables
323 --p_headers_rec.global_attribute20(i) := NULL; Not present in interface tables
324 p_headers_rec.interface_source_code(i) := NULL;
325 p_headers_rec.reference_num(i) := NULL;
326 --p_headers_rec.wf_item_type(i) := NULL; Not present in interface tables
327 --p_headers_rec.wf_item_key(i) := NULL; Not present in interface tables
328 --p_headers_rec.mrc_rate_type(i) := NULL; Not present in interface tables
329 --p_headers_rec.mrc_rate_date(i) := NULL; Not present in interface tables
330 --p_headers_rec.mrc_rate(i) := NULL; Not present in interface tables
331 p_headers_rec.pcard_id(i) := NULL;
332
333 l_progress := '078';
334 --p_headers_rec.price_update_tolerance(i) := NULL; Not present in interface tables
335 --p_headers_rec.pay_on_code(i) -- Default as in PDOI (iP will not provide)
336 --p_headers_rec.xml_flag(i) := NULL; Not present in interface tables
337 --p_headers_rec.xml_send_date(i) := NULL; Not present in interface tables
338 --p_headers_rec.xml_change_send_date(i) := NULL; Not present in interface tables
339 p_headers_rec.global_agreement_flag(i) := 'Y';
340 --p_headers_rec.consigned_consumption_flag(i) := NULL; Not present in interface tables
341 --p_headers_rec.cbc_accounting_date(i) := NULL; Not present in interface tables
342 p_headers_rec.consume_req_demand_flag(i) := NULL;
343 --p_headers_rec.change_requested_by(i) := NULL; Not present in interface tables
344 --p_headers_rec.shipping_control(i) -- Default as in PDOI (iP will not provide)
345 --p_headers_rec.conterms_exist_flag(i) := NULL; Not present in interface tables
346 --p_headers_rec.conterms_articles_upd_date(i) := NULL; Not present in interface tables
347 --p_headers_rec.conterms_deliv_upd_date(i) := NULL; Not present in interface tables
348 --p_headers_rec.pending_signature_flag(i) := NULL; Not present in interface tables
349
350 --p_headers_rec.change_summary(i) := NULL;-- Not present in 11.5.9.
351 --p_headers_rec.encumbrance_required_flag(i) := NULL;-- Not present in 11.5.9.
352
353 l_progress := '079';
354 --p_headers_rec.document_creation_method(i) := NULL; -- Not present in 11.5.9. For 11.5.10, default CATALOG_MIGRATION (Open issue) Not present in interface tables
355 --p_headers_rec.submit_date(i) := NULL; -- Not present in 11.5.9. For 11.5.10, default NULL (Open issue) Not present in interface tables
356 --p_headers_rec.supplier_notif_method(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
357 --p_headers_rec.fax(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
358 --p_headers_rec.email_address(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
359 --p_headers_rec.retro_price_comm_updates_flag(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
360 --p_headers_rec.retro_price_apply_updates_flag(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
361 --p_headers_rec.update_sourcing_rules_flag(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
362 --p_headers_rec.auto_sourcing_flag(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
363 --p_headers_rec.created_language(i) -- Copy value from interface table.
364 --p_headers_rec.cpa_reference(i) -- Copy value from interface table.
365 --p_headers_rec.ip_category_id(i) -- Copy value from interface table.
366 --p_headers_rec.last_updated_program(i) := 'CATALOG_MIGRATION'; -- TODO: Confirm with Sareddy. Not present in interface tables
367 --p_headers_rec.style_id(i) := NULL; -- Not present in 11.5.9, 11.5.10 Not present in interface tables
368
369 l_progress := '080';
370 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_sys.fob_lookup_code='||PO_R12_CAT_UPG_PVT.g_sys.fob_lookup_code); END IF;
371 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_site_fob_lookup_codes(i)='||l_site_fob_lookup_codes(i)); END IF;
375 NVL(p_headers_rec.fob(i),
372 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(i)='||p_headers_rec.fob(i)); END IF;
373
374 p_headers_rec.fob(i) :=
376 NVL(l_site_fob_lookup_codes(i),
377 PO_R12_CAT_UPG_PVT.g_sys.fob_lookup_code));
378
379 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(i)='||p_headers_rec.fob(i)); END IF;
380
381 l_progress := '082';
382 -- default freight_carrier(ship_via_lookup_code)
383 p_headers_rec.freight_carrier(i) :=
384 NVL(p_headers_rec.freight_carrier(i),
385 NVL(l_site_ship_via_lookup_codes(i),
386 PO_R12_CAT_UPG_PVT.g_sys.ship_via_lookup_code));
387
388 l_progress := '083';
389 -- default freight_terms
390 p_headers_rec.freight_terms(i) :=
391 NVL(p_headers_rec.freight_terms(i),
392 NVL(l_site_freight_terms_luc(i),
393 PO_R12_CAT_UPG_PVT.g_sys.freight_terms_lookup_code));
394
395 l_progress := '084';
396 -- default ship_to_location_id
397 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_sys.ship_to_location_id='||PO_R12_CAT_UPG_PVT.g_sys.ship_to_location_id); END IF;
398 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_site_ship_to_location_ids(i)='||l_site_ship_to_location_ids(i)); END IF;
399 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(i)='||p_headers_rec.ship_to_location_id(i)); END IF;
400
401 p_headers_rec.ship_to_location_id(i) :=
402 NVL(p_headers_rec.ship_to_location_id(i),
403 NVL(l_site_ship_to_location_ids(i),
404 PO_R12_CAT_UPG_PVT.g_sys.ship_to_location_id));
405
406 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(i)=<'||p_headers_rec.ship_to_location_id(i)||'>'); END IF;
407
408 l_progress := '085';
409 -- default bill_to_location_id
410 p_headers_rec.bill_to_location_id(i) :=
411 NVL(p_headers_rec.bill_to_location_id(i),
412 NVL(l_site_bill_to_location_ids(i),
413 PO_R12_CAT_UPG_PVT.g_sys.bill_to_location_id));
414
415 l_progress := '086';
416 -- default currency_code
417 p_headers_rec.currency_code(i) :=
418 NVL(p_headers_rec.currency_code(i),
419 NVL(l_site_currency_codes(i),
420 NVL(l_vendor_currency_codes(i),
421 PO_R12_CAT_UPG_PVT.g_sys.currency_code)));
422
423 l_progress := '087';
424 -- default terms_id
425 p_headers_rec.terms_id(i) :=
426 NVL(p_headers_rec.terms_id(i),
427 NVL(l_site_terms_ids(i),
428 l_vendor_terms_ids(i)));
429
430 l_progress := '088';
431 -- default shipping_control
432 p_headers_rec.shipping_control(i) :=
433 NVL(p_headers_rec.shipping_control(i),
434 l_site_shipping_controls(i));
435
436 l_progress := '089';
437 -- default pay_on_code
438 p_headers_rec.pay_on_code(i) := l_site_pay_on_codes(i);
439
440 l_progress := '090';
441 -- default rate info after currency default
442 IF (p_headers_rec.currency_code(i) <> PO_R12_CAT_UPG_PVT.g_sys.currency_code)
443 THEN
444 l_progress := '100';
445 -- deafult rate_date
446 p_headers_rec.rate_date(i) := sysdate;
447
448 l_progress := '110';
449 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Calling GL_CURRENCY_API.is_fixed_rate()'); END IF;
450 -- default rate_type
451 l_is_fixed_rate := 'N';
452 BEGIN
453 l_is_fixed_rate := GL_CURRENCY_API.is_fixed_rate
454 (
455 x_from_currency => p_headers_rec.currency_code(i),
456 x_to_currency => PO_R12_CAT_UPG_PVT.g_sys.currency_code,
457 x_effective_date => p_headers_rec.rate_date(i)
458 );
459 EXCEPTION
460 WHEN OTHERS THEN
461 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception from GL_CURRENCY_API.is_fixed_rate(): '|| SQLERRM(SQLCODE)); END IF;
462 -- Mark this record as errored and continue. Do not raise the exception
463 -- because we want to procss as many records as possible.
464 p_headers_rec.has_errors(i) := 'Y';
465
466 -- Add error message into INTERFACE_ERRORS table
467 -- ICX_CAT_ERR_IN_GL_CURR_API
468 -- "An error occurred in the call to API_NAME while retrieving the currency conversion rate."
469 PO_R12_CAT_UPG_UTL.add_fatal_error(
470 p_interface_header_id => p_headers_rec.interface_header_id(i),
471 --p_error_message_name => 'PO_CAT_UPG_GL_API1_EXCEPTION',
472 p_error_message_name => 'ICX_CAT_ERR_IN_GL_CURR_API',
473 p_table_name => 'PO_HEADERS_INTERFACE',
474 p_column_name => 'API_NAME',
475 p_column_value => 'GL_CURRENCY_API.is_fixed_rate',
476 p_token1_name => 'API_NAME',
477 p_token1_value => 'GL_CURRENCY_API.is_fixed_rate'
478 );
479 END;
480
481 IF (p_headers_rec.has_errors(i) <> 'Y' AND
482 l_is_fixed_rate = 'Y') THEN
483 p_headers_rec.rate_type_code(i) := 'EMU FIXED';
484 ELSE
485 p_headers_rec.rate_type_code(i) :=
486 NVL(p_headers_rec.rate_type_code(i),
487 PO_R12_CAT_UPG_PVT.g_sys.default_rate_type);
488 END IF;
489
490 l_progress := '120';
491 -- default rate
492 IF (p_headers_rec.has_errors(i) <> 'Y' AND
493 (p_headers_rec.rate(i) IS NULL OR
494 p_headers_rec.rate_type_code(i) = 'EMU FIXED')) THEN
495 l_progress := '130';
496 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Calling GL_CURRENCY_API.get_rate()'); END IF;
497 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_set_of_books_id='||PO_R12_CAT_UPG_PVT.g_sys.sob_id); END IF;
498 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_from_currency='||p_headers_rec.currency_code(i)); END IF;
499 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_conversion_date='||p_headers_rec.rate_date(i)); END IF;
500 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_conversion_type='||p_headers_rec.rate_type_code(i)); END IF;
501
502 BEGIN
503 rate := NULL; -- Bug 5461235
504 rate := GL_CURRENCY_API.get_rate
505 (
506 x_set_of_books_id => PO_R12_CAT_UPG_PVT.g_sys.sob_id,
507 x_from_currency => p_headers_rec.currency_code(i),
508 x_conversion_date => p_headers_rec.rate_date(i),
509 x_conversion_type => p_headers_rec.rate_type_code(i)
510 );
511 -- Bug 5461235: Start
512 l_progress := '132';
513 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'GL_CURRENCY_API.get_rate() returned rate='||rate); END IF;
514
515 l_progress := '134';
516 IF (rate IS NULL) THEN
517 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'GL_CURRENCY_API.get_rate() returned rate as NULL, raising NO_RATE exception.'); END IF;
518 RAISE GL_CURRENCY_API.NO_RATE;
519 END IF;
520 -- Bug 5461235: End
521 EXCEPTION
522 -- Bug 5461235: Start
523 WHEN GL_CURRENCY_API.NO_RATE THEN
524 l_progress := '136';
525 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'GL_CURRENCY_API.get_rate() throws GL_CURRENCY_API.NO_RATE exception'); END IF;
526 p_headers_rec.has_errors(i) := 'Y';
527 -- Add error message into INTERFACE_ERRORS table
528 -- ICX_CAT_RATE_REQD
529 -- "There is no rate for the rate date and type default combination."
530 PO_R12_CAT_UPG_UTL.add_fatal_error(
531 p_interface_header_id => p_headers_rec.interface_header_id(i),
532 p_error_message_name => 'ICX_CAT_RATE_REQD',
533 p_table_name => 'PO_HEADERS_INTERFACE',
534 p_column_name => 'CURRENCY_CODE',
535 p_column_value => p_headers_rec.currency_code(i),
536 p_token1_name => 'API_NAME',
537 p_token1_value => 'GL_CURRENCY_API.get_rate'
538 );
539 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
540 l_progress := '138';
541 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'GL_CURRENCY_API.get_rate() throws GL_CURRENCY_API.INVALID_CURRENCY exception'); END IF;
542 p_headers_rec.has_errors(i) := 'Y';
543 -- Add error message into INTERFACE_ERRORS table
544 -- ICX_CAT_INVALID_CURRENCY
545 -- "Default currency code is inactive or invalid."
546 PO_R12_CAT_UPG_UTL.add_fatal_error(
547 p_interface_header_id => p_headers_rec.interface_header_id(i),
548 p_error_message_name => 'ICX_CAT_INVALID_CURRENCY',
549 p_table_name => 'PO_HEADERS_INTERFACE',
550 p_column_name => 'CURRENCY_CODE',
551 p_column_value => p_headers_rec.currency_code(i),
552 p_token1_name => 'API_NAME',
553 p_token1_value => 'GL_CURRENCY_API.get_rate'
554 );
555 -- Bug 5461235: End
556 WHEN OTHERS THEN
557 l_progress := '139';
558 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception from GL_CURRENCY_API.get_rate(): '|| SQLERRM(SQLCODE)); END IF;
559 -- Mark this record as errored and continue. Do not raise the exception
560 -- because we want to procss as many records as possible.
561 p_headers_rec.has_errors(i) := 'Y';
562
563 -- Add error message into INTERFACE_ERRORS table
564 -- ICX_CAT_ERR_IN_GL_CURR_API
565 -- "An error occurred in the call to API_NAME while retrieving the currency conversion rate."
566 PO_R12_CAT_UPG_UTL.add_fatal_error(
567 p_interface_header_id => p_headers_rec.interface_header_id(i),
568 --p_error_message_name => 'PO_CAT_UPG_GL_API2_EXCEPTION',
569 p_error_message_name => 'ICX_CAT_ERR_IN_GL_CURR_API',
570 p_table_name => 'PO_HEADERS_INTERFACE',
571 p_column_name => 'API_NAME',
572 p_column_value => 'GL_CURRENCY_API.get_rate',
573 p_token1_name => 'API_NAME',
574 p_token1_value => 'GL_CURRENCY_API.get_rate'
575 );
576 END; -- Exception block around GL_CURRENCY_API.get_rate API
577
578 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Return Value, rate='||rate); END IF;
579
580 l_progress := '140';
581 p_headers_rec.rate(i) := ROUND(rate, 15);
582 END IF; -- IF (p_headers_rec.has_errors(i) <> 'Y' AND
583 -- (p_headers_rec.rate(i) IS NULL OR
584 -- p_headers_rec.rate_type_code(i) = 'EMU FIXED'))
585
586 END IF; -- IF (p_headers_rec.currency_code(i) <> g_sys.currency_code)
587
588 <<END_OF_HEADERS_LOOP>>
589 l_progress := '150';
590 END LOOP;
591
592 l_progress := '160';
593 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'|| ','|| SQLERRM(SQLCODE)); END IF;
597 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
598 END default_headers;
599
600 --------------------------------------------------------------------------------
601 --Start of Comments
602 --Name: default_info_from_vendor
603 --Pre-reqs:
604 -- The iP catalog data is populated in input pl/sql tables.
605 --Modifies:
606 -- a) Input pl/sql table: Defaults a value in some columns.
607 -- b) FND_MSG_PUB on unhandled exceptions.
608 --Locks:
609 -- None.
610 --Function:
611 -- Defaults the following header level fields from vendor:
612 --
613 -- invoice_currency_code
614 -- terms_id
615 --
616 -- This API should be called during the upgrade phase only.
617 --Parameters:
618 --IN:
619 --p_key
620 -- Key used to access records in PO_SESSION_GT table.
621 --p_headers_rec
622 -- A record of plsql tables containing a batch of headers. The value of
623 -- vendor_id will be picked up from this record.
624 --OUT:
625 --x_invoice_currency_codes
626 -- A pl/sql table in which the default values of invoice_currency_codes
627 -- would be returned.
628 --x_terms_ids
629 -- A pl/sql table in which the default values of terms_ids
630 -- would be returned.
631 --
632 --End of Comments
633 --------------------------------------------------------------------------------
634 PROCEDURE default_info_from_vendor
635 (
636 p_key IN NUMBER,
637 p_headers_rec IN PO_R12_CAT_UPG_PVT.record_of_headers_type,
638 x_invoice_currency_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15,
639 x_terms_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
640 )
641 IS
642 l_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor';
643 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
644 l_progress VARCHAR2(3) := '000';
645
646 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
647
648 l_currency_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
649 l_terms_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
650 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
651
652 l_index NUMBER;
653 i NUMBER;
654 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
655 BEGIN
656 l_progress := '010';
657 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
658
659 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(l_size);
660
661 l_progress := '020';
662 -- SQL What: Get vendor related info into session GT table
663 -- SQL Why : It will be used to populate the OUT parameters.
664 -- SQL Join: vendor_id
665 FORALL i IN 1 .. p_headers_rec.vendor_id.COUNT
666 INSERT INTO PO_SESSION_GT(key,
667 num1,
668 char1,
669 num2)
670 SELECT p_key,
671 l_subscript_array(i),
672 vendor.invoice_currency_code,
673 vendor.terms_id
674 FROM po_vendors vendor
675 WHERE vendor.vendor_id = p_headers_rec.vendor_id(i)
676 AND p_headers_rec.vendor_id(i) IS NOT NULL
677 --AND p_headers_rec.has_errors(i) = 'N'
678 AND p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;
679
680 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
681
682 l_progress := '030';
683 -- SQL What: Transfer from session GT table to local arrays
684 -- SQL Why : It will be used to populate the OUT parameters.
685 -- SQL Join: key
686 DELETE FROM PO_SESSION_GT
687 WHERE key = p_key
688 RETURNING num1, char1, num2
689 BULK COLLECT INTO l_indexes, l_currency_codes, l_terms_ids;
690
691 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
692
693 FOR i IN 1 .. p_headers_rec.vendor_id.COUNT
697 END LOOP;
694 LOOP
695 x_invoice_currency_codes(i) := NULL;
696 x_terms_ids(i) := NULL;
698
699 l_progress := '040';
700 -- transfer from local arrays to OUT parameters
701 FOR i IN 1 .. l_indexes.COUNT
702 LOOP
703 l_index := l_indexes(i);
704
705 x_invoice_currency_codes(l_index) := l_currency_codes(i);
706 x_terms_ids(l_index) := l_terms_ids(i);
707 END LOOP;
708
709 l_progress := '050';
710 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
711 EXCEPTION
712 WHEN OTHERS THEN
713 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
714 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
715 END default_info_from_vendor;
716
717 --------------------------------------------------------------------------------
718 --Start of Comments
719 --Name: default_vendor_sites
720 --Pre-reqs:
721 -- The iP catalog data is populated in input pl/sql tables.
722 --Modifies:
723 -- a) Input pl/sql table: Defaults a value in some columns.
724 -- b) FND_MSG_PUB on unhandled exceptions.
725 --Locks:
726 -- None.
727 --Function:
728 -- Gets the default vendor_site_id if the site_id is NULL in the interface table.
729 -- The site would default only if there is exactly 1 site for the given vendor.
730 --
731 -- This API should be called during the upgrade phase only.
732 --Parameters:
733 --IN:
734 --p_key
735 -- Key used to access records in PO_SESSION_GT table.
736 --IN/OUT:
737 --p_headers_rec
738 -- A record of plsql tables containing a batch of headers. The value of
739 -- vendor_id will be picked up from this record. The default values of
740 -- vendor site will be written in the tables in this record.
741 --
742 --End of Comments
743 --------------------------------------------------------------------------------
744 PROCEDURE default_vendor_sites
745 (
746 p_key IN NUMBER,
747 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
748 )
749 IS
750 l_api_name CONSTANT VARCHAR2(30) := 'default_vendor_sites';
751 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
752 l_progress VARCHAR2(3) := '000';
753
754 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
755
756 l_vendor_site_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
757 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
758
759 l_index NUMBER;
760 i NUMBER;
761
762 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
763 BEGIN
764 l_progress := '010';
765
766 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
767
768 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.vendor_site_id.COUNT);
769
770 l_progress := '020';
771 -- SQL What: Get the default vendor_site_id if the site_id is NULL in the
772 -- interface table. The site would default only if there is exactly
773 -- 1 site for the given vendor.
774 -- SQL Why : It will be used to populate the OUT parameters.
775 -- SQL Join: vendor_id
776 FORALL i IN 1 .. p_headers_rec.vendor_id.COUNT
777 INSERT INTO PO_SESSION_GT(key, num1, num2, num3)
778 SELECT p_key,
779 l_subscript_array(i),
780 min(vendor_site.vendor_site_id),
781 vendor_site.vendor_id
782 FROM po_vendor_sites_all vendor_site
783 WHERE p_headers_rec.vendor_id(i) IS NOT NULL
784 AND p_headers_rec.vendor_site_id(i) IS NULL
785 --AND p_headers_rec.has_errors(i) = 'N'
786 AND p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
787 AND vendor_site.vendor_id = p_headers_rec.vendor_id(i)
788 AND vendor_site.purchasing_site_flag = 'Y'
789 AND TRUNC(sysdate) < nvl(vendor_site.inactive_date, TRUNC(sysdate + 1))
790 AND NVL(vendor_site.rfq_only_site_flag, 'N') <> 'Y'
791 GROUP BY vendor_site.vendor_id
792 HAVING count(vendor_site.vendor_site_id) = 1;
793
794 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
795
796 l_progress := '030';
797 -- SQL What: Transfer from session GT table to local arrays
798 -- SQL Why : It will be used to populate the OUT parameters.
799 -- SQL Join: key
800 DELETE FROM PO_SESSION_GT
801 WHERE key = p_key
802 RETURNING num1,
803 num2
804 BULK COLLECT INTO l_indexes, l_vendor_site_ids;
805
806 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
807
808 l_progress := '040';
809 -- transfer from local arrays to OUT parameters
810 FOR i IN 1 .. l_indexes.COUNT
811 LOOP
812 l_index := l_indexes(i);
813 p_headers_rec.vendor_site_id(l_index) := l_vendor_site_ids(i);
814 END LOOP;
815
816 l_progress := '050';
817 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
818 EXCEPTION
819 WHEN OTHERS THEN
820 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
821 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
822 END default_vendor_sites;
823
824 --------------------------------------------------------------------------------
825 --Start of Comments
826 --Name: default_info_from_vendor_site
827 --Pre-reqs:
828 -- The iP catalog data is populated in input pl/sql tables.
829 --Modifies:
830 -- a) Input pl/sql table: Defaults a value in some columns.
834 --Function:
831 -- b) FND_MSG_PUB on unhandled exceptions.
832 --Locks:
833 -- None.
835 -- Defaults the following header level fields from vendor:
836 --
837 -- fob_lookup_code
838 -- ship_via_lookup_code
839 -- freight_terms_lookup_code
840 -- ship_to_location_id
841 -- bill_to_location_id
842 -- invoice_currency_code
843 -- terms_id
844 -- pay_on_codes
845 -- shipping_controls
846 --
847 -- This API should be called during the upgrade phase only.
848 --Parameters:
849 --IN:
850 --p_key
851 -- Key used to access records in PO_SESSION_GT table.
852 --IN/OUT:
853 --p_headers_rec
854 -- A record of plsql tables containing a batch of headers. The value of
855 -- vendor_id will be picked up from this record. The default values from
856 -- vendor site will be written in the tables in this record.
857 --
858 --End of Comments
859 --------------------------------------------------------------------------------
860 PROCEDURE default_info_from_vendor_site
861 (
862 p_key IN NUMBER,
863 p_headers_rec IN PO_R12_CAT_UPG_PVT.record_of_headers_type,
864 x_fob_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
865 x_ship_via_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
866 x_freight_terms_lookup_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
867 x_ship_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
868 x_bill_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
869 x_invoice_currency_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15,
870 x_terms_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
871 x_pay_on_codes OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25,
872 x_shipping_controls OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30
873 )
874 IS
875 l_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor_site';
876 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
877 l_progress VARCHAR2(3) := '000';
878
879 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
880
881 l_fob_lookup_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
882 l_ship_via_lookup_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
883 l_freight_terms_luc PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
884 l_ship_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
885 l_bill_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
886 l_currency_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
887 l_terms_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
888 l_shipping_controls PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
889 l_pay_on_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
890 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
891
892 l_index NUMBER;
893 i NUMBER;
894 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
895 BEGIN
896 l_progress := '010';
897
898 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
899
900 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.vendor_site_id.COUNT);
901
902 l_progress := '020';
903 -- SQL What: Get vendor site related info into session GT table
904 -- SQL Why : It will be used to populate the OUT parameters.
905 -- SQL Join: vendor_site_id
906 FORALL i IN 1 .. p_headers_rec.vendor_id.COUNT
907 INSERT INTO PO_SESSION_GT(key,
908 num1,
909 char1,
910 char2,
911 char3,
912 num2,
913 num3,
914 char4,
915 num4,
916 char5,
917 index_char1)
918 SELECT p_key,
919 l_subscript_array(i),
920 vendor_site.fob_lookup_code,
921 vendor_site.ship_via_lookup_code,
922 vendor_site.freight_terms_lookup_code,
923 vendor_site.ship_to_location_id,
924 vendor_site.bill_to_location_id,
925 vendor_site.invoice_currency_code,
926 vendor_site.terms_id,
927 vendor_site.pay_on_code,
928 vendor_site.shipping_control -- (not present in 11.5.9)
929 FROM po_vendor_sites_all vendor_site
930 WHERE p_headers_rec.vendor_id(i) IS NOT NULL
931 AND p_headers_rec.vendor_site_id(i) IS NOT NULL
932 --AND p_headers_rec.has_errors(i) = 'N'
933 AND p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
934 AND vendor_site.vendor_site_id = p_headers_rec.vendor_site_id(i)
935 AND vendor_site.purchasing_site_flag = 'Y'
936 AND TRUNC(sysdate) < nvl(vendor_site.inactive_date, TRUNC(sysdate + 1))
937 AND NVL(vendor_site.rfq_only_site_flag, 'N') <> 'Y';
938
939 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
940
941 l_progress := '030';
942 -- SQL What: Transfer from session GT table to local arrays
943 -- SQL Why : It will be used to populate the OUT parameters.
944 -- SQL Join: key
945 DELETE FROM PO_SESSION_GT
946 WHERE key = p_key
947 RETURNING num1,
948 char1,
949 char2,
950 char3,
951 num2,
952 num3,
953 char4,
954 num4,
955 char5,
956 index_char1
957 BULK COLLECT INTO l_indexes, l_fob_lookup_codes, l_ship_via_lookup_codes,
961
958 l_freight_terms_luc, l_ship_to_location_ids,
959 l_bill_to_location_ids, l_currency_codes, l_terms_ids,
960 l_pay_on_codes, l_shipping_controls;
962 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
963
964 FOR i IN 1 .. p_headers_rec.vendor_id.COUNT
965 LOOP
966 x_fob_lookup_codes(i) := NULL;
967 x_ship_via_lookup_codes(i) := NULL;
968 x_freight_terms_lookup_codes(i) := NULL;
969 x_ship_to_location_ids(i) := NULL;
970 x_bill_to_location_ids(i) := NULL;
971 x_invoice_currency_codes(i) := NULL;
972 x_terms_ids(i) := NULL;
973 x_pay_on_codes(i) := NULL;
974 x_shipping_controls(i) := NULL;
975 END LOOP;
976
977 l_progress := '040';
978 -- transfer from local arrays to OUT parameters
979 FOR i IN 1 .. l_indexes.COUNT
980 LOOP
981 l_index := l_indexes(i);
982
983 x_fob_lookup_codes(l_index) := l_fob_lookup_codes(i);
984 x_ship_via_lookup_codes(l_index) := l_ship_via_lookup_codes(i);
985 x_freight_terms_lookup_codes(l_index) := l_freight_terms_luc(i);
986 x_ship_to_location_ids(l_index) := l_ship_to_location_ids(i);
987 x_bill_to_location_ids(l_index) := l_bill_to_location_ids(i);
988 x_invoice_currency_codes(l_index) := l_currency_codes(i);
989 x_terms_ids(l_index) := l_terms_ids(i);
990 x_pay_on_codes(l_index) := l_pay_on_codes(i);
991 x_shipping_controls(l_index) := l_shipping_controls(i);
992 END LOOP;
993
994 l_progress := '050';
995 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
996 EXCEPTION
997 WHEN OTHERS THEN
998 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
999 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1000 END default_info_from_vendor_site;
1001
1002 --------------------------------------------------------------------------------
1003 --Start of Comments
1004 --Name: default_vendor_contact_info
1005 --Pre-reqs:
1006 -- The iP catalog data is populated in input pl/sql tables.
1007 --Modifies:
1008 -- a) Input pl/sql table: Defaults a value in some columns.
1009 -- b) FND_MSG_PUB on unhandled exceptions.
1010 --Locks:
1011 -- None.
1012 --Function:
1013 -- Defaults the vendor contact id from vendor site.
1014 -- This API should be called during the upgrade phase only.
1015 --Parameters:
1016 --IN:
1017 --p_key
1018 -- Key used to access records in PO_SESSION_GT table.
1019 --IN/OUT:
1020 --p_headers_rec
1021 -- A record of plsql tables containing a batch of headers. The value of
1022 -- vendor_site_id will be picked up from this record. The default values
1023 -- of vendor_contact_id will be written in the tables in this record.
1024 --
1025 --End of Comments
1026 --------------------------------------------------------------------------------
1027 PROCEDURE default_vendor_contact_info
1028 (
1029 p_key IN NUMBER,
1030 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1031 )
1032 IS
1033 l_api_name CONSTANT VARCHAR2(30) := 'default_vendor_contact_info';
1034 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1035 l_progress VARCHAR2(3) := '000';
1036
1037 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1038
1039 l_contact_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1040 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1041 l_index NUMBER;
1042
1043 i NUMBER;
1044 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1045 BEGIN
1046 l_progress := '010';
1047 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1048
1049 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.vendor_site_id.COUNT);
1050
1051 l_progress := '020';
1052 -- SQL What: Get vendor contact id into session GT table
1053 -- SQL Why : It will be used to populate the OUT parameters.
1054 -- SQL Join: vendor_site_id
1055 FORALL i IN 1 .. p_headers_rec.vendor_site_id.COUNT
1056 INSERT INTO PO_SESSION_GT(key,
1057 num1,
1058 num2,
1059 num3)
1060 SELECT p_key,
1061 l_subscript_array(i),
1062 min(pvc.vendor_contact_id),
1063 pvc.vendor_site_id
1064 FROM po_vendor_contacts pvc
1065 WHERE p_headers_rec.vendor_id(i) IS NOT NULL
1066 AND p_headers_rec.vendor_site_id(i) IS NOT NULL
1067 AND p_headers_rec.vendor_contact_id(i) IS NULL
1068 --AND p_headers_rec.has_errors(i) = 'N'
1069 AND p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
1070 AND pvc.vendor_site_id = p_headers_rec.vendor_site_id(i)
1071 AND TRUNC(sysdate) < nvl(pvc.inactive_date, TRUNC(sysdate + 1))
1072 GROUP BY pvc.vendor_site_id
1073 HAVING count(pvc.vendor_contact_id) = 1;
1074
1075 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1076
1077 l_progress := '030';
1078 -- SQL What: Transfer from session GT table to local arrays
1079 -- SQL Why : It will be used to populate the OUT parameters.
1080 -- SQL Join: key
1081 DELETE FROM po_session_gt
1082 WHERE key = p_key
1083 RETURNING num1, num2
1084 BULK COLLECT INTO l_indexes, l_contact_ids;
1085
1086 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1087
1088 l_progress := '040';
1089 -- transfer from local arrays to OUT parameters
1093 p_headers_rec.vendor_contact_id(l_index) := l_contact_ids(i);
1090 FOR i IN 1 .. l_indexes.COUNT
1091 LOOP
1092 l_index := l_indexes(i);
1094 END LOOP;
1095
1096 l_progress := '050';
1097 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1101 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1102 END default_vendor_contact_info;
1103
1104 --------------------------------------------------------------------------------
1105 --Start of Comments
1106 --Name: default_buyer
1107 --Pre-reqs:
1108 -- The iP catalog data is populated in input pl/sql tables.
1109 -- The defaulting of vendor/site and currency has already happenned.
1110 --Modifies:
1111 -- a) Input pl/sql table: Defaults a value in some columns.
1112 -- b) FND_MSG_PUB on unhandled exceptions.
1113 --Locks:
1114 -- None.
1115 --Function:
1116 -- Gets the default buyer (agent_id) if the agent_id is NULL in the interface table.
1117 -- The defaulting rules are as follows:
1118 -- 1) Get the buyer from the referenced CPA
1119 -- 2) For those headers that do not have any CPA reference, try to source
1120 -- a document to obtain a buyer. The sourcing rule will look for
1121 -- the most recent created document in any status. The matching
1122 -- will be based on supplier, supplier site, currency and OU. It
1123 -- will follow the order below:
1124 -- a. Matching BPA Header
1125 -- b. Matching CPA
1126 -- c. Matching Standard PO
1127 -- 3) If there is no CPA reference, and no source doc is found,
1128 -- then get the latest buyer created for the current business group.
1129 --
1130 -- This API should be called during the upgrade phase only.
1131 --Parameters:
1132 --IN:
1133 --p_key
1134 -- Key used to access records in PO_SESSION_GT table.
1135 --IN/OUT:
1136 --p_headers_rec
1137 -- A record of plsql tables containing a batch of headers. The value of
1138 -- vendor_id, site_id, currency_code, org_id, cpa_reference will be picked up
1139 -- from this record. The default values of agent_id will be written in the
1140 -- tables in this record.
1141 --
1142 --End of Comments
1143 --------------------------------------------------------------------------------
1144 PROCEDURE default_buyer
1145 (
1146 p_key IN NUMBER
1147 , x_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1148 )
1149 IS
1150 l_api_name CONSTANT VARCHAR2(30) := 'default_buyer';
1151 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1152 l_progress VARCHAR2(3) := '000';
1153
1154 l_agent_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1155 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1156 l_index NUMBER;
1157
1158 i NUMBER;
1159 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1160 BEGIN
1161 l_progress := '010';
1162 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1163
1164 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(x_headers_rec.po_header_id.COUNT);
1165
1166 l_progress := '020';
1167 -- SQL What: Get the buyer from the referenced CPA
1168 -- SQL Why : It will be used to populate the OUT parameters (to default the
1169 -- agent_id into the new GBPA)
1170 -- SQL Join: segment1, org_id
1171 FORALL i IN 1 .. x_headers_rec.po_header_id.COUNT
1172 INSERT INTO PO_SESSION_GT(key,
1173 num1,
1174 num2)
1175 SELECT p_key,
1176 l_subscript_array(i),
1177 POH.agent_id
1178 FROM PO_HEADERS_ALL POH
1179 WHERE POH.po_header_id = x_headers_rec.cpa_reference(i)
1180 AND x_headers_rec.cpa_reference(i) IS NOT NULL
1181 AND x_headers_rec.agent_id(i) IS NULL
1182 --AND x_headers_rec.has_errors(i) = 'N'
1183 AND x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
1184 AND EXISTS
1185 (SELECT 'Its a valid buyer'
1186 FROM PO_BUYERS_VAL_V VALID_BUYER
1187 WHERE VALID_BUYER.employee_id = POH.agent_id);
1188
1189 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1190
1191 l_progress := '030';
1192 -- SQL What: Transfer from session GT table to local arrays
1193 -- SQL Why : It will be used to populate the OUT parameters.
1194 -- SQL Join: key
1195 DELETE FROM PO_SESSION_GT
1196 WHERE key = p_key
1197 RETURNING num1, num2
1198 BULK COLLECT INTO l_indexes, l_agent_ids;
1199
1200 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1201
1202 l_progress := '040';
1203 -- transfer from local arrays to OUT parameters
1204 FOR i IN 1 .. l_indexes.COUNT
1205 LOOP
1206 l_index := l_indexes(i);
1207 x_headers_rec.agent_id(l_index) := l_agent_ids(i);
1208 END LOOP;
1209
1210 l_progress := '050';
1211 -- SQL What: For data that does not have any CPA reference, try to source
1212 -- a document to obtain a buyer. The sourcing rule will look for
1213 -- the most recent created document in any status. The matching
1214 -- will be based on supplier, supplier site, currency and OU. It
1215 -- will follow the order below:
1216 -- 1. Matching BPA Header
1217 -- 2. Matching CPA
1218 -- 3. Matching Standard PO
1219 -- SQL Why : It will be used to populate the OUT parameters (to default the
1223 INSERT INTO PO_SESSION_GT(key,
1220 -- agent_id into the new GBPA)
1221 -- SQL Join: vendor_id, vendor_site_id, currency_code, org_id, type_lookup_code
1222 FORALL i IN 1 .. x_headers_rec.po_header_id.COUNT
1224 num1,
1225 num2)
1226 SELECT p_key,
1227 l_subscript_array(i),
1228 SUB_QUERY.agent_id
1229 FROM (
1230 SELECT agent_id
1231 FROM
1232 (SELECT POH.agent_id
1233 FROM PO_HEADERS_ALL POH
1234 WHERE POH.vendor_id = x_headers_rec.vendor_id(i)
1235 AND POH.vendor_site_id = x_headers_rec.vendor_site_id(i)
1236 AND POH.currency_code = x_headers_rec.currency_code(i)
1237 AND POH.org_id = x_headers_rec.org_id(i)
1238 AND POH.type_lookup_code IN ('BLANKET', 'CONTRACT', 'STANDARD')
1239 AND EXISTS
1240 (SELECT 'Its a valid buyer'
1241 FROM PO_BUYERS_VAL_V VALID_BUYER
1242 WHERE VALID_BUYER.employee_id = POH.agent_id)
1243 ORDER BY
1244 DECODE(POH.type_lookup_code,
1245 'BLANKET', 1,
1246 'CONTRACT', 2,
1247 'STANDARD', 3) ASC,
1248 POH.creation_date DESC)
1249 WHERE rownum = 1
1250 ) SUB_QUERY
1251 WHERE x_headers_rec.agent_id(i) IS NULL
1252 --AND x_headers_rec.has_errors(i) = 'N'
1253 AND x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;
1254
1255 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1256
1257 l_progress := '060';
1258 -- SQL What: Transfer from session GT table to local arrays
1259 -- SQL Why : It will be used to populate the OUT parameters.
1260 -- SQL Join: key
1261 DELETE FROM PO_SESSION_GT
1262 WHERE key = p_key
1263 RETURNING num1, num2
1264 BULK COLLECT INTO l_indexes, l_agent_ids;
1265
1266 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1267
1268 l_progress := '070';
1269 -- transfer from local arrays to OUT parameters
1270 FOR i IN 1 .. l_indexes.COUNT
1271 LOOP
1272 l_index := l_indexes(i);
1273 x_headers_rec.agent_id(l_index) := l_agent_ids(i);
1274 END LOOP;
1275
1276 l_progress := '080';
1277 -- SQL What: If there is no CPA reference, and no source doc is found,
1278 -- then get the latest buyer created for the current business group.
1279 -- SQL Why : It will be used to populate the OUT parameters (to default the
1280 -- agent_id into the new GBPA)
1281 -- SQL Join: org_id, business_group_id, agent_id
1282
1283 -- Note: In this query, we need the sub query because the view PO_BUYERS_VAL_V
1284 -- does not have the column CREATION_DATE, on which we want to sort.
1285 -- Bug#5389286 use PER_EMPLOYEES_CURRENT_X instead of POBUYERS_VAL_V
1286 -- and collapsed 2 subqueries into 1 subquery to get newest valid
1287 -- buyer in the current business group
1288 FORALL i IN 1 .. x_headers_rec.po_header_id.COUNT
1289 INSERT INTO PO_SESSION_GT(key,
1290 num1,
1291 num2)
1292 SELECT p_key,
1293 l_subscript_array(i),
1294 SUB_QUERY.agent_id
1295 FROM (
1296 SELECT agent_id
1297 FROM
1298 (
1299 SELECT BUYER.agent_id
1300 FROM PO_AGENTS BUYER, PER_EMPLOYEES_CURRENT_X HRE,
1301 HR_ALL_ORGANIZATION_UNITS HROU
1302 WHERE HRE.EMPLOYEE_ID = BUYER.AGENT_ID
1303 AND SYSDATE BETWEEN NVL(BUYER.START_DATE_ACTIVE, SYSDATE-1)
1304 AND NVL(BUYER.END_DATE_ACTIVE, SYSDATE+1)
1305 AND HROU.organization_id = x_headers_rec.org_id(i)
1306 AND HROU.business_group_id = HRE.business_group_id
1307 ORDER BY BUYER.creation_date DESC
1308 )
1309 WHERE rownum = 1
1310 ) SUB_QUERY
1311 WHERE x_headers_rec.agent_id(i) IS NULL
1312 --AND x_headers_rec.has_errors(i) = 'N'
1313 AND x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;
1314
1315 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1316
1317 l_progress := '090';
1318 -- SQL What: Transfer from session GT table to local arrays
1319 -- SQL Why : It will be used to populate the OUT parameters.
1320 -- SQL Join: key
1321 DELETE FROM PO_SESSION_GT
1322 WHERE key = p_key
1323 RETURNING num1, num2
1324 BULK COLLECT INTO l_indexes, l_agent_ids;
1325
1326 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1327
1328 l_progress := '100';
1329 -- transfer from local arrays to OUT parameters
1330 FOR i IN 1 .. l_indexes.COUNT
1331 LOOP
1332 l_index := l_indexes(i);
1333 x_headers_rec.agent_id(l_index) := l_agent_ids(i);
1334 END LOOP;
1335
1336 l_progress := '110';
1337 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1338 EXCEPTION
1339 WHEN OTHERS THEN
1340 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1341 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1342 END default_buyer;
1343
1344
1345 ----------------------------------------------------------------------------------
1346 -- Lines
1350
1347 ----------------------------------------------------------------------------------
1348
1349 -- Forward function declarations
1351 PROCEDURE default_hdr_info
1352 (
1353 p_key IN PO_SESSION_GT.key%TYPE,
1354 p_lines_rec IN PO_R12_CAT_UPG_PVT.record_of_lines_type,
1355 x_org_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1356 x_vendor_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1357 x_vendor_site_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1358 x_ship_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1359 x_min_release_amounts OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1360 x_po_header_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
1361 );
1362
1363 PROCEDURE copy_info_from_hdr
1364 (
1365 p_hdr_org_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1366 p_po_header_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1367 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1368 );
1369
1370 PROCEDURE default_line_type
1371 (
1372 p_key IN PO_SESSION_GT.key%TYPE,
1373 p_hdr_org_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1374 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1375 );
1376
1377 PROCEDURE default_info_from_line_type
1378 (
1379 p_key IN PO_SESSION_GT.key%TYPE,
1380 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1381 );
1382
1383 PROCEDURE default_info_from_item
1384 (
1385 p_key IN PO_SESSION_GT.key%TYPE,
1386 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1387 );
1388
1389 PROCEDURE default_hzd_cls_from_un_num
1390 (
1391 p_key IN PO_SESSION_GT.key%TYPE,
1392 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1393 );
1394
1395 PROCEDURE get_ship_to_org_from_location
1396 (
1397 p_key IN PO_SESSION_GT.key%TYPE,
1398 p_location_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1399 x_org_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
1400 );
1401
1402 -- END: Forward function declarations
1403
1404 --------------------------------------------------------------------------------
1405 --Start of Comments
1406 --Name: default_lines
1407 --Pre-reqs:
1408 -- The iP catalog data is populated in input pl/sql tables.
1409 --Modifies:
1410 -- a) Input pl/sql table: Overwrites the defaulted value.
1411 -- b) FND_MSG_PUB on unhandled exceptions.
1412 --Locks:
1413 -- None.
1414 --Function:
1415 -- Defaults the line level column values, if no value is given in the
1416 -- interface tables.
1417 -- This API should be called during the upgrade phase only.
1418 --Parameters:
1419 --IN:
1420 -- p_lines_rec
1421 -- A table of plsql records containing a batch of lines.
1422 --OUT:
1423 --End of Comments
1424 --------------------------------------------------------------------------------
1425 PROCEDURE default_lines
1426 (
1427 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1428 )
1429 IS
1430 l_api_name CONSTANT VARCHAR2(30) := 'default_lines';
1431 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1432 l_progress VARCHAR2(3) := '000';
1433
1434 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
1435
1436 l_key PO_SESSION_GT.key%TYPE;
1437 i NUMBER;
1438
1439 l_hdr_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1440 l_hdr_vendor_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1441 l_hdr_vendor_site_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1442 l_hdr_ship_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1443 l_hdr_min_release_amounts PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1444 l_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1445
1446 l_it_inspection_required_flags PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR1;
1447 l_ship_to_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1448
1449 BEGIN
1450 l_progress := '010';
1451 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1452
1453 l_progress := '020';
1454 -- pick a new key from temp table which will be used in all default logic
1455 --l_key := PO_CORE_S.get_session_gt_nextval;
1456 SELECT PO_SESSION_GT_S.nextval
1457 INTO l_key
1458 FROM DUAL;
1459
1460 l_progress := '030';
1461 -- some information for the lines has to be copied from the respective
1462 -- header level. Each of the arrays that are OUT parameters will have
1463 -- an entry for every record in the p_lines array.
1464 default_hdr_info
1465 (
1466 p_key => l_key,
1467 p_lines_rec => p_lines_rec,
1468 x_org_ids => l_hdr_org_ids,
1469 x_vendor_ids => l_hdr_vendor_ids,
1470 x_vendor_site_ids => l_hdr_vendor_site_ids,
1471 x_ship_to_location_ids => l_hdr_ship_to_location_ids,
1472 x_min_release_amounts => l_hdr_min_release_amounts,
1473 x_po_header_ids => l_po_header_ids
1474 );
1475
1476 l_progress := '040';
1477 -- copy the org_id and po_header_id from header
1478 copy_info_from_hdr
1479 (
1480 p_hdr_org_ids => l_hdr_org_ids,
1481 p_po_header_ids => l_po_header_ids,
1482 x_lines_rec => p_lines_rec -- IN OUT
1483 );
1484
1485 l_progress := '050';
1486 -- default line_type_id that will be used in other defaulting logic
1487 default_line_type
1488 (
1489 p_key => l_key,
1490 p_hdr_org_ids => l_hdr_org_ids,
1491 x_lines_rec => p_lines_rec -- IN OUT
1492 );
1493
1494 l_progress := '060';
1498 -- purchase_basis,
1495 -- Get default info from line type definition,
1496 -- The attributes we default from line type are:
1497 -- order_type_lookup_code,
1499 -- matching_basis
1500 -- The following default in PDOI, but in catalog migration iP will
1501 -- provide a value for these and therefore, they are not required
1502 -- to be defaulted.
1503 -- category_id,
1504 -- unit_of_measure,
1505 -- unit_price
1506 default_info_from_line_type
1507 (
1508 p_key => l_key,
1509 x_lines_rec => p_lines_rec -- IN OUT
1510 );
1511
1512 l_progress := '070';
1513 -- Get default info from item definition
1514 -- The attributes we default from item are:
1515 -- un_number_id,
1516 -- hazard_class_id,
1517 -- market_price
1518 -- inspection_required_flag
1519 -- The following default in PDOI, but in catalog migration iP will
1520 -- provide a value for these and therefore, they are not required
1521 -- to be defaulted.
1522 -- item_description,
1523 -- unit_of_measure,
1524 -- unit_price
1525 -- category_id
1526 default_info_from_item(
1527 p_key => l_key,
1528 x_lines_rec => p_lines_rec);
1529
1530 l_progress := '080';
1531 -- If hazard_class_id is NULL at item level and un_number_id is not null,
1532 -- then default hazard_class_id from UN Number.
1533 default_hzd_cls_from_un_num(
1534 p_key => l_key,
1535 x_lines_rec => p_lines_rec);
1536
1537 l_progress := '090';
1538 -- On the Header, only the ship_to_location_id is present, so we need to
1539 -- get the ship_to_org_id's here
1540 get_ship_to_org_from_location(p_key => l_key,
1541 p_location_ids => l_hdr_ship_to_location_ids,
1542 x_org_ids => l_ship_to_org_ids);
1543
1544 l_progress := '100';
1545 -- default all other attributes
1546 FOR i IN 1 .. p_lines_rec.interface_line_id.COUNT
1547 LOOP
1548 l_progress := '110';
1549 IF (--p_lines_rec.has_errors(i) = 'Y' OR
1550 p_lines_rec.action(i) <> PO_R12_CAT_UPG_PVT.g_action_line_create) THEN
1551 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Skipping rec#'||i||' has_errors='||p_lines_rec.has_errors(i)||'action='||p_lines_rec.action(i)); END IF;
1552 goto END_OF_LINES_LOOP;
1553 END IF;
1554
1555 -- default base_unit_price
1556 IF (p_lines_rec.base_unit_price(i) IS NULL AND
1557 p_lines_rec.order_type_lookup_code(i) <> 'FIXED PRICE') THEN
1558 p_lines_rec.base_unit_price(i) := p_lines_rec.unit_price(i);
1559 END IF;
1560
1561 -- default list_price_per_unit
1562 -- TODO: ONLY PRESENT IN TXN TABLES
1563 --IF (p_lines_rec.list_prices_per_unit(i) IS NULL AND
1564 -- p_lines_rec.item_id(i) IS NOT NULL) THEN
1565 -- p_lines_rec.list_prices_per_unit(i) := p_lines_rec.unit_price(i);
1566 --END IF;
1567
1568 l_progress := '120';
1569 -- default market_price
1570 IF (p_lines_rec.market_price(i) IS NULL AND
1571 p_lines_rec.item_id(i) IS NOT NULL) THEN
1572 p_lines_rec.market_price(i) := p_lines_rec.unit_price(i);
1573 END IF;
1574
1575 l_progress := '130';
1576 --------------------------------------------------------------
1577 --p_lines_rec.po_line_id(i) := NULL; -- NOT NULL NUMBER From sequence PO_LINES_S
1578 p_lines_rec.last_update_date(i) := sysdate; -- NOT NULL DATE Sysdate
1579 l_progress := '131';
1580 p_lines_rec.last_updated_by(i) := FND_GLOBAL.user_id; -- NOT NULL NUMBER FND_GLOBAL.user_id
1581 --p_lines_rec.po_header_id(i) := NULL; -- NOT NULL NUMBER From the PO header created in the same flow
1582 --p_lines_rec.line_type_id(i) := NULL; -- NOT NULL NUMBER Default as in PDOI(Open issue) In PDOI it is a must column/parameter like buyer
1583 --p_lines_rec.line_num(i) := NULL; -- NOT NULL NUMBER Default as in PDOI
1584 p_lines_rec.last_update_login(i) := FND_GLOBAL.login_id; -- NUMBER FND_GLOBAL.login_id
1585 p_lines_rec.creation_date(i) := sysdate; -- DATE sysdate
1586 p_lines_rec.created_by(i) := PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER; -- NUMBER -12 (suggested by iP)
1587 --p_lines_rec.item_id(i) := NULL; -- NUMBER Copy value from interface table
1588 --p_lines_rec.item_revision(i) := NULL; -- VARCHAR2(3) Copy value from interface table
1589 --p_lines_rec.category_id(i) := NULL; -- NUMBER Copy value from interface table
1590 --p_lines_rec.item_description(i) := NULL; -- VARCHAR2(240) Copy value from interface table
1591 --p_lines_rec.unit_meas_lookup_code(i) := NULL; -- VARCHAR2(25) Copy value from interface table
1592 --p_lines_rec.quantity_committed(i) := NULL; -- NUMBER NULL -- TODO: ONLY PRESENT IN TXN TABLES
1593 p_lines_rec.committed_amount(i) := NULL; -- NUMBER NULL
1594 p_lines_rec.allow_price_override_flag(i) := 'N'; -- VARCHAR2(1) Default as in PDOI (iP will not provide)
1595 p_lines_rec.not_to_exceed_price(i) := NULL; -- NUMBER NULL
1596 l_progress := '132';
1597 --p_lines_rec.list_price_per_unit(i) := NULL; -- NUMBER ??? (Same as unit_price?)
1598 --p_lines_rec.unit_price(i) := NULL; -- NUMBER Copy value from interface table
1599 p_lines_rec.quantity(i) := NULL; -- NUMBER NULL
1600 --p_lines_rec.un_number_id(i) := NULL; -- NUMBER Default as in PDOI (iP will not provide)
1601 --p_lines_rec.hazard_class_id(i) := NULL; -- NUMBER Default as in PDOI (iP will not provide)
1602 p_lines_rec.note_to_vendor(i) := NULL; -- VARCHAR2(480) NULL
1603 p_lines_rec.from_header_id(i) := NULL; -- NUMBER NULL
1604 p_lines_rec.from_line_id(i) := NULL; -- NUMBER NULL
1605 p_lines_rec.min_order_quantity(i) := NULL; -- NUMBER NULL
1606 p_lines_rec.max_order_quantity(i) := NULL; -- NUMBER NULL
1610 --p_lines_rec.market_price(i) := NULL; -- NUMBER Default as in PDOI (iP will not provide)
1607 p_lines_rec.qty_rcv_tolerance(i) := NULL; -- NUMBER Default as in PDOI (iP will not provide)
1608 p_lines_rec.over_tolerance_error_flag(i) := NULL; -- VARCHAR2(25) NULL
1609 l_progress := '133';
1611 --p_lines_rec.unordered_flag(i) := 'N'; -- VARCHAR2(1) N -- TODO: ONLY PRESENT IN TXN TABLES
1612 --p_lines_rec.closed_flag(i) := 'N'; -- VARCHAR2(1) N -- TODO: ONLY PRESENT IN TXN TABLES
1613 --p_lines_rec.user_hold_flag(i) := 'N'; -- VARCHAR2(1) N -- TODO: ONLY PRESENT IN TXN TABLES
1614 --p_lines_rec.cancel_flag(i) := 'N'; -- VARCHAR2(1) N -- TODO: ONLY PRESENT IN TXN TABLES
1615 --p_lines_rec.cancelled_by(i) := NULL; -- NUMBER(9) NULL -- TODO: ONLY PRESENT IN TXN TABLES
1616 --p_lines_rec.cancel_date(i) := NULL; -- DATE NULL -- TODO: ONLY PRESENT IN TXN TABLES
1617 --p_lines_rec.cancel_reason(i) := NULL; -- VARCHAR2(240) NULL -- TODO: ONLY PRESENT IN TXN TABLES
1618 --p_lines_rec.firm_status_lookup_code(i) := NULL; -- VARCHAR2(30) NULL -- TODO: ONLY PRESENT IN TXN TABLES
1619 --p_lines_rec.firm_date(i) := NULL; -- DATE NULL -- TODO: ONLY PRESENT IN TXN TABLES
1620 --p_lines_rec.vendor_product_num(i) := NULL; -- VARCHAR2(25) Copy value from interface table
1621 p_lines_rec.contract_num(i) := NULL; -- VARCHAR2(25) NULL
1622 p_lines_rec.type_1099(i) := NULL; -- VARCHAR2(10) Default as in PDOI (iP will not provide)
1623 p_lines_rec.capital_expense_flag(i) := 'N'; -- VARCHAR2(1) N
1624 --p_lines_rec.negotiated_by_preparer_flag(i) := 'N'; -- VARCHAR2(1) N
1625 l_progress := '134';
1626 --p_lines_rec.attribute_category(i) := NULL; -- VARCHAR2(30) NULL
1627 --p_lines_rec.attribute1(i) := NULL; -- VARCHAR2(150) NULL
1628 --p_lines_rec.attribute2(i) := NULL; -- VARCHAR2(150) NULL
1629 --p_lines_rec.attribute3(i) := NULL; -- VARCHAR2(150) NULL
1630 --p_lines_rec.attribute4(i) := NULL; -- VARCHAR2(150) NULL
1631 --p_lines_rec.attribute5(i) := NULL; -- VARCHAR2(150) NULL
1632 --p_lines_rec.attribute6(i) := NULL; -- VARCHAR2(150) NULL
1633 --p_lines_rec.attribute7(i) := NULL; -- VARCHAR2(150) NULL
1634 --p_lines_rec.attribute8(i) := NULL; -- VARCHAR2(150) NULL
1635 --p_lines_rec.attribute9(i) := NULL; -- VARCHAR2(150) NULL
1636 --p_lines_rec.attribute10(i) := NULL; -- VARCHAR2(150) NULL
1637 --p_lines_rec.reference_num(i) := NULL; -- VARCHAR2(25) NULL
1638 --p_lines_rec.attribute11(i) := NULL; -- VARCHAR2(150) NULL
1639 --p_lines_rec.attribute12(i) := NULL; -- VARCHAR2(150) NULL
1640 --p_lines_rec.attribute13(i) := NULL; -- VARCHAR2(150) NULL
1641 --p_lines_rec.attribute14(i) := NULL; -- VARCHAR2(150) NULL
1642 --p_lines_rec.attribute15(i) := NULL; -- VARCHAR2(150) NULL
1643 p_lines_rec.min_release_amount(i) := l_hdr_min_release_amounts(i); -- NUMBER Copy value from header level
1644 p_lines_rec.price_type(i) := PO_R12_CAT_UPG_PVT.g_sys.price_lookup_code; -- VARCHAR2(25) Default as in PDOI (iP will not provide)
1645 p_lines_rec.closed_code(i) := 'OPEN'; -- VARCHAR2(25) OPEN
1646 p_lines_rec.price_break_lookup_code(i) := NULL; -- VARCHAR2(25) NULL (For GA's)
1647 p_lines_rec.ussgl_transaction_code(i) := NULL; -- VARCHAR2(30) NULL
1648 --p_lines_rec.government_context(i) := NULL; -- VARCHAR2(30) NULL -- TODO: ONLY PRESENT IN TXN TABLES
1649 l_progress := '135';
1650 p_lines_rec.request_id(i) := FND_GLOBAL.conc_request_id; -- NUMBER iPs conc program request id
1651 p_lines_rec.program_application_id(i) := FND_GLOBAL.prog_appl_id; -- NUMBER
1652 p_lines_rec.program_id(i) := FND_GLOBAL.conc_program_id; -- NUMBER
1653 p_lines_rec.program_update_date(i) := sysdate; -- DATE
1654 p_lines_rec.closed_date(i) := NULL; -- DATE NULL
1655 p_lines_rec.closed_reason(i) := NULL; -- VARCHAR2(240) NULL
1656 p_lines_rec.closed_by(i) := NULL; -- NUMBER(9) NULL
1657 p_lines_rec.transaction_reason_code(i) := NULL; -- VARCHAR2(25) NULL
1658 l_progress := '136';
1659 --p_lines_rec.org_id(i) := NULL; -- NUMBER Copy value from header level.
1660 --p_lines_rec.qc_grade(i) := NULL; -- VARCHAR2(25) NULL (Obsolete)
1661 --p_lines_rec.base_uom(i) := NULL; -- VARCHAR2(25) NULL (Obsolete)
1662 --p_lines_rec.base_qty(i) := NULL; -- NUMBER NULL (Obsolete)
1663 --p_lines_rec.secondary_uom(i) := NULL; -- VARCHAR2(25) NULL (Obsolete)
1664 --p_lines_rec.secondary_qty(i) := NULL; -- NUMBER NULL (Obsolete)
1665 --p_lines_rec.global_attribute_category(i) := NULL; -- VARCHAR2(150) NULL
1666 --p_lines_rec.global_attribute1(i) := NULL; -- VARCHAR2(150) NULL
1667 --p_lines_rec.global_attribute2(i) := NULL; -- VARCHAR2(150) NULL
1668 --p_lines_rec.global_attribute3(i) := NULL; -- VARCHAR2(150) NULL
1669 --p_lines_rec.global_attribute4(i) := NULL; -- VARCHAR2(150) NULL
1670 --p_lines_rec.global_attribute5(i) := NULL; -- VARCHAR2(150) NULL
1671 --p_lines_rec.global_attribute6(i) := NULL; -- VARCHAR2(150) NULL
1672 --p_lines_rec.global_attribute7(i) := NULL; -- VARCHAR2(150) NULL
1673 --p_lines_rec.global_attribute8(i) := NULL; -- VARCHAR2(150) NULL
1674 --p_lines_rec.global_attribute9(i) := NULL; -- VARCHAR2(150) NULL
1675 --p_lines_rec.global_attribute10(i) := NULL; -- VARCHAR2(150) NULL
1676 --p_lines_rec.global_attribute11(i) := NULL; -- VARCHAR2(150) NULL
1677 --p_lines_rec.global_attribute12(i) := NULL; -- VARCHAR2(150) NULL
1678 --p_lines_rec.global_attribute13(i) := NULL; -- VARCHAR2(150) NULL
1679 --p_lines_rec.global_attribute14(i) := NULL; -- VARCHAR2(150) NULL
1680 --p_lines_rec.global_attribute15(i) := NULL; -- VARCHAR2(150) NULL
1681 --p_lines_rec.global_attribute16(i) := NULL; -- VARCHAR2(150) NULL
1682 --p_lines_rec.global_attribute17(i) := NULL; -- VARCHAR2(150) NULL
1683 --p_lines_rec.global_attribute18(i) := NULL; -- VARCHAR2(150) NULL
1684 --p_lines_rec.global_attribute19(i) := NULL; -- VARCHAR2(150) NULL
1685 --p_lines_rec.global_attribute20(i) := NULL; -- VARCHAR2(150) NULL
1686 --p_lines_rec.line_reference_num(i) := NULL; -- VARCHAR2(25) NULL
1687 --p_lines_rec.project_id(i) := NULL; -- NUMBER NULL
1688 --p_lines_rec.task_id(i) := NULL; -- NUMBER NULL
1689 p_lines_rec.expiration_date(i) := NULL; -- DATE NULL
1690 p_lines_rec.oke_contract_header_id(i) := NULL; -- NUMBER NULL
1691 p_lines_rec.oke_contract_version_id(i) := NULL; -- NUMBER NULL
1692 p_lines_rec.secondary_quantity(i) := NULL; -- NUMBER NULL
1693 p_lines_rec.secondary_unit_of_measure(i) := NULL; -- VARCHAR2(25) NULL
1694 p_lines_rec.preferred_grade(i) := NULL; -- VARCHAR2(150) NULL
1695 p_lines_rec.auction_header_id(i) := NULL; -- NUMBER NULL
1696 p_lines_rec.auction_display_number(i) := NULL; -- VARCHAR2(40) NULL
1697 p_lines_rec.auction_line_number(i) := NULL; -- NUMBER NULL
1698 p_lines_rec.bid_number(i) := NULL; -- NUMBER NULL
1699 p_lines_rec.bid_line_number(i) := NULL; -- NUMBER NULL
1700 l_progress := '137';
1701 --p_lines_rec.retroactive_date(i) := NULL; -- DATE NULL
1702 p_lines_rec.supplier_ref_number(i) := NULL; -- VARCHAR2(150) NULL
1703 p_lines_rec.contract_id(i) := NULL; -- NUMBER NULL
1704 --p_lines_rec.start_date(i) := NULL; -- DATE NULL (contingent worker)
1705 p_lines_rec.amount(i) := NULL; -- NUMBER NULL (services proc)
1706 p_lines_rec.job_id(i) := NULL; -- NUMBER NULL
1707 p_lines_rec.contractor_first_name(i) := NULL; -- VARCHAR2(240) NULL
1708 p_lines_rec.contractor_last_name(i) := NULL; -- VARCHAR2(240) NULL
1709 p_lines_rec.from_line_location_id(i) := NULL; -- NUMBER NULL
1710 l_progress := '138';
1711 --p_lines_rec.order_type_lookup_code(i) := NULL; -- NOT NULL VARCHAR2(25) Default as in PDOI (iP will not provide)QUANTITY
1712 --p_lines_rec.purchase_basis(i) := NULL; -- NOT NULL VARCHAR2(30) Default as in PDOI (iP will not provide)GOODS (must be GOODS?) (Open issue)
1713 --p_lines_rec.matching_basis(i) := NULL; -- NOT NULL VARCHAR2(30) Default as in PDOI (iP will not provide)QUANTITY
1714 --p_lines_rec.svc_amount_notif_sent(i) := NULL; -- VARCHAR2(1) Not present in 11.5.9. For 11.5.10, default NULL
1715 --p_lines_rec.svc_completion_notif_sent(i) := NULL; -- VARCHAR2(1) Not present in 11.5.9. For 11.5.10, default NULL
1716 --p_lines_rec.base_unit_price(i) := NULL; -- NUMBER Not present in 11.5.9. For 11.5.10, default same as unit price? (Open issue)
1717 --p_lines_rec.manual_price_change_flag(i) := NULL; -- VARCHAR2(1) NULL
1718 --p_lines_rec.retainage_rate(i) := NULL; -- NUMBER Not present in 11.5.9, 11.5.10
1719 --p_lines_rec.max_retainage_amount(i) := NULL; -- NUMBER Not present in 11.5.9, 11.5.10
1720 --p_lines_rec.progress_payment_rate(i) := NULL; -- NUMBER Not present in 11.5.9, 11.5.10
1721 --p_lines_rec.recoupment_rate(i) := NULL; -- NUMBER Not present in 11.5.9, 11.5.10
1722 --p_lines_rec.catalog_name(i) := NULL; -- VARCHAR2(255) Copy value from interface table
1723 --p_lines_rec.supplier_part_auxid(i) := NULL; -- VARCHAR2(255) Copy value from interface table
1724 --p_lines_rec.ip_category_id(i) := NULL; -- NUMBER Copy value from interface table
1725 --p_lines_rec.last_updated_program(i) := 'CATALOG_MIGRATION'; -- VARCHAR2(255) 'CATALOG_MIGRATION' (Open Issue) -- TODO: ONLY PRESENT IN TXN TABLES
1726 --p_lines_rec.advance_amount(i) := NULL; -- NUMBER Not present in 11.5.9, 11.5.10
1727 ------------------------------------------------------------------------
1728 p_lines_rec.ship_to_location_id(i) := l_hdr_ship_to_location_ids(i);
1729 l_progress := '139';
1730 p_lines_rec.ship_to_organization_id(i) := l_ship_to_org_ids(i);
1731 ------------------------------------------------------------------------
1732
1733 l_progress := '140';
1734 -- Bug 5032164: There is no need to get tax information for Blankets.
1735 p_lines_rec.taxable_flag(i) := NULL;
1736 p_lines_rec.tax_name(i) := NULL;
1737 p_lines_rec.tax_code_id(i) := NULL;
1738 p_lines_rec.tax_user_override_flag(i) := NULL;
1739
1740 <<END_OF_LINES_LOOP>>
1741 l_progress := '160';
1742 END LOOP;
1743
1744 l_progress := '170';
1745 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1746 EXCEPTION
1747 WHEN OTHERS THEN
1748 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1749 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1750 END default_lines;
1751
1752 --------------------------------------------------------------------------------
1753 --Start of Comments
1754 --Name: copy_info_from_hdr
1755 --Pre-reqs:
1756 -- The iP catalog data is populated in input pl/sql tables.
1757 --Modifies:
1758 -- a) FND_MSG_PUB on unhandled exceptions.
1759 --Locks:
1760 -- None.
1761 --Function:
1762 -- Copies the header level org_id and po_header_id into the line level.
1763 --Parameters:
1764 --IN:
1765 --p_hdr_org_ids
1766 -- A table of numbers containing the header level org_id's
1767 --IN/OUT:
1768 --x_lines_rec
1769 -- A record of plsql tables containing a batch of lines. The value of
1770 -- org_id will be populated from the header level.
1771 --
1772 --End of Comments
1773 --------------------------------------------------------------------------------
1774 PROCEDURE copy_info_from_hdr
1775 (
1776 p_hdr_org_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1777 p_po_header_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1778 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1779 )
1780 IS
1781 l_api_name CONSTANT VARCHAR2(30) := 'copy_info_from_hdr';
1782 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1783 l_progress VARCHAR2(3) := '000';
1784
1785 i NUMBER;
1786 BEGIN
1787 l_progress := '010';
1788 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1789
1790 l_progress := '020';
1791 FOR i IN 1 .. x_lines_rec.interface_line_id.COUNT
1792 LOOP
1793 IF (--x_lines_rec.has_errors(i) = 'N' AND
1794 p_hdr_org_ids.EXISTS(i)) THEN
1795 x_lines_rec.org_id(i) := p_hdr_org_ids(i);
1796 x_lines_rec.po_header_id(i) := p_po_header_ids(i);
1797 END IF;
1798 END LOOP;
1799
1800 l_progress := '030';
1801 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1802 EXCEPTION
1803 WHEN OTHERS THEN
1804 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1805 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1806 END copy_info_from_hdr;
1807
1808 --------------------------------------------------------------------------------
1809 --Start of Comments
1810 --Name: default_line_type
1811 --Pre-reqs:
1812 -- The iP catalog data is populated in input pl/sql tables.
1813 --Modifies:
1814 -- a) FND_MSG_PUB on unhandled exceptions.
1815 --Locks:
1816 -- None.
1817 --Function:
1818 -- Defaults the line type.
1819 --Parameters:
1820 --IN:
1821 --p_key
1822 -- Key used to access records in PO_SESSION_GT table.
1823 --p_hdr_org_ids
1824 -- A table of numbers containing the header level org_id's
1825 --IN/OUT:
1826 --x_lines_rec
1827 -- A record of plsql tables containing a batch of lines. The value of
1828 -- line_type_id will be populated.
1829 --
1830 --End of Comments
1831 --------------------------------------------------------------------------------
1832 PROCEDURE default_line_type
1833 (
1834 p_key IN PO_SESSION_GT.key%TYPE,
1835 p_hdr_org_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
1836 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1837 )
1838 IS
1839 l_api_name CONSTANT VARCHAR2(30) := 'default_line_type';
1840 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1841 l_progress VARCHAR2(3) := '000';
1842
1843 l_size NUMBER := x_lines_rec.interface_line_id.COUNT;
1844
1845 l_line_type_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1846 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1847 l_index NUMBER;
1848
1849 i NUMBER;
1850 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1851 BEGIN
1852 l_progress := '010';
1853 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1854
1855 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(x_lines_rec.interface_line_id.COUNT);
1856
1857 l_progress := '030';
1858 -- SQL What: Get the default line type from PSP. If the purchase_basis for
1859 -- this one is not GOODS, default line type to GOODS (seeded
1860 -- value of line_type_id = 1)
1861 -- SQL Why : It will be used to populate the OUT parameters.
1862 -- SQL Join: line_type_id, org_id
1863 FORALL i IN 1 .. x_lines_rec.interface_line_id.COUNT
1864 INSERT INTO PO_SESSION_GT(key,
1865 num1,
1866 num2)
1867 SELECT p_key,
1868 l_subscript_array(i),
1869 DECODE(POLTB.purchase_basis,
1870 'GOODS', PSP.line_type_id,
1871 1)
1872 FROM PO_SYSTEM_PARAMETERS_ALL PSP,
1873 PO_LINE_TYPES_B POLTB
1874 WHERE PSP.org_id = p_hdr_org_ids(i)
1875 AND PSP.line_type_id = POLTB.line_type_id
1876 --AND x_lines_rec.has_errors(i) = 'N'
1877 AND x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;
1881 l_progress := '030';
1878
1879 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1880
1882 -- SQL What: Transfer from session GT table to local arrays
1883 -- SQL Why : It will be used to populate the OUT parameters.
1884 -- SQL Join: key
1885 DELETE FROM po_session_gt
1886 WHERE key = p_key
1887 RETURNING num1, num2
1888 BULK COLLECT INTO l_indexes, l_line_type_ids;
1889
1890 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1891
1892 l_progress := '040';
1893 -- transfer from local arrays to OUT parameters
1894 FOR i IN 1 .. l_indexes.COUNT
1895 LOOP
1896 l_index := l_indexes(i);
1897 x_lines_rec.line_type_id(l_index) := l_line_type_ids(i);
1898 END LOOP;
1899
1900 l_progress := '050';
1901 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1905 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1906 END default_line_type;
1907
1908 --------------------------------------------------------------------------------
1909 --Start of Comments
1910 --Name: default_info_from_line_type
1911 --Pre-reqs:
1912 -- The iP catalog data is populated in input pl/sql tables.
1913 --Modifies:
1914 -- a) FND_MSG_PUB on unhandled exceptions.
1915 --Locks:
1916 -- None.
1917 --Function:
1918 -- Get default info from line type definition,
1919 -- The attributes we defaulted from line type include:
1920 -- order_type_lookup_code,
1921 -- purchase_basis,
1922 -- matching_basis
1923 -- The following default in PDOI, but in catalog migration iP will
1924 -- provide a value for these and therefore, they are not required
1925 -- to be defaulted.
1926 -- category_id,
1927 -- unit_of_measure,
1928 -- unit_price
1929 --Parameters:
1930 --IN:
1931 --p_key
1932 -- Key used to access records in PO_SESSION_GT table.
1933 --IN/OUT:
1934 --x_lines_rec
1935 -- A record of plsql tables containing a batch of lines. The value of
1936 -- order_type_lookup_code, purchase_basis and matching_basis will be populated.
1937 --
1938 --End of Comments
1939 --------------------------------------------------------------------------------
1940 PROCEDURE default_info_from_line_type
1941 (
1942 p_key IN PO_SESSION_GT.key%TYPE,
1943 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
1944 )
1945 IS
1946 l_api_name CONSTANT VARCHAR2(30) := 'default_info_from_line_type';
1947 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1948 l_progress VARCHAR2(3) := '000';
1949
1950 l_size NUMBER := x_lines_rec.interface_line_id.COUNT;
1951
1952 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1953 l_order_type_lookup_codes PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
1954 l_purchase_basis PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
1955 l_matching_basis PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
1956
1957 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1958 l_index NUMBER;
1959 i NUMBER;
1960 BEGIN
1961 l_progress := '010';
1962 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1963
1964 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(x_lines_rec.line_type_id.COUNT);
1965
1966 l_progress := '020';
1967 -- SQL What: Get the line type related info into the session GT table.
1968 -- SQL Why : It will be used to populate the OUT parameters.
1969 -- SQL Join: line_type_id
1970 FORALL i IN 1 .. x_lines_rec.line_type_id.COUNT
1971 INSERT INTO po_session_gt(key,
1972 num1,
1973 char1,
1974 char2,
1975 char3)
1976 SELECT p_key,
1977 l_subscript_array(i),
1978 order_type_lookup_code,
1979 purchase_basis,
1980 matching_basis
1981 FROM PO_LINE_TYPES_B
1982 WHERE line_type_id = x_lines_rec.line_type_id(i)
1983 AND x_lines_rec.line_type_id(i) IS NOT NULL
1984 --AND x_lines_rec.has_errors(i) = 'N'
1985 AND x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;
1986
1987 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1988
1989 l_progress := '030';
1990 -- SQL What: Transfer from session GT table to local arrays
1991 -- SQL Why : It will be used to populate the OUT parameters.
1992 -- SQL Join: key
1993 DELETE FROM po_session_gt
1994 WHERE key = p_key
1995 RETURNING num1, char1, char2, char3
1996 BULK COLLECT INTO l_indexes, l_order_type_lookup_codes,
1997 l_purchase_basis, l_matching_basis;
1998
1999 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2000
2001 l_progress := '040';
2002 -- transfer from local arrays to OUT parameters
2003 FOR i IN 1 .. l_indexes.COUNT
2004 LOOP
2005 l_index := l_indexes(i);
2006 x_lines_rec.order_type_lookup_code(l_index) := l_order_type_lookup_codes(i);
2007 x_lines_rec.purchase_basis(l_index) := l_purchase_basis(i);
2008 x_lines_rec.matching_basis(l_index) := l_matching_basis(i);
2009 END LOOP;
2010
2011 l_progress := '050';
2012 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2016 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2017 END default_info_from_line_type;
2018
2019 --------------------------------------------------------------------------------
2020 --Start of Comments
2021 --Name: default_info_from_item
2022 --Pre-reqs:
2023 -- The iP catalog data is populated in input pl/sql tables.
2024 --Modifies:
2025 -- a) FND_MSG_PUB on unhandled exceptions.
2026 --Locks:
2027 -- None.
2028 --Function:
2029 -- Get default information from item definitions
2030 --
2031 -- un_number_id
2032 -- hazard_class_id
2033 -- market_price
2034 -- inspection_required_flag
2035 --
2036 --Parameters:
2037 --IN:
2038 --p_key
2039 -- Key used to access records in PO_SESSION_GT table.
2040 --IN/OUT:
2041 --x_lines_rec
2042 -- A record of plsql tables containing a batch of lines. The value of
2043 -- above gived fields will be populated.
2044 --
2045 --End of Comments
2046 --------------------------------------------------------------------------------
2047 PROCEDURE default_info_from_item
2048 (
2049 p_key IN PO_SESSION_GT.key%TYPE,
2050 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
2051 )
2052 IS
2053 l_api_name CONSTANT VARCHAR2(30) := 'default_info_from_item';
2054 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2055 l_progress VARCHAR2(3) := '000';
2056
2057 l_size NUMBER := x_lines_rec.interface_line_id.COUNT;
2058
2059 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2060 l_un_number_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2061 l_hazard_class_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2062 l_market_prices PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2063 l_inspection_required_flags PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR1;
2064
2065 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2066 l_index NUMBER;
2067 i NUMBER;
2068 BEGIN
2069 l_progress := '010';
2070 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2071
2072 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(x_lines_rec.line_type_id.COUNT);
2073
2074 l_progress := '020';
2075 -- SQL What: Default information from item_id into the session GT table.
2076 -- SQL Why : It will be used to populate the OUT parameters.
2077 -- SQL Join: item_id
2078 FORALL i IN 1 .. x_lines_rec.line_type_id.COUNT
2079 INSERT INTO po_session_gt(key,
2080 num1,
2081 num2,
2082 num3,
2083 num4,
2084 char1)
2085 SELECT p_key,
2086 l_subscript_array(i),
2087 un_number_id,
2088 hazard_class_id,
2089 market_price,
2090 inspection_required_flag
2091 FROM MTL_SYSTEM_ITEMS_B
2092 WHERE inventory_item_id = x_lines_rec.item_id(i)
2093 AND organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
2094 AND x_lines_rec.item_id(i) IS NOT NULL
2095 --AND x_lines_rec.has_errors(i) = 'N'
2096 AND x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;
2097
2098 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2099
2100 l_progress := '030';
2101 -- SQL What: Transfer from session GT table to local arrays
2102 -- SQL Why : It will be used to populate the OUT parameters.
2103 -- SQL Join: key
2104 DELETE FROM po_session_gt
2105 WHERE key = p_key
2106 RETURNING num1, num2, num3, num4, char1
2107 BULK COLLECT INTO l_indexes, l_un_number_ids, l_hazard_class_ids,
2108 l_market_prices, l_inspection_required_flags;
2109
2110 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2111
2112 l_progress := '040';
2113 -- transfer from local arrays to OUT parameters
2114 FOR i IN 1 .. l_indexes.COUNT
2115 LOOP
2116 l_index := l_indexes(i);
2117 x_lines_rec.un_number_id(l_index) := l_un_number_ids(i);
2118 x_lines_rec.hazard_class_id(l_index) := l_hazard_class_ids(i);
2119 x_lines_rec.market_price(l_index) := l_market_prices(i);
2120 x_lines_rec.inspection_required_flag(l_index) := l_inspection_required_flags(i);
2121 END LOOP;
2122
2123 l_progress := '050';
2124 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2125 EXCEPTION
2126 WHEN OTHERS THEN
2127 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2128 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2129 END default_info_from_item;
2130
2131 --------------------------------------------------------------------------------
2132 --Start of Comments
2133 --Name: get_ship_to_org_from_location
2134 --Pre-reqs:
2135 -- The iP catalog data is populated in input pl/sql tables.
2136 --Modifies:
2137 -- a) FND_MSG_PUB on unhandled exceptions.
2138 --Locks:
2139 -- None.
2140 --Function:
2141 -- Gets the Ship-to-org associated with a given location
2142 --
2143 --Parameters:
2144 --IN:
2145 --p_key
2146 -- Key used to access records in PO_SESSION_GT table.
2147 --p_location_ids
2148 -- A plsql tables containing a set of location_ids
2149 --OUT:
2150 --x_org_ids
2151 -- A plsql tables containing the values of ship-to-org-id's for the
2152 -- corresponding location-id's.
2153 --
2154 --End of Comments
2155 --------------------------------------------------------------------------------
2156 PROCEDURE get_ship_to_org_from_location
2157 (
2158 p_key IN PO_SESSION_GT.key%TYPE,
2159 p_location_ids IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2160 x_org_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
2161 )
2162 IS
2163 l_api_name CONSTANT VARCHAR2(30) := 'get_ship_to_org_from_location';
2164 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2165 l_progress VARCHAR2(3) := '000';
2166
2167 l_size NUMBER := p_location_ids.COUNT;
2168
2169 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2170 l_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2171
2172 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2173 l_index NUMBER;
2174 i NUMBER;
2175 BEGIN
2176 l_progress := '010';
2177 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2178
2179 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_location_ids.COUNT);
2180
2181 IF (p_location_ids.COUNT > 0) THEN
2182 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_location_ids(1)='||p_location_ids(1)); END IF;
2183 END IF;
2184
2185 l_progress := '020';
2186 -- SQL What: Default information from ship_to_location into session GT table.
2187 -- SQL Why : It will be used to populate the OUT parameters.
2188 -- SQL Join: location_id
2189 FORALL i IN 1 .. p_location_ids.COUNT
2190 INSERT INTO po_session_gt(key,
2191 num1,
2192 num2)
2193 SELECT p_key,
2194 l_subscript_array(i),
2195 inventory_organization_id
2196 FROM HR_LOCATIONS_V
2197 WHERE location_id = p_location_ids(i)
2198 AND ship_to_site_flag = 'Y'
2199 AND p_location_ids(i) IS NOT NULL;
2200
2201 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2202
2203 l_progress := '030';
2204 -- SQL What: Transfer from session GT table to local arrays
2205 -- SQL Why : It will be used to populate the OUT parameters.
2206 -- SQL Join: key
2207 DELETE FROM po_session_gt
2208 WHERE key = p_key
2209 RETURNING num1, num2
2210 BULK COLLECT INTO l_indexes, l_org_ids;
2211
2212 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2213
2214 l_progress := '040';
2215 FOR i IN 1 .. p_location_ids.COUNT
2216 LOOP
2217 x_org_ids(i) := NULL;
2218 END LOOP;
2219
2220 -- transfer from local arrays to OUT parameters
2221 FOR i IN 1 .. l_indexes.COUNT
2222 LOOP
2223 l_index := l_indexes(i);
2224 x_org_ids(l_index) := l_org_ids(i);
2225 END LOOP;
2226
2227 l_progress := '050';
2228 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2229 EXCEPTION
2230 WHEN OTHERS THEN
2231 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2232 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2233 END get_ship_to_org_from_location;
2234
2235
2236 --------------------------------------------------------------------------------
2237 --Start of Comments
2238 --Name: default_hzd_cls_from_un_num
2239 --Pre-reqs:
2240 -- The iP catalog data is populated in input pl/sql tables.
2241 --Modifies:
2242 -- a) FND_MSG_PUB on unhandled exceptions.
2243 --Locks:
2244 -- None.
2245 --Function:
2246 -- Get default hazard_class_id for a given un_number
2247 --
2248 --Parameters:
2249 --IN:
2250 --p_key
2251 -- Key used to access records in PO_SESSION_GT table.
2252 --IN/OUT:
2253 --x_lines_rec
2254 -- A record of plsql tables containing a batch of lines. The value of
2255 -- hazard_class_id will be populated.
2256 --
2257 --End of Comments
2258 --------------------------------------------------------------------------------
2259 PROCEDURE default_hzd_cls_from_un_num
2260 (
2261 p_key IN PO_SESSION_GT.key%TYPE,
2262 x_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
2263 )
2264 IS
2265 l_api_name CONSTANT VARCHAR2(30) := 'default_hzd_cls_from_un_num';
2266 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2267 l_progress VARCHAR2(3) := '000';
2268
2269 l_size NUMBER := x_lines_rec.interface_line_id.COUNT;
2270
2271 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2272 l_hazard_class_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2273
2274 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2275 l_index NUMBER;
2276 i NUMBER;
2277 BEGIN
2278 l_progress := '010';
2279 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2280
2281 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(x_lines_rec.ship_to_location_id.COUNT);
2282
2283 l_progress := '020';
2284 -- SQL What: Default hazard_class_id into the session GT table.
2285 -- SQL Why : It will be used to populate the OUT parameters.
2286 -- SQL Join: un_number
2287 FORALL i IN 1 .. x_lines_rec.ship_to_location_id.COUNT
2288 INSERT INTO po_session_gt(key,
2289 num1,
2290 num2)
2291 SELECT p_key,
2292 l_subscript_array(i),
2293 hazard_class_id
2294 FROM PO_UN_NUMBERS_VAL_V
2295 WHERE un_number = x_lines_rec.un_number(i)
2296 AND x_lines_rec.un_number(i) IS NOT NULL
2297 AND x_lines_rec.hazard_class_id(i) IS NULL
2298 --AND x_lines_rec.has_errors(i) = 'N'
2299 AND x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;
2300
2301 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2302
2303 l_progress := '030';
2304 -- SQL What: Transfer from session GT table to local arrays
2305 -- SQL Why : It will be used to populate the OUT parameters.
2306 -- SQL Join: key
2307 DELETE FROM po_session_gt
2308 WHERE key = p_key
2309 RETURNING num1, num2
2310 BULK COLLECT INTO l_indexes, l_hazard_class_ids;
2311
2312 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2313
2314 l_progress := '040';
2315 -- transfer from local arrays to OUT parameters
2316 FOR i IN 1 .. l_indexes.COUNT
2317 LOOP
2318 l_index := l_indexes(i);
2319 x_lines_rec.hazard_class_id(l_index) := l_hazard_class_ids(i);
2320 END LOOP;
2321
2322 l_progress := '050';
2323 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2324 EXCEPTION
2325 WHEN OTHERS THEN
2326 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2327 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2328 END default_hzd_cls_from_un_num;
2329
2330 --------------------------------------------------------------------------------
2331 --Start of Comments
2332 --Name: default_hdr_info
2333 --Pre-reqs:
2334 -- The iP catalog data is populated in input pl/sql tables.
2335 --Modifies:
2336 -- a) FND_MSG_PUB on unhandled exceptions.
2337 --Locks:
2338 -- None.
2339 --Function:
2340 -- Get default value of the following header level fields for the
2341 -- respective lines.
2342 --
2343 -- org_id
2344 -- vendor_id
2345 -- vendor_site_id
2346 -- ship_to_location_id
2347 -- min_release_amount
2348 --
2349 --Parameters:
2350 --IN:
2351 --p_key
2352 -- Key used to access records in PO_SESSION_GT table.
2353 --p_lines_rec
2354 -- A record of plsql tables containing a batch of lines.
2355 --OUT:
2356 --x_org_ids
2357 -- A plsql table in which the org_id's will be populated.
2358 --x_vendor_ids
2359 -- A plsql table in which the vendor_id's will be populated.
2360 --x_vendor_site_ids
2361 -- A plsql table in which the vendor_site_id's will be populated.
2362 --x_ship_to_location_ids
2363 -- A plsql table in which the ship_to_location_id's will be populated.
2364 --x_min_release_amounts
2365 -- A plsql table in which the min_release_amount's will be populated.
2366 --End of Comments
2367 --------------------------------------------------------------------------------
2368 PROCEDURE default_hdr_info
2369 (
2370 p_key IN PO_SESSION_GT.key%TYPE,
2371 p_lines_rec IN PO_R12_CAT_UPG_PVT.record_of_lines_type,
2372 x_org_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2373 x_vendor_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2374 x_vendor_site_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2375 x_ship_to_location_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2376 x_min_release_amounts OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
2377 x_po_header_ids OUT NOCOPY PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
2378 )
2379 IS
2380 l_api_name CONSTANT VARCHAR2(30) := 'default_hdr_info';
2381 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2382 l_progress VARCHAR2(3) := '000';
2383
2384 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
2385
2386 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2387 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2388
2389 l_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2390 l_vendor_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2391 l_vendor_site_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2392 l_ship_to_location_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2393 l_min_release_amounts PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2394 l_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2395
2396 l_index NUMBER;
2397 i NUMBER;
2398 BEGIN
2399 l_progress := '010';
2400 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2401
2402 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.interface_line_id.COUNT);
2403
2404 l_progress := '020';
2405 -- SQL What: Default header related info into the session GT table.
2406 -- SQL Why : It will be used to populate the OUT parameters.
2407 -- SQL Join: interface_header_id, po_header_id
2408 FORALL i IN 1 .. p_lines_rec.interface_line_id.COUNT
2409 INSERT INTO po_session_gt(key,
2410 num1,
2411 num2,
2412 num3,
2413 num4,
2414 num5,
2415 num6,
2416 num7)
2417 SELECT p_key,
2418 l_subscript_array(i),
2419 POH.org_id,
2420 POH.vendor_id,
2421 POH.vendor_site_id,
2422 POH.ship_to_location_id,
2423 POH.min_release_amount,
2424 POH.po_header_id
2425 FROM PO_HEADERS_ALL POH,
2426 PO_HEADERS_INTERFACE POHI
2427 WHERE POH.po_header_id = POHI.po_header_id
2428 AND POHI.interface_header_id = p_lines_rec.interface_header_id(i)
2429 --AND p_lines_rec.has_errors(i) = 'N'
2430 AND p_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;
2431
2432 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2433
2434 l_progress := '030';
2435 -- SQL What: Transfer from session GT table to local arrays
2436 -- SQL Why : It will be used to populate the OUT parameters.
2437 -- SQL Join: key
2438 DELETE FROM po_session_gt
2439 WHERE key = p_key
2440 RETURNING num1, num2, num3, num4, num5, num6, num7
2441 BULK COLLECT INTO l_indexes, l_org_ids, l_vendor_ids,
2442 l_vendor_site_ids, l_ship_to_location_ids,
2443 l_min_release_amounts, l_po_header_ids;
2444
2445 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2446
2447 l_progress := '040';
2448
2449 FOR i IN 1 .. p_lines_rec.interface_line_id.COUNT
2450 LOOP
2451 x_org_ids(i) := NULL;
2452 x_vendor_ids(i) := NULL;
2453 x_vendor_site_ids(i) := NULL;
2454 x_ship_to_location_ids(i) := NULL;
2455 x_min_release_amounts(i) := NULL;
2456 x_po_header_ids(i) := NULL;
2457 END LOOP;
2458
2459 -- transfer from local arrays to OUT parameters
2460 FOR i IN 1 .. l_indexes.COUNT
2461 LOOP
2462 l_index := l_indexes(i);
2463 x_org_ids(l_index) := l_org_ids(i);
2464 x_vendor_ids(l_index) := l_vendor_ids(i);
2465 x_vendor_site_ids(l_index) := l_vendor_site_ids(i);
2466 x_ship_to_location_ids(l_index) := l_ship_to_location_ids(i);
2467 x_min_release_amounts(l_index) := l_min_release_amounts(i);
2468 x_po_header_ids(l_index) := l_po_header_ids(i);
2469 END LOOP;
2470
2471 l_progress := '050';
2472 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2476 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2477 END default_hdr_info;
2478
2479 END PO_R12_CAT_UPG_DEF_PVT;
[Home] [Help]