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