[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_RPT_SPR_PVT
Source
1 PACKAGE BODY jmf_shikyu_rpt_spr_pvt AS
2 --$Header: JMFVSPRB.pls 120.13 2006/11/09 01:13:33 rajkrish noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFVSPRB.pls |
10 --| |
11 --| DESCRIPTION: Body file of the package for creating temporary |
12 --| data for the SHIKYU Subcontracting Order Report. |
13 --| |
14 --| FUNCTION/PROCEDURE: spr_load_subcontracting_po |
15 --| |
16 --| HISTORY: |
17 --| 28-MAY-2005 fwang Created. |
18 --| 18-NOV-2005 shu Added code for setting request completed |
19 --| with warning if SHIKYU profile is disable |
20 --| 01-Dec-2005 shzhao Changed the dynamic sql |
21 --| 02-Dec-2005 shzhao Changed the blanket po logic in PROCEDURE |
22 --| spr_load_subcontracting_po |
23 --| 05-Dec-2005 shzhao Did a small change on PROCEDURE |
24 --| spr_load_subcontracting_po for to_char |
25 --| 12-Dec-2005 shzhao did a small change on PROCEDURE |
26 --| spr_load_subcontracting_po for address |
27 --| 10-Jan-2006 shzhao Updated the logging code. |
28 --| 12-Apr-2006 the2 Fixed bug 5151544: The Parameter selection |
29 --| "Buyer Name and Employee no" does not work. |
30 --| 16-Jun-2006 the2 Fixed bug 5197398: Add Project_num and |
31 --| task_num to subcontracting order report. |
32 --| 23-Jun-2006 the2 Fixed bug 5352338: adjust cursor |
33 --| l_cur_get_subcontracting_po |
34 --| 26-Jun-2006 the2 Adjust cursor l_cur_get_subcontracting_po to|
35 --| get project_id and task_id from |
36 --| jmf_subcontract_orders table |
37 --| 26-Jun-2006 the2 Adjust cursor l_cur_get_subcontracting_po to|
38 --| merge project_num1 and project_num2 to just |
39 --| one project_num |
40 --+===========================================================================+
41
42 --=============================================
43 -- CONSTANTS
44 --=============================================
45 g_pkg_name CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_RPT_SPR_PVT';
46 g_module_prefix CONSTANT VARCHAR2(50) := 'jmf.plsql.' || g_pkg_name || '.';
47
48 --=============================================
49 -- GLOBAL VARIABLES
50 --=============================================
51
52 g_debug_level NUMBER := fnd_log.g_current_runtime_level;
53 g_proc_level NUMBER := fnd_log.level_procedure;
54 --g_unexp_level NUMBER := fnd_log.level_unexpected;
55 g_excep_level NUMBER := fnd_log.level_exception;
56
57 --========================================================================
58 -- PROCEDURE : spr_load_subcontracting_po PUBLIC
59 -- PARAMETERS: p_ou_id operating unit id
60 -- : p_report_type print selection
61 -- : p_po_num_from po number from
62 -- : p_po_num_to po number to
63 -- : p_agent_name_num agent number
64 -- : p_cancel_line print cancel line
65 -- : p_approved_flag approved
66 -- COMMENT : get shikyu subcontracting data and insert into temp table
67 -- PRE-COND :
68 -- EXCEPTIONS:
69 --========================================================================
70
71 PROCEDURE spr_load_subcontracting_po
72 ( p_ou_id IN NUMBER
73 , p_report_type IN VARCHAR2
74 , p_po_num_from IN VARCHAR2
75 , p_po_num_to IN VARCHAR2
76 , p_agent_name_num IN NUMBER
77 , p_cancel_line IN VARCHAR2
78 , p_approved_flag IN VARCHAR2
79 )
80 IS
81 l_api_name CONSTANT VARCHAR2(30) := 'spr_load_subcontracting_po';
82
83 l_subcontract_po_shipment_id jmf_shikyu_spr_rpt_temp.SUBCONTRACT_PO_SHIPMENT_ID%TYPE ;
84 l_shipment_type jmf_shikyu_spr_rpt_temp.shipment_type%TYPE;
85 l_report_title_num jmf_shikyu_spr_rpt_temp.report_title_num%TYPE ;
86 l_po_number jmf_shikyu_spr_rpt_temp.sub_po_num%TYPE ;
87 l_agent_name jmf_shikyu_spr_rpt_temp.agent_name%TYPE ;
88 l_line_num jmf_shikyu_spr_rpt_temp.SUB_PO_LINE_NUM%TYPE ;
89 l_shipment_num jmf_shikyu_spr_rpt_temp.SHIP_OR_RELEASE_NUM%TYPE ;
90 l_revision_num jmf_shikyu_spr_rpt_temp.REV_NUM%TYPE ;
91 l_creation_date jmf_shikyu_spr_rpt_temp.CREATION_DATE%TYPE ;
92 l_created_by_name jmf_shikyu_spr_rpt_temp.CREATED_BY_NAME%TYPE ;
93 l_revised_date jmf_shikyu_spr_rpt_temp.REVISED_DATE%TYPE ;
94 l_last_updated_by_name jmf_shikyu_spr_rpt_temp.REVISED_BY_NAME%TYPE ;
95 l_vendor_site_code jmf_shikyu_spr_rpt_temp.VENDOR_SITE_CODE%TYPE ;
96 l_vendor_address_line1 jmf_shikyu_spr_rpt_temp.VENDOR_SITE_ADDRESS1%TYPE ;
97 l_vendor_address_line2 jmf_shikyu_spr_rpt_temp.VENDOR_SITE_ADDRESS2%TYPE ;
98 l_vendor_address_line3 jmf_shikyu_spr_rpt_temp.VENDOR_SITE_ADDRESS3%TYPE ;
99 l_shipto_location_code jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_CODE%TYPE ;
100 l_shipto_address_line_1 jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_ADDRESS1%TYPE ;
101 l_shipto_address_line_2 jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_ADDRESS2%TYPE ;
102 l_shipto_address_line_3 jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_ADDRESS3%TYPE ;
103 l_billto_location_code jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_CODE%TYPE ;
104 l_billto_address_line_1 jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_ADDRESS1%TYPE ;
105 l_billto_address_line_2 jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_ADDRESS2%TYPE ;
106 l_billto_address_line_3 jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_ADDRESS3%TYPE ;
107
108 l_vendor_city_state_zip jmf_shikyu_spr_rpt_temp.VENDOR_CITY_STATE_ZIP%TYPE ;
109 l_vendor_country jmf_shikyu_spr_rpt_temp.VENDOR_COUNTRY%TYPE ;
110 l_ship_to_site_postal_code jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_POSTAL_CODE%TYPE ;
111 l_ship_to_site_country jmf_shikyu_spr_rpt_temp.SHIP_TO_SITE_COUNTRY%TYPE ;
112 l_billto_site_postal_code jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_POSTAL_CODE%TYPE ;
113 l_billto_site_country jmf_shikyu_spr_rpt_temp.BILL_TO_SITE_COUNTRY%TYPE ;
114
115 l_customer_num jmf_shikyu_spr_rpt_temp.CUSTOMER_NUM%TYPE ;
116 l_supplier_num jmf_shikyu_spr_rpt_temp.SUPPLIER_NUM%TYPE ;
117 l_pay_term jmf_shikyu_spr_rpt_temp.PAY_TERM%TYPE ;
118 l_FREIGHT_TERM jmf_shikyu_spr_rpt_temp.FREIGHT_TERM%TYPE ;
119 l_FOB_TYPE jmf_shikyu_spr_rpt_temp.FOB_TYPE%TYPE ;
120 l_SHIPPING_CONTROL jmf_shikyu_spr_rpt_temp.SHIPPING_CONTROL%TYPE ;
121 l_SHIP_VIA jmf_shikyu_spr_rpt_temp.SHIP_VIA%TYPE ;
122 l_CONFIRM_TO_NAME jmf_shikyu_spr_rpt_temp.CONFIRM_TO_NAME%TYPE ;
123 l_CONFIRM_TO_TELEPHONE jmf_shikyu_spr_rpt_temp.CONFIRM_TO_TELEPHONE%TYPE ;
124 l_REQUESTER jmf_shikyu_spr_rpt_temp.REQUESTER%TYPE ;
125 l_NOTES jmf_shikyu_spr_rpt_temp.NOTES%TYPE ;
126 l_project_num jmf_shikyu_spr_rpt_temp.PROJECT_NUM%TYPE ;
127 l_task_num jmf_shikyu_spr_rpt_temp.TASK_NUM%TYPE ;
128 l_ITEM_NUM jmf_shikyu_spr_rpt_temp.ITEM_NUM%TYPE ;
129 l_ITEM_DESC jmf_shikyu_spr_rpt_temp.ITEM_DESC%TYPE ;
130 l_NEED_BY_DATE jmf_shikyu_spr_rpt_temp.NEED_BY_DATE%TYPE ;
131 l_PROMISED_DATE jmf_shikyu_spr_rpt_temp.PROMISED_DATE%TYPE ;
132 l_QUANTITY jmf_shikyu_spr_rpt_temp.QUANTITY%TYPE ;
133 l_UOM jmf_shikyu_spr_rpt_temp.UOM%TYPE ;
134 l_ITEM_PRICE jmf_shikyu_spr_rpt_temp.ITEM_PRICE%TYPE ;
135 l_TAXABLE_FLAG jmf_shikyu_spr_rpt_temp.TAXABLE_FLAG%TYPE ;
136 l_AMOUNT jmf_shikyu_spr_rpt_temp.AMOUNT%TYPE ;
137 l_AGENT_ID jmf_shikyu_spr_rpt_temp.AGENT_ID%TYPE ;
138 l_VENDOR_ID jmf_shikyu_spr_rpt_temp.VENDOR_ID%TYPE ;
139 l_ITEM_ID jmf_shikyu_spr_rpt_temp.ITEM_ID%TYPE ;
140
141 l_print_count po_headers_all.print_count%TYPE;
142 l_printed_date po_headers_all.printed_date%TYPE;
143 l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
144 l_po_header_id po_headers_all.po_header_id%TYPE;
145 l_po_line_id po_lines_all.po_line_id%TYPE;
146
147 l_cancel_flag po_lines_all.cancel_flag%TYPE;
148 l_po_release_id po_line_locations_all.po_release_id%TYPE;
149
150 l_manual_po_num_type PO_SYSTEM_PARAMETERS.manual_po_num_type%TYPE;
151
152 CURSOR l_cur_get_blanket_po (
153 lp_org_id NUMBER
154 , lp_po_header_id NUMBER
155 , lp_po_line_id NUMBER
156 , lp_po_release_id NUMBER ) IS
157 SELECT 'Subcontracting Order ' || h.segment1 || '-' || to_char(l.line_num) || '-' ||
158 to_char(re.release_num) || ',' || to_char(h.revision_num) REPORT_TITLE_NUM
159 ,re.release_num
160 FROM po_headers_all h
161 , po_lines_all l
162 , po_line_locations_all loc
163 , po_releases_all re
164 WHERE re.po_release_id = loc.po_release_id
165 AND loc.po_line_id = lp_po_line_id
166 AND l.po_header_id = lp_po_header_id
167 AND h.org_id = lp_org_id
168 AND h.po_header_id = l.po_header_id
169 AND l.po_line_id = loc.po_line_id
170 AND re.po_release_id = lp_po_release_id ;
171
172 CURSOR l_cur_get_subcontracting_po(
173 lp_org_id NUMBER
174 , lp_po_num_from VARCHAR2
175 , lp_po_num_to VARCHAR2
176 , lp_agent_name_num NUMBER
177 , lp_approved_flag VARCHAR2
178 , lp_manual_po_num_type VARCHAR2) IS
179 SELECT sub.subcontract_po_shipment_id
180 , loc.shipment_type
181 , 'Subcontracting Order ' || h.segment1 || '-' || l.line_num || '-' ||
182 loc.shipment_num || ',' || h.revision_num REPORT_TITLE_NUM
183 , h.segment1
184 , (SELECT hremp.full_name
185 FROM hr_employees hremp
186 WHERE hremp.employee_id(+) = h.agent_id
187 AND h.agent_id = nvl(lp_agent_name_num,h.agent_id)) AGENT_NAME
188 , l.line_num
189 , loc.shipment_num
190 , h.revision_num
191 , h.creation_date
192 , (SELECT fnd_user.user_name
193 FROM fnd_user
194 WHERE fnd_user.user_id = h.created_by) created_by
195 , h.revised_date
196 , (SELECT fnd_user.user_name
197 FROM fnd_user
198 WHERE fnd_user.user_id = h.last_updated_by) last_updated_by
199 , pvsa.vendor_site_code
200 , pvsa.address_line1
201 , pvsa.address_line2
202 , pvsa.address_line3
203 , pvsa.city || ' ' || pvsa.state || ' ' || pvsa.zip
204 , ( SELECT nls_territory
205 FROM FND_TERRITORIES
206 WHERE territory_code= pvsa.country)
207 , hrloc.location_code
208 , hrloc.address_line_1
209 , hrloc.address_line_2
210 , hrloc.address_line_3
211 , hrloc.region_1 || ' ' || hrloc.region_2 || ' ' || hrloc.postal_code
212 , ( SELECT nls_territory
213 FROM FND_TERRITORIES
214 WHERE territory_code= hrloc.country)
215 , hrloc2.location_code
216 , hrloc2.address_line_1
217 , hrloc2.address_line_2
218 , hrloc2.address_line_3
219 , hrloc2.region_1 || ' ' || hrloc2.region_2 || ' ' || hrloc2.postal_code
220 , ( SELECT nls_territory
221 FROM FND_TERRITORIES
222 WHERE territory_code= hrloc2.country)
223 , (SELECT po_vendors.customer_num
224 FROM po_vendors
225 WHERE po_vendors.vendor_id = h.vendor_id)
226 , (SELECT po_vendors.segment1
227 FROM po_vendors
228 WHERE po_vendors.vendor_id = h.vendor_id)
229 , (SELECT ap_terms_tl.NAME
230 FROM ap_terms_tl
231 WHERE ap_terms_tl.term_id = h.terms_id AND
232 ap_terms_tl.LANGUAGE = userenv('LANG'))
233 , (SELECT po_lookup_codes.displayed_field
234 FROM po_lookup_codes
235 WHERE po_lookup_codes.lookup_type = 'FREIGHT TERMS' AND
236 po_lookup_codes.lookup_code = h.freight_terms_lookup_code)
237 , (SELECT po_lookup_codes.displayed_field
238 FROM po_lookup_codes
239 WHERE po_lookup_codes.lookup_type = 'FOB' AND
240 po_lookup_codes.lookup_code = h.fob_lookup_code)
241 , (SELECT po_lookup_codes.displayed_field
242 FROM po_lookup_codes
243 WHERE po_lookup_codes.lookup_type = 'SHIPPING CONTROL' AND
244 po_lookup_codes.lookup_code = h.shipping_control)
245 , (SELECT org_freight_tl.freight_code_tl
246 FROM org_freight_tl
247 WHERE org_freight_tl.freight_code = h.ship_via_lookup_code AND
248 org_freight_tl.LANGUAGE = userenv('LANG') AND
249 org_freight_tl.organization_id = l.org_id)
250 , pvc.first_name || ' ' || pvc.last_name
251 , pvc.phone
252 , decode((SELECT COUNT(d.po_distribution_id)
253 FROM po_distributions_all d
254 WHERE d.line_location_id = sub.subcontract_po_shipment_id),
255 1,
256 ( SELECT hremp.full_name || ' ' || hrloc.location_code
257 FROM po_distributions_all d,
258 hr_locations_all hrloc,
259 hr_employees hremp
260 WHERE hrloc.location_id(+) = d.deliver_to_location_id AND
261 hremp.employee_id(+) = d.deliver_to_person_id AND
262 d.line_location_id = sub.subcontract_po_shipment_id),
263 -- 'MANY REQUESTOR')
264 '%M' )
265 , l.note_to_vendor
266 , proj.project_number
267 , pt.task_number
268 , loc.shipment_num
269 , mtl.segment1
270 , mtl.description
271 , loc.need_by_date
272 , loc.promised_date
273 , loc.quantity
274 , l.unit_meas_lookup_code
275 , sub.osa_item_price
276 , l.taxable_flag
277 , l.unit_price * l.quantity
278 , h.agent_id
279 , h.vendor_id
280 , l.item_id
281 , h.print_count
282 , h.printed_date
283 , l.cancel_flag
284 , h.type_lookup_code
285 , h.po_header_id
286 , l.po_line_id
287 , loc.po_release_id
288 FROM po_headers_all h,
289 po_lines_all l,
290 po_line_locations_all loc,
291 jmf_subcontract_orders sub,
292 mtl_system_items_vl mtl,
293 po_vendor_sites_all pvsa,
294 hr_locations_all hrloc,
295 hr_locations_all hrloc2,
296 po_vendor_contacts pvc,
297 (select distinct project_id, segment1 AS project_number
298 from pa_projects_all
299 union
300 select distinct project_id, project_number
301 from pjm_seiban_numbers) proj,
302 pa_tasks pt
303 WHERE --h.type_lookup_code IN ('STANDARD')
304 h.po_header_id = sub.subcontract_po_header_id
305 AND l.po_line_id = sub.subcontract_po_line_id
306 AND loc.line_location_id = sub.subcontract_po_shipment_id
307 AND pvsa.vendor_site_id(+) = h.vendor_site_id
308 AND hrloc.location_id(+) = h.ship_to_location_id
309 AND hrloc2.location_id(+) = h.bill_to_location_id
310 AND pvc.vendor_contact_id(+) = h.vendor_contact_id
311 AND mtl.inventory_item_id = l.item_id
312 AND sub.oem_organization_id = mtl.organization_id
313 AND l.org_id = lp_org_id
314 AND ((decode(lp_manual_po_num_type,
315 'NUMERIC',
316 decode(rtrim(h.segment1, '0123456789'),
317 NULL,
318 to_number(h.segment1),
319 -1),
320 null) BETWEEN
321 decode(lp_manual_po_num_type,
322 'NUMERIC',
323 decode(rtrim(nvl(lp_po_num_from, h.segment1), '0123456789'),
324 NULL,
325 to_number(nvl(lp_po_num_from, h.segment1)),
326 -1),
327 null) AND
328 decode(lp_manual_po_num_type,
329 'NUMERIC',
330 decode(rtrim(nvl(lp_po_num_to, h.segment1), '0123456789'),
331 NULL,
332 to_number(nvl(lp_po_num_to, h.segment1)),
333 -1),
334 null)) OR
335 (h.segment1 BETWEEN decode(lp_manual_po_num_type,
336 'ALPHANUMERIC',
337 nvl(lp_po_num_from, h.segment1),
338 null) AND
339 decode(lp_manual_po_num_type,
340 'ALPHANUMERIC',
341 nvl(lp_po_num_to, h.segment1),
342 null)))
343 AND h.approved_flag = NVL(lp_approved_flag,h.approved_flag)
344 AND h.agent_id = NVL(lp_agent_name_num,h.agent_id)
345 AND proj.project_id(+) = sub.project_id
346 AND pt.task_id(+) = sub.task_id;
347
348
349 BEGIN
350 -- g_debug_level := fnd_log.g_current_runtime_level;
351
352 IF (g_proc_level >= g_debug_level)
353 THEN
354 fnd_log.STRING(g_proc_level
355 ,g_module_prefix || l_api_name || '.begin'
356 ,NULL);
357 END IF;
358
359 DELETE FROM jmf_shikyu_spr_rpt_temp;
360
361 commit;
362
363 SELECT psp.manual_po_num_type manual_po_num_type
364 INTO l_manual_po_num_type
365 FROM po_system_parameters_all psp
366 where org_id = p_ou_id;
367
368 /* EXCEPTION
369 WHEN no_data_found THEN
370 l_manual_po_num_type := 'ALPHANUMERIC'; */
371
372 OPEN l_cur_get_subcontracting_po
373 ( p_ou_id
374 , p_po_num_from
375 , p_po_num_to
376 , p_agent_name_num
377 , p_approved_flag
378 , l_manual_po_num_type
379 );
380
381 LOOP
382 FETCH l_cur_get_subcontracting_po
383 INTO l_subcontract_po_shipment_id
384 ,l_shipment_type
385 ,l_report_title_num
386 ,l_po_number
387 ,l_agent_name
388 ,l_line_num
389 ,l_shipment_num
390 ,l_revision_num
391 ,l_creation_date
392 ,l_created_by_name
393 ,l_revised_date
394 ,l_last_updated_by_name
395 ,l_vendor_site_code
396 ,l_vendor_address_line1
397 ,l_vendor_address_line2
398 ,l_vendor_address_line3
399 ,l_vendor_city_state_zip
400 ,l_vendor_country
401 ,l_shipto_location_code
402 ,l_shipto_address_line_1
403 ,l_shipto_address_line_2
404 ,l_shipto_address_line_3
405 ,l_ship_to_site_postal_code
406 ,l_ship_to_site_country
407 ,l_billto_location_code
408 ,l_billto_address_line_1
409 ,l_billto_address_line_2
410 ,l_billto_address_line_3
411 ,l_billto_site_postal_code
412 ,l_billto_site_country
413 ,l_customer_num
414 ,l_supplier_num
415 ,l_pay_term
416 ,l_FREIGHT_TERM
417 ,l_FOB_TYPE
418 ,l_SHIPPING_CONTROL
419 ,l_SHIP_VIA
420 ,l_CONFIRM_TO_NAME
421 ,l_CONFIRM_TO_TELEPHONE
422 ,l_REQUESTER
423 ,l_NOTES
424 ,l_project_num
425 ,l_task_num
426 ,l_SHIPMENT_NUM
427 ,l_ITEM_NUM
428 ,l_ITEM_DESC
429 ,l_NEED_BY_DATE
430 ,l_PROMISED_DATE
431 ,l_QUANTITY
432 ,l_UOM
433 ,l_ITEM_PRICE
434 ,l_TAXABLE_FLAG
435 ,l_AMOUNT
436 ,l_AGENT_ID
437 ,l_VENDOR_ID
438 ,l_ITEM_ID
439 ,l_print_count
440 ,l_printed_date
441 ,l_cancel_flag
442 ,l_type_lookup_code
443 ,l_po_header_id
444 ,l_po_line_id
445 ,l_po_release_id ;
446
447 EXIT WHEN l_cur_get_subcontracting_po%NOTFOUND;
448
449 --p_report_type
450 IF ( p_report_type = 'N' AND nvl(l_print_count,0) = 0) OR
451 ( p_report_type = 'C' AND l_revised_date > l_printed_date) OR
452 ( nvl( p_report_type,'R') = 'R') THEN
453
454 -- cancel flag
455 IF (NVL(p_cancel_line ,'Y') = 'N' AND l_cancel_flag ='N') OR
456 ( NVL(p_cancel_line ,'Y') = 'Y') THEN
457
458 -- need_by_date and promised_date
459 IF (l_NEED_BY_DATE IS NOT NULL) AND (l_PROMISED_DATE IS NOT NULL) THEN
460 l_PROMISED_DATE :=NULL;
461 END IF;
462
463 -- balnket PO
464 IF l_type_lookup_code = 'BLANKET' THEN
465
466 OPEN l_cur_get_blanket_po (
467 p_ou_id
468 , l_po_header_id
469 , l_po_line_id
470 , l_po_release_id) ;
471
472 LOOP
473 FETCH l_cur_get_blanket_po
474 INTO l_report_title_num
475 , l_shipment_num ;
476
477
478 EXIT WHEN l_cur_get_blanket_po%NOTFOUND;
479 END LOOP;
480 CLOSE l_cur_get_blanket_po;
481
482 END IF;
483
484 INSERT INTO jmf_shikyu_spr_rpt_temp
485 ( subcontract_po_shipment_id
486 , shipment_type
487 , report_title_num
488 , sub_po_num
489 , agent_name
490 , sub_po_line_num
491 , ship_or_release_num
492 , rev_num
493 , creation_date
494 , created_by_name
495 , revised_date
496 , REVISED_BY_NAME
497 , vendor_site_code
498 , VENDOR_SITE_ADDRESS1
499 , VENDOR_SITE_ADDRESS2
500 , VENDOR_SITE_ADDRESS3
501 , VENDOR_CITY_STATE_ZIP
502 , VENDOR_COUNTRY
503 , SHIP_TO_SITE_CODE
504 , SHIP_TO_SITE_ADDRESS1
505 , SHIP_TO_SITE_ADDRESS2
506 , SHIP_TO_SITE_ADDRESS3
507 , SHIP_TO_SITE_POSTAL_CODE
508 , SHIP_TO_SITE_COUNTRY
509 , BILL_TO_SITE_CODE
510 , BILL_TO_SITE_ADDRESS1
511 , BILL_TO_SITE_ADDRESS2
512 , BILL_TO_SITE_ADDRESS3
513 , BILL_TO_SITE_POSTAL_CODE
514 , BILL_TO_SITE_COUNTRY
515 , customer_num
516 , supplier_num
517 , pay_term
518 , FREIGHT_TERM
519 , FOB_TYPE
520 , SHIPPING_CONTROL
521 , SHIP_VIA
522 , CONFIRM_TO_NAME
523 , CONFIRM_TO_TELEPHONE
524 , REQUESTER
525 , NOTES
526 , PROJECT_NUM
527 , TASK_NUM
528 , SHIPMENT_NUM
529 , ITEM_NUM
530 , ITEM_DESC
531 , NEED_BY_DATE
532 , PROMISED_DATE
533 , QUANTITY
534 , UOM
535 , ITEM_PRICE
536 , TAXABLE_FLAG
537 , AMOUNT
538 , AGENT_ID
539 , VENDOR_ID
540 , ITEM_ID )
541 VALUES
542 (l_subcontract_po_shipment_id
543 ,l_shipment_type
544 ,l_report_title_num
545 ,l_po_number
546 ,l_agent_name
547 ,l_line_num
548 ,l_shipment_num
549 ,l_revision_num
550 ,l_creation_date
551 ,l_created_by_name
552 ,l_revised_date
553 ,l_last_updated_by_name
554 ,l_vendor_site_code
555 ,l_vendor_address_line1
556 ,l_vendor_address_line2
557 ,l_vendor_address_line3
558 ,l_vendor_city_state_zip
559 ,l_vendor_country
560 ,l_shipto_location_code
561 ,l_shipto_address_line_1
562 ,l_shipto_address_line_2
563 ,l_shipto_address_line_3
564 ,l_ship_to_site_postal_code
565 ,l_ship_to_site_country
566 ,l_billto_location_code
567 ,l_billto_address_line_1
568 ,l_billto_address_line_2
569 ,l_billto_address_line_3
570 ,l_billto_site_postal_code
571 ,l_billto_site_country
572 ,l_customer_num
573 ,l_supplier_num
574 ,l_pay_term
575 ,l_FREIGHT_TERM
576 ,l_FOB_TYPE
577 ,l_SHIPPING_CONTROL
578 ,l_SHIP_VIA
579 ,l_CONFIRM_TO_NAME
580 ,l_CONFIRM_TO_TELEPHONE
581 ,l_REQUESTER
582 ,l_NOTES
583 ,l_project_num
584 ,l_task_num
585 ,l_SHIPMENT_NUM
586 ,l_ITEM_NUM
587 ,l_ITEM_DESC
588 ,l_NEED_BY_DATE
589 ,l_PROMISED_DATE
590 ,l_QUANTITY
591 ,l_UOM
592 ,l_ITEM_PRICE
593 ,l_TAXABLE_FLAG
594 ,l_AMOUNT
595 ,l_AGENT_ID
596 ,l_VENDOR_ID
597 ,l_ITEM_ID);
598 END IF;
599
600 END IF;
601
602 COMMIT;
603
604 END LOOP;
605
606 CLOSE l_cur_get_subcontracting_po;
607
608
609 EXCEPTION
610 WHEN no_data_found THEN
611 IF (g_excep_level >= g_debug_level)
612 THEN
613 fnd_log.STRING(g_excep_level
614 ,g_module_prefix || l_api_name || '.exception'
615 ,'no_data_found');
616 END IF;
617 WHEN OTHERS THEN
618
619 ROLLBACK;
620
621 IF (g_excep_level >= g_debug_level)
622 THEN
623 fnd_log.STRING(g_excep_level
624 ,g_module_prefix || l_api_name || '.exception'
625 ,SQLERRM);
626 END IF;
627
628 END spr_load_subcontracting_po;
629
630 END jmf_shikyu_rpt_spr_pvt;