[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* last_update_date */
IF p_header_record.header_record.last_update_date IS NULL THEN
p_header_record.header_record.last_update_date := x_sysdate;
asn_debug.put_line('defaulting last update date');
/* last_updated_by */
IF p_header_record.header_record.last_updated_by IS NULL THEN
p_header_record.header_record.last_updated_by := fnd_global.user_id;
asn_debug.put_line('defaulting last update by');
/* last_update_login */
IF p_header_record.header_record.last_update_login IS NULL THEN
p_header_record.header_record.last_update_login := fnd_global.login_id;
asn_debug.put_line('defaulting last update login');
SELECT rcv_shipment_headers_s.NEXTVAL
INTO p_header_record.header_record.receipt_header_id
FROM SYS.DUAL;
SELECT COUNT(*)
INTO v_count
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND ( rti.auto_transact_code IN('RECEIVE', 'DELIVER')
OR rti.transaction_type IN('RECEIVE', 'DELIVER'));
SELECT user_defined_receipt_num_code
INTO v_rcv_type
FROM rcv_parameters
WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
SELECT MAX(hr_locations_all.location_id),
COUNT(*)
INTO x_location_id,
x_count
FROM hr_locations_all
WHERE hr_locations_all.inventory_organization_id = p_header_record.header_record.ship_to_organization_id
AND NVL(hr_locations_all.inactive_date, x_sysdate + 1) > x_sysdate
AND NVL(hr_locations_all.receiving_site_flag, 'N') = 'Y';
SELECT MAX(rti.po_header_id),
MAX(document_num)
INTO x_po_header_id,
x_document_num
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id;
SELECT po_header_id
INTO x_po_header_id
FROM po_headers
WHERE segment1 = x_document_num
AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED');
SELECT COUNT(*)
INTO temp_count
FROM oe_drop_ship_sources
WHERE po_header_id = x_po_header_id;
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE rcv_shipment_headers.receipt_num = p_header_record.header_record.receipt_num
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
rcv_headers_interface rhi
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rhi.header_interface_id = rti.header_interface_id
AND ( ( rti.to_organization_code IS NOT NULL
AND rti.to_organization_code <> p_header_record.header_record.ship_to_organization_code)
OR ( rti.to_organization_id IS NOT NULL
AND rti.to_organization_id <> p_header_record.header_record.ship_to_organization_id)
);
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
hr_locations_all hl, --Bug 5219141. Replace hr_locations by hr_locations_all
mtl_parameters org --Replaced org_organization_definitions
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rti.to_organization_code IS NULL
AND rti.to_organization_id IS NULL
AND rti.ship_to_location_id IS NOT NULL
AND rti.ship_to_location_id = hl.location_id
AND hl.inventory_organization_id = org.organization_id
AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
hr_locations_all hl, --Bug 5219141. Replace hr_locations by hr_locations_all
mtl_parameters org --Replaced org_organization_definitions
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rti.to_organization_code IS NULL
AND rti.to_organization_id IS NULL
AND rti.ship_to_location_code IS NOT NULL
AND rti.ship_to_location_code = hl.location_code
AND hl.inventory_organization_id = org.organization_id
AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
SELECT invoice_amount_limit
FROM po_vendor_sites_all --Bug 5219141 Replace po_vendor_sites by po_vendor_sites_all
WHERE po_vendor_sites_all.vendor_site_id = p_inv_rec.vendor_site_id
AND po_vendor_sites_all.vendor_id = p_inv_rec.vendor_id;
PROCEDURE insert_shipment_header(
p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
) IS
BEGIN
-- Set asn_type to null if asn_type is STD as the UI gets confused
IF NVL(p_header_record.header_record.asn_type, 'STD') = 'STD' THEN
p_header_record.header_record.asn_type := NULL;
INSERT INTO rcv_shipment_headers
(shipment_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
receipt_source_code,
vendor_id,
vendor_site_id,
organization_id,
shipment_num,
receipt_num,
ship_to_location_id,
ship_to_org_id,
bill_of_lading,
packing_slip,
shipped_date,
freight_carrier_code,
expected_receipt_date,
employee_id,
num_of_containers,
waybill_airbill_num,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
asn_type,
edi_control_num,
notice_creation_date,
gross_weight,
gross_weight_uom_code,
net_weight,
net_weight_uom_code,
tar_weight,
tar_weight_uom_code,
packaging_code,
carrier_method,
carrier_equipment,
carrier_equipment_num,
carrier_equipment_alpha,
special_handling_code,
hazard_code,
hazard_class,
hazard_description,
freight_terms,
freight_bill_number,
invoice_date,
invoice_amount,
tax_name,
tax_amount,
freight_amount,
invoice_status_code,
asn_status,
currency_code,
conversion_rate_type,
conversion_rate,
conversion_date,
payment_terms_id,
invoice_num,
ship_from_location_id
)
VALUES (p_header_record.header_record.receipt_header_id,
p_header_record.header_record.last_update_date,
p_header_record.header_record.last_updated_by,
p_header_record.header_record.creation_date,
p_header_record.header_record.created_by,
p_header_record.header_record.last_update_login,
p_header_record.header_record.receipt_source_code,
p_header_record.header_record.vendor_id,
p_header_record.header_record.vendor_site_id,
TO_NUMBER(NULL), -- this is the from organization id and shld be null instead of ship_to_org_id
p_header_record.header_record.shipment_num,
p_header_record.header_record.receipt_num,
p_header_record.header_record.location_id,
p_header_record.header_record.ship_to_organization_id,
p_header_record.header_record.bill_of_lading,
p_header_record.header_record.packing_slip,
p_header_record.header_record.shipped_date,
p_header_record.header_record.freight_carrier_code,
p_header_record.header_record.expected_receipt_date,
p_header_record.header_record.employee_id,
p_header_record.header_record.num_of_containers,
p_header_record.header_record.waybill_airbill_num,
p_header_record.header_record.comments,
p_header_record.header_record.attribute_category,
p_header_record.header_record.attribute1,
p_header_record.header_record.attribute2,
p_header_record.header_record.attribute3,
p_header_record.header_record.attribute4,
p_header_record.header_record.attribute5,
p_header_record.header_record.attribute6,
p_header_record.header_record.attribute7,
p_header_record.header_record.attribute8,
p_header_record.header_record.attribute9,
p_header_record.header_record.attribute10,
p_header_record.header_record.attribute11,
p_header_record.header_record.attribute12,
p_header_record.header_record.attribute13,
p_header_record.header_record.attribute14,
p_header_record.header_record.attribute15,
p_header_record.header_record.usggl_transaction_code,
NULL, -- p_header_record.header_record.Government_Context
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
x_sysdate,
p_header_record.header_record.asn_type,
p_header_record.header_record.edi_control_num,
p_header_record.header_record.notice_creation_date,
p_header_record.header_record.gross_weight,
p_header_record.header_record.gross_weight_uom_code,
p_header_record.header_record.net_weight,
p_header_record.header_record.net_weight_uom_code,
p_header_record.header_record.tar_weight,
p_header_record.header_record.tar_weight_uom_code,
p_header_record.header_record.packaging_code,
p_header_record.header_record.carrier_method,
p_header_record.header_record.carrier_equipment,
NULL, -- p_header_record.header_record.Carrier_Equipment_Num
NULL, -- p_header_record.header_record.Carrier_Equipment_Alpha
p_header_record.header_record.special_handling_code,
p_header_record.header_record.hazard_code,
p_header_record.header_record.hazard_class,
p_header_record.header_record.hazard_description,
p_header_record.header_record.freight_terms,
p_header_record.header_record.freight_bill_number,
p_header_record.header_record.invoice_date,
p_header_record.header_record.total_invoice_amount,
p_header_record.header_record.tax_name,
p_header_record.header_record.tax_amount,
p_header_record.header_record.freight_amount,
p_header_record.header_record.invoice_status_code,
NULL, -- p_header_record.header_record.Asn_Status
p_header_record.header_record.currency_code,
p_header_record.header_record.conversion_rate_type,
p_header_record.header_record.conversion_rate,
p_header_record.header_record.conversion_rate_date,
p_header_record.header_record.payment_terms_id,
p_header_record.header_record.invoice_num,
p_header_record.header_record.ship_from_location_id
);
rcv_error_pkg.set_sql_error_message('insert_shipment_header', '000');
END insert_shipment_header;
SELECT MAX(rti.to_organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = x_header_interface_id;
/* ksareddy RVCTP performance fix 2481798 - select from mtl_parameters instead
SELECT MAX(ORG.ORGANIZATION_CODE)
INTO X_TO_ORGANIZATION_CODE
FROM RCV_TRANSACTIONS_INTERFACE RTI,
ORG_ORGANIZATION_DEFINITIONS ORG
WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
AND ORG.ORGANIZATION_ID = RTI.TO_ORGANIZATION_ID;
SELECT MAX(mtl.organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti,
mtl_parameters mtl
WHERE rti.header_interface_id = x_header_interface_id
AND mtl.organization_id = rti.to_organization_id;
* new one where we select the organization_code from table MTL_PARAMETERS
* instead of the expensive nonmergible view ORG_ORGANIZATION_DEFINITIONS.
SELECT MAX(org.organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti,
hr_locations hl,
org_organization_definitions org
WHERE rti.header_interface_id = x_header_interface_id
AND ( rti.ship_to_location_code = hl.location_code
OR rti.ship_to_location_id = hl.location_id)
AND hl.inventory_organization_id = org.organization_id;*/
SELECT MAX(MTL.ORGANIZATION_CODE)
INTO X_TO_ORGANIZATION_CODE
FROM RCV_TRANSACTIONS_INTERFACE RTI,
HR_LOCATIONS_ALL HL, --BUG 5219141 Replaced HR_LOCATIONS
MTL_PARAMETERS MTL
WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
AND (RTI.SHIP_TO_LOCATION_CODE = HL.LOCATION_CODE
OR RTI.SHIP_TO_LOCATION_ID = HL.LOCATION_ID)
AND HL.INVENTORY_ORGANIZATION_ID = MTL.ORGANIZATION_ID;
rcv_error_pkg.set_sql_error_message('insert_shipment_header', '000');
SELECT (next_receipt_num + 1)
INTO p_header_record.header_record.receipt_num
FROM rcv_parameters
WHERE organization_id = p_header_record.header_record.ship_to_organization_id
FOR UPDATE OF next_receipt_num;
SELECT COUNT(*)
INTO l_count
FROM rcv_shipment_headers
WHERE receipt_num = p_header_record.header_record.receipt_num
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
UPDATE rcv_parameters
SET next_receipt_num = p_header_record.header_record.receipt_num
WHERE organization_id = p_header_record.header_record.ship_to_organization_id;