[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by_name jmf_shikyu_spr_rpt_temp.REVISED_BY_NAME%TYPE ;
SELECT 'Subcontracting Order ' || h.segment1 || '-' || to_char(l.line_num) || '-' ||
to_char(re.release_num) || ',' || to_char(h.revision_num) REPORT_TITLE_NUM
,re.release_num
FROM po_headers_all h
, po_lines_all l
, po_line_locations_all loc
, po_releases_all re
WHERE re.po_release_id = loc.po_release_id
AND loc.po_line_id = lp_po_line_id
AND l.po_header_id = lp_po_header_id
AND h.org_id = lp_org_id
AND h.po_header_id = l.po_header_id
AND l.po_line_id = loc.po_line_id
AND re.po_release_id = lp_po_release_id ;
SELECT sub.subcontract_po_shipment_id
, loc.shipment_type
, 'Subcontracting Order ' || h.segment1 || '-' || l.line_num || '-' ||
loc.shipment_num || ',' || h.revision_num REPORT_TITLE_NUM
, h.segment1
, (SELECT hremp.full_name
FROM hr_employees hremp
WHERE hremp.employee_id(+) = h.agent_id
AND h.agent_id = nvl(lp_agent_name_num,h.agent_id)) AGENT_NAME
, l.line_num
, loc.shipment_num
, h.revision_num
, h.creation_date
, (SELECT fnd_user.user_name
FROM fnd_user
WHERE fnd_user.user_id = h.created_by) created_by
, h.revised_date
, (SELECT fnd_user.user_name
FROM fnd_user
WHERE fnd_user.user_id = h.last_updated_by) last_updated_by
, pvsa.vendor_site_code
, pvsa.address_line1
, pvsa.address_line2
, pvsa.address_line3
, pvsa.city || ' ' || pvsa.state || ' ' || pvsa.zip
, ( SELECT nls_territory
FROM FND_TERRITORIES
WHERE territory_code= pvsa.country)
, hrloc.location_code
, hrloc.address_line_1
, hrloc.address_line_2
, hrloc.address_line_3
, hrloc.region_1 || ' ' || hrloc.region_2 || ' ' || hrloc.postal_code
, ( SELECT nls_territory
FROM FND_TERRITORIES
WHERE territory_code= hrloc.country)
, hrloc2.location_code
, hrloc2.address_line_1
, hrloc2.address_line_2
, hrloc2.address_line_3
, hrloc2.region_1 || ' ' || hrloc2.region_2 || ' ' || hrloc2.postal_code
, ( SELECT nls_territory
FROM FND_TERRITORIES
WHERE territory_code= hrloc2.country)
, (SELECT po_vendors.customer_num
FROM po_vendors
WHERE po_vendors.vendor_id = h.vendor_id)
, (SELECT po_vendors.segment1
FROM po_vendors
WHERE po_vendors.vendor_id = h.vendor_id)
, (SELECT ap_terms_tl.NAME
FROM ap_terms_tl
WHERE ap_terms_tl.term_id = h.terms_id AND
ap_terms_tl.LANGUAGE = userenv('LANG'))
, (SELECT po_lookup_codes.displayed_field
FROM po_lookup_codes
WHERE po_lookup_codes.lookup_type = 'FREIGHT TERMS' AND
po_lookup_codes.lookup_code = h.freight_terms_lookup_code)
, (SELECT po_lookup_codes.displayed_field
FROM po_lookup_codes
WHERE po_lookup_codes.lookup_type = 'FOB' AND
po_lookup_codes.lookup_code = h.fob_lookup_code)
, (SELECT po_lookup_codes.displayed_field
FROM po_lookup_codes
WHERE po_lookup_codes.lookup_type = 'SHIPPING CONTROL' AND
po_lookup_codes.lookup_code = h.shipping_control)
, (SELECT org_freight_tl.freight_code_tl
FROM org_freight_tl
WHERE org_freight_tl.freight_code = h.ship_via_lookup_code AND
org_freight_tl.LANGUAGE = userenv('LANG') AND
org_freight_tl.organization_id = l.org_id)
, pvc.first_name || ' ' || pvc.last_name
, pvc.phone
, decode((SELECT COUNT(d.po_distribution_id)
FROM po_distributions_all d
WHERE d.line_location_id = sub.subcontract_po_shipment_id),
1,
( SELECT hremp.full_name || ' ' || hrloc.location_code
FROM po_distributions_all d,
hr_locations_all hrloc,
hr_employees hremp
WHERE hrloc.location_id(+) = d.deliver_to_location_id AND
hremp.employee_id(+) = d.deliver_to_person_id AND
d.line_location_id = sub.subcontract_po_shipment_id),
-- 'MANY REQUESTOR')
'%M' )
, l.note_to_vendor
, proj.project_number
, pt.task_number
, loc.shipment_num
, mtl.segment1
, mtl.description
, loc.need_by_date
, loc.promised_date
, loc.quantity
, l.unit_meas_lookup_code
, sub.osa_item_price
, l.taxable_flag
, l.unit_price * l.quantity
, h.agent_id
, h.vendor_id
, l.item_id
, h.print_count
, h.printed_date
, l.cancel_flag
, h.type_lookup_code
, h.po_header_id
, l.po_line_id
, loc.po_release_id
FROM po_headers_all h,
po_lines_all l,
po_line_locations_all loc,
jmf_subcontract_orders sub,
mtl_system_items_vl mtl,
po_vendor_sites_all pvsa,
hr_locations_all hrloc,
hr_locations_all hrloc2,
po_vendor_contacts pvc,
(select distinct project_id, segment1 AS project_number
from pa_projects_all
union
select distinct project_id, project_number
from pjm_seiban_numbers) proj,
pa_tasks pt
WHERE --h.type_lookup_code IN ('STANDARD')
h.po_header_id = sub.subcontract_po_header_id
AND l.po_line_id = sub.subcontract_po_line_id
AND loc.line_location_id = sub.subcontract_po_shipment_id
AND pvsa.vendor_site_id(+) = h.vendor_site_id
AND hrloc.location_id(+) = h.ship_to_location_id
AND hrloc2.location_id(+) = h.bill_to_location_id
AND pvc.vendor_contact_id(+) = h.vendor_contact_id
AND mtl.inventory_item_id = l.item_id
AND sub.oem_organization_id = mtl.organization_id
AND l.org_id = lp_org_id
AND ((decode(lp_manual_po_num_type,
'NUMERIC',
decode(rtrim(h.segment1, '0123456789'),
NULL,
to_number(h.segment1),
-1),
null) BETWEEN
decode(lp_manual_po_num_type,
'NUMERIC',
decode(rtrim(nvl(lp_po_num_from, h.segment1), '0123456789'),
NULL,
to_number(nvl(lp_po_num_from, h.segment1)),
-1),
null) AND
decode(lp_manual_po_num_type,
'NUMERIC',
decode(rtrim(nvl(lp_po_num_to, h.segment1), '0123456789'),
NULL,
to_number(nvl(lp_po_num_to, h.segment1)),
-1),
null)) OR
(h.segment1 BETWEEN decode(lp_manual_po_num_type,
'ALPHANUMERIC',
nvl(lp_po_num_from, h.segment1),
null) AND
decode(lp_manual_po_num_type,
'ALPHANUMERIC',
nvl(lp_po_num_to, h.segment1),
null)))
AND h.approved_flag = NVL(lp_approved_flag,h.approved_flag)
AND h.agent_id = NVL(lp_agent_name_num,h.agent_id)
AND proj.project_id(+) = sub.project_id
AND pt.task_id(+) = sub.task_id;
DELETE FROM jmf_shikyu_spr_rpt_temp;
SELECT psp.manual_po_num_type manual_po_num_type
INTO l_manual_po_num_type
FROM po_system_parameters_all psp
where org_id = p_ou_id;
,l_last_updated_by_name
,l_vendor_site_code
,l_vendor_address_line1
,l_vendor_address_line2
,l_vendor_address_line3
,l_vendor_city_state_zip
,l_vendor_country
,l_shipto_location_code
,l_shipto_address_line_1
,l_shipto_address_line_2
,l_shipto_address_line_3
,l_ship_to_site_postal_code
,l_ship_to_site_country
,l_billto_location_code
,l_billto_address_line_1
,l_billto_address_line_2
,l_billto_address_line_3
,l_billto_site_postal_code
,l_billto_site_country
,l_customer_num
,l_supplier_num
,l_pay_term
,l_FREIGHT_TERM
,l_FOB_TYPE
,l_SHIPPING_CONTROL
,l_SHIP_VIA
,l_CONFIRM_TO_NAME
,l_CONFIRM_TO_TELEPHONE
,l_REQUESTER
,l_NOTES
,l_project_num
,l_task_num
,l_SHIPMENT_NUM
,l_ITEM_NUM
,l_ITEM_DESC
,l_NEED_BY_DATE
,l_PROMISED_DATE
,l_QUANTITY
,l_UOM
,l_ITEM_PRICE
,l_TAXABLE_FLAG
,l_AMOUNT
,l_AGENT_ID
,l_VENDOR_ID
,l_ITEM_ID
,l_print_count
,l_printed_date
,l_cancel_flag
,l_type_lookup_code
,l_po_header_id
,l_po_line_id
,l_po_release_id ;
INSERT INTO jmf_shikyu_spr_rpt_temp
( subcontract_po_shipment_id
, shipment_type
, report_title_num
, sub_po_num
, agent_name
, sub_po_line_num
, ship_or_release_num
, rev_num
, creation_date
, created_by_name
, revised_date
, REVISED_BY_NAME
, vendor_site_code
, VENDOR_SITE_ADDRESS1
, VENDOR_SITE_ADDRESS2
, VENDOR_SITE_ADDRESS3
, VENDOR_CITY_STATE_ZIP
, VENDOR_COUNTRY
, SHIP_TO_SITE_CODE
, SHIP_TO_SITE_ADDRESS1
, SHIP_TO_SITE_ADDRESS2
, SHIP_TO_SITE_ADDRESS3
, SHIP_TO_SITE_POSTAL_CODE
, SHIP_TO_SITE_COUNTRY
, BILL_TO_SITE_CODE
, BILL_TO_SITE_ADDRESS1
, BILL_TO_SITE_ADDRESS2
, BILL_TO_SITE_ADDRESS3
, BILL_TO_SITE_POSTAL_CODE
, BILL_TO_SITE_COUNTRY
, customer_num
, supplier_num
, pay_term
, FREIGHT_TERM
, FOB_TYPE
, SHIPPING_CONTROL
, SHIP_VIA
, CONFIRM_TO_NAME
, CONFIRM_TO_TELEPHONE
, REQUESTER
, NOTES
, PROJECT_NUM
, TASK_NUM
, SHIPMENT_NUM
, ITEM_NUM
, ITEM_DESC
, NEED_BY_DATE
, PROMISED_DATE
, QUANTITY
, UOM
, ITEM_PRICE
, TAXABLE_FLAG
, AMOUNT
, AGENT_ID
, VENDOR_ID
, ITEM_ID )
VALUES
(l_subcontract_po_shipment_id
,l_shipment_type
,l_report_title_num
,l_po_number
,l_agent_name
,l_line_num
,l_shipment_num
,l_revision_num
,l_creation_date
,l_created_by_name
,l_revised_date
,l_last_updated_by_name
,l_vendor_site_code
,l_vendor_address_line1
,l_vendor_address_line2
,l_vendor_address_line3
,l_vendor_city_state_zip
,l_vendor_country
,l_shipto_location_code
,l_shipto_address_line_1
,l_shipto_address_line_2
,l_shipto_address_line_3
,l_ship_to_site_postal_code
,l_ship_to_site_country
,l_billto_location_code
,l_billto_address_line_1
,l_billto_address_line_2
,l_billto_address_line_3
,l_billto_site_postal_code
,l_billto_site_country
,l_customer_num
,l_supplier_num
,l_pay_term
,l_FREIGHT_TERM
,l_FOB_TYPE
,l_SHIPPING_CONTROL
,l_SHIP_VIA
,l_CONFIRM_TO_NAME
,l_CONFIRM_TO_TELEPHONE
,l_REQUESTER
,l_NOTES
,l_project_num
,l_task_num
,l_SHIPMENT_NUM
,l_ITEM_NUM
,l_ITEM_DESC
,l_NEED_BY_DATE
,l_PROMISED_DATE
,l_QUANTITY
,l_UOM
,l_ITEM_PRICE
,l_TAXABLE_FLAG
,l_AMOUNT
,l_AGENT_ID
,l_VENDOR_ID
,l_ITEM_ID);