[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert_cancelled_asn_lines(p_header_record);
asn_debug.put_line('Call insert_shipment_header');
insert_shipment_header(p_header_record);
asn_debug.put_line('After insert_shipment_header');
* receiving an ASN. We need to update these
* in rsh.
*/
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Before update_shipment_header');
update_shipment_header(p_header_record);
asn_debug.put_line('After update_shipment_header');
rcv_rma_headers.insert_rma_header(p_header_record);
rcv_int_order_pp_pvt.update_header(p_header_record);
rcv_roi_header_common.default_last_update_info(p_header_record);
SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
INTO p_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, NVL(vendor_site_id, -9999))
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND ( ( p_header_record.header_record.transaction_type = 'CANCEL'
AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12))
OR ( p_header_record.header_record.transaction_type <> 'CANCEL'
AND shipped_date >= NVL(ADD_MONTHS(p_header_record.header_record.shipped_date, -12), shipped_date))
);
asn_debug.put_line('Select stmt failed to get ship_header_id');
SELECT Count(DISTINCT poh.vendor_site_id),poh.vendor_site_id
INTO count1,x_ven_site_id
FROM rcv_transactions_interface rti, po_headers poh
WHERE ((rti.document_num IS NOT NULL AND rti.document_num = poh.segment1) OR
(rti.po_header_id is not null AND rti.po_header_id = poh.po_header_id))
AND rti.header_interface_id = p_header_record.header_record.header_interface_id
GROUP BY poh.vendor_site_id;
SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
INTO p_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND ( ( p_header_record.header_record.transaction_type = 'CANCEL'
AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12))
OR ( p_header_record.header_record.transaction_type <> 'CANCEL'
AND shipped_date >= NVL(ADD_MONTHS(p_header_record.header_record.shipped_date, -12), shipped_date))
);
SELECT COUNT(*)
INTO x_in_this_op_unit
FROM po_headers poh,
rcv_transactions_interface rti
WHERE poh.vendor_id = p_header_record.header_record.vendor_id
AND poh.segment1 = rti.document_num
AND rti.header_interface_id = p_header_record.header_record.header_interface_id
AND NVL(rti.source_document_code, 'PO') = 'PO';
UPDATE rcv_headers_interface
SET processing_status_code = 'PENDING'
WHERE header_interface_id = p_header_record.header_record.header_interface_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING'
WHERE header_interface_id = p_header_record.header_record.header_interface_id
AND processing_status_code = 'RUNNING'
AND processing_mode_code = 'BATCH';
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND --trunc(shipped_date) = trunc(p_header_record.header_record.shipped_date) and
( shipped_date IS NULL
OR shipped_date >= ADD_MONTHS(x_sysdate, -12))
AND shipment_num = p_header_record.header_record.shipment_num
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND receipt_num IS NOT NULL;
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12)
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num;
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND --trunc(shipped_date) = trunc(p_header_record.header_record.shipped_date) and
shipped_date >= ADD_MONTHS(x_sysdate, -12);
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
SELECT SUM(quantity_received)
INTO x_count
FROM rcv_shipment_lines
WHERE rcv_shipment_lines.shipment_header_id = p_header_record.header_record.receipt_header_id;
SELECT SUM(quantity_received)
INTO x_count
FROM rcv_shipment_lines
WHERE EXISTS(SELECT 'x'
FROM rcv_shipment_headers
WHERE rcv_shipment_headers.shipment_header_id = rcv_shipment_lines.shipment_header_id
AND NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12));
SELECT MAX(shipment_header_id)
INTO p_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
SELECT MAX(shipment_header_id)
INTO x_shipment_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
PROCEDURE insert_shipment_header(
p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
) 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;
asn_debug.put_line('Before insert into rsh ');
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,
remit_to_site_id,
ship_from_location_id,
performance_period_from, --Complex Work
performance_period_to, --Complex Work
request_date --Complex Work
)
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,
'NEW_SHIP', -- 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.remit_to_site_id,
p_header_record.header_record.ship_from_location_id,
/* Complex Work. Added new columns */
p_header_record.header_record.performance_period_from,
p_header_record.header_record.performance_period_to,
p_header_record.header_record.request_date
);
asn_debug.put_line('After insert into rsh ');
asn_debug.put_line('Exception in insert_shipment_header ');
END insert_shipment_header;
PROCEDURE update_shipment_header(
p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
) IS
BEGIN
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Enter in update_shipment_header ');
UPDATE rcv_shipment_headers
SET receipt_num = NVL(receipt_num, p_header_record.header_record.receipt_num),
bill_of_lading = p_header_record.header_record.bill_of_lading,
packing_slip = p_header_record.header_record.packing_slip,
freight_carrier_code = p_header_record.header_record.freight_carrier_code,
expected_receipt_date = p_header_record.header_record.expected_receipt_date,
employee_id = p_header_record.header_record.employee_id,
num_of_containers = p_header_record.header_record.num_of_containers,
waybill_airbill_num = p_header_record.header_record.waybill_airbill_num,
comments = p_header_record.header_record.comments,
attribute1 = p_header_record.header_record.attribute1,
attribute2 = p_header_record.header_record.attribute2,
attribute3 = p_header_record.header_record.attribute3,
attribute4 = p_header_record.header_record.attribute4,
attribute5 = p_header_record.header_record.attribute5,
attribute6 = p_header_record.header_record.attribute6,
attribute7 = p_header_record.header_record.attribute7,
attribute8 = p_header_record.header_record.attribute8,
attribute9 = p_header_record.header_record.attribute9,
attribute10 = p_header_record.header_record.attribute10,
attribute11 = p_header_record.header_record.attribute11,
attribute12 = p_header_record.header_record.attribute12,
attribute13 = p_header_record.header_record.attribute13,
attribute14 = p_header_record.header_record.attribute14,
attribute15 = p_header_record.header_record.attribute15
WHERE shipment_header_id = p_header_record.header_record.receipt_header_id;
asn_debug.put_line('Exception in update_shipment_header ');
END update_shipment_header;
PROCEDURE insert_cancelled_asn_lines(
p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
) IS
BEGIN
-- delete any asn lines that have been sent
asn_debug.put_line('Delete any asn lines that have been sent');
DELETE FROM rcv_transactions_interface
WHERE header_interface_id = p_header_record.header_record.header_interface_id;
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
header_interface_id,
GROUP_ID,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
category_id,
quantity,
unit_of_measure,
interface_source_code,
item_id,
item_description,
employee_id,
auto_transact_code,
receipt_source_code,
vendor_id,
to_organization_id,
source_document_code,
po_header_id,
po_line_id,
po_line_location_id,
shipment_header_id,
shipment_line_id,
destination_type_code,
processing_request_id,
org_id
)
SELECT rcv_transactions_interface_s.NEXTVAL,
p_header_record.header_record.header_interface_id,
p_header_record.header_record.GROUP_ID,
p_header_record.header_record.last_update_date,
p_header_record.header_record.last_updated_by,
p_header_record.header_record.last_update_login,
p_header_record.header_record.creation_date,
p_header_record.header_record.created_by,
'CANCEL',
NVL(p_header_record.header_record.notice_creation_date, SYSDATE),
'RUNNING', -- This has to be set to running otherwise C code in rvtbm
-- will not pick it up
'BATCH',
'PENDING',
rsl.category_id,
rsl.quantity_shipped,
rsl.unit_of_measure,
'RCV',
rsl.item_id,
rsl.item_description,
rsl.employee_id,
'CANCEL',
'VENDOR',
p_header_record.header_record.vendor_id,
rsl.to_organization_id,
'PO',
rsl.po_header_id,
rsl.po_line_id,
rsl.po_line_location_id,
rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.destination_type_code,
p_header_record.header_record.processing_request_id,
poh.org_id
FROM rcv_shipment_lines rsl,
po_headers_all poh
WHERE rsl.shipment_header_id = p_header_record.header_record.receipt_header_id
AND rsl.shipment_line_status_code <> 'CANCELLED'
AND rsl.po_header_id = poh.po_header_id
AND NOT EXISTS(SELECT 'x'
FROM rcv_transactions_interface rti
WHERE rti.shipment_line_id = rsl.shipment_line_id
AND rti.shipment_header_id = rsl.shipment_header_id
AND rti.transaction_type = 'CANCEL'
AND rti.shipment_header_id = p_header_record.header_record.receipt_header_id);
END insert_cancelled_asn_lines;