The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(
NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
*POLL.PRICE_OVERRIDE,
(POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
INTO x_pay_item_total
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM');
SELECT DISTINCT
POH.PO_HEADER_ID,
POH.SEGMENT1,
POH.CURRENCY_CODE
INTO
l_po_header_id, x_po_num, x_po_currency_code
FROM
RCV_TRANSACTIONS_INTERFACE RTI,
PO_HEADERS_ALL POH
WHERE
POH.PO_HEADER_ID = RTI.PO_HEADER_ID AND
RTI.HEADER_INTERFACE_ID = p_wc_header_id;
SELECT DISTINCT
POH.PO_HEADER_ID,
POH.SEGMENT1,
POH.CURRENCY_CODE
INTO
l_po_header_id, x_po_num, x_po_currency_code
FROM
RCV_SHIPMENT_LINES RSL,
PO_HEADERS_ALL POH
WHERE
POH.PO_HEADER_ID = RSL.PO_HEADER_ID AND
RSL.SHIPMENT_HEADER_ID = p_wc_header_id;
SELECT SUM(
NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
*POLL.PRICE_OVERRIDE,
(POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
INTO x_po_lines_ordered
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE POLL.PO_HEADER_ID = l_po_header_id AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM');
select SUM(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)))
from PO_LINE_LOCATIONS_ALL PLL
where PLL.po_header_id = p_po_header_id
and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM');
SELECT org_id
INTO l_org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT SUM( NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
INTO l_wc_intf_prev_submitted
FROM
PO_LINE_LOCATIONS_ALL POLL,
RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI
WHERE
RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM') AND
RTI.PO_HEADER_ID = p_po_header_id AND
RHI.REQUEST_DATE < p_wc_request_date;
SELECT SUM( NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
INTO l_wc_intf_prev_delivered
FROM
PO_LINE_LOCATIONS_ALL POLL,
RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI
WHERE
RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
POLL.PAYMENT_TYPE = 'DELIVERY' AND
RTI.PO_HEADER_ID = p_po_header_id AND
RHI.REQUEST_DATE < p_wc_request_date;
SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
INTO l_wc_ship_prev_submitted
FROM
PO_LINE_LOCATIONS_ALL POLL,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE
RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
(RSL.APPROVAL_STATUS is NULL OR
RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED')) AND
RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM') AND
RSL.PO_HEADER_ID = p_po_header_id AND
RSH.REQUEST_DATE < p_wc_request_date;
SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
INTO l_wc_ship_prev_delivered
FROM
PO_LINE_LOCATIONS_ALL POLL,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE
RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
(RSL.APPROVAL_STATUS is NULL OR
RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED')) AND
RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
POLL.PAYMENT_TYPE = 'DELIVERY' AND
RSL.PO_HEADER_ID = p_po_header_id AND
RSH.REQUEST_DATE < p_wc_request_date;
SELECT SUM(NVL(RTI.REQUESTED_AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE)),
SUM(NVL(RTI.MATERIAL_STORED_AMOUNT,0))
INTO l_requested, l_material
FROM RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI,
PO_LINE_LOCATIONS_ALL POLL
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'LUMPSUM', 'RATE') AND
RHI.HEADER_INTERFACE_ID = p_wc_id;
SELECT SUM(NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
INTO l_delivery
FROM RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI,
PO_LINE_LOCATIONS_ALL POLL
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
POLL.PAYMENT_TYPE = 'DELIVERY' AND
RHI.HEADER_INTERFACE_ID = p_wc_id;
SELECT SUM(NVL(RSL.REQUESTED_AMOUNT,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE)),
SUM(NVL(RSL.MATERIAL_STORED_AMOUNT,0))
INTO l_requested, l_material
FROM RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_LINE_LOCATIONS_ALL POLL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
POLL.PAYMENT_TYPE in ('MILESTONE', 'LUMPSUM', 'RATE') AND
RSH.SHIPMENT_HEADER_ID = p_wc_id;
SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
INTO l_delivery
FROM RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_LINE_LOCATIONS_ALL POLL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
POLL.PAYMENT_TYPE = 'DELIVERY' AND
RSH.SHIPMENT_HEADER_ID = p_wc_id;
SELECT processing_status_code, transaction_type
INTO l_header_processing_status, l_header_transaction_type
FROM RCV_HEADERS_INTERFACE
WHERE HEADER_INTERFACE_ID = p_wc_id;
SELECT RSH.APPROVAL_STATUS
INTO l_header_approval_status
FROM RCV_SHIPMENT_HEADERS RSH
WHERE RSH.SHIPMENT_HEADER_ID = p_wc_id;
SELECT count(*) into l_wc_total_lines
FROM RCV_SHIPMENT_LINES
WHERE SHIPMENT_HEADER_ID = p_wc_id;
SELECT count(*) into l_wc_lines_processed
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'DELIVER' AND
SHIPMENT_HEADER_ID = p_wc_id;
SELECT count(*) into l_wc_lines_errored
FROM RCV_TRANSACTIONS_INTERFACE RTI,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND
RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RTI.PROCESSING_STATUS_CODE in ('ERROR','COMPLETED') AND
RTI.TRANSACTION_STATUS_CODE = 'ERROR' AND
RSH.SHIPMENT_HEADER_ID = p_wc_id;
select count(*)
into x_total_lines
from RCV_SHIPMENT_LINES rsl,
RCV_SHIPMENT_HEADERS rsh
where rsh.shipment_header_id= p_wc_id
and rsh.shipment_header_id = rsl.shipment_header_id;
select count(*)
into x_cancelled_lines
from RCV_SHIPMENT_LINES rsl,
RCV_SHIPMENT_HEADERS rsh
where rsh.shipment_header_id = p_wc_id
and rsh.shipment_header_id = rsl.shipment_header_id
and rsl.shipment_line_status_code = 'CANCELLED';
select count(*)
into x_pending_cancel
from RCV_TRANSACTIONS_INTERFACE rti,
RCV_SHIPMENT_HEADERS rsh
where rti.transaction_type = 'CANCEL'
and rti.shipment_header_id = rsh.shipment_header_id
and rsh.shipment_header_id = p_wc_id;
select wf_item_type, wf_item_key
from rcv_shipment_headers
where shipment_header_id = p_header_id;
SELECT rsl.shipment_line_id
FROM RCV_SHIPMENT_LINES rsl,
RCV_SHIPMENT_HEADERS rsh
WHERE rsh.SHIPMENT_HEADER_ID = p_wc_id
AND rsh.shipment_header_id = rsl.shipment_header_id;
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE = 'CANCELLED'
WHERE HEADER_INTERFACE_ID = p_wc_id;
UPDATE RCV_HEADERS_INTERFACE
SET PROCESSING_STATUS_CODE = 'CANCELLED'
WHERE HEADER_INTERFACE_ID = p_wc_id;
SELECT POH.AGENT_ID
INTO l_buyer_id
FROM PO_HEADERS_ALL POH
WHERE POH.PO_HEADER_ID = p_po_header_id;
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
SELECT
RSL.PO_HEADER_ID,
RSL.PO_RELEASE_ID,
RSL.PO_LINE_ID,
RSL.SHIPMENT_HEADER_ID,
RSL.PO_LINE_LOCATION_ID,
RSL.DELIVER_TO_LOCATION_ID,
RSL.TO_ORGANIZATION_ID,
RSL.ITEM_ID,
RSL.QUANTITY_SHIPPED,
RSL.SOURCE_DOCUMENT_CODE,
RSL.CATEGORY_ID,
RSL.UNIT_OF_MEASURE,
RSL.ITEM_DESCRIPTION,
RSL.EMPLOYEE_ID,
RSL.DESTINATION_TYPE_CODE,
RSL.DESTINATION_CONTEXT,
RSL.TO_SUBINVENTORY,
RSL.ROUTING_HEADER_ID,
RSL.PRIMARY_UNIT_OF_MEASURE,
RSL.SHIP_TO_LOCATION_ID,
RSL.AMOUNT_SHIPPED,
RSL.REQUESTED_AMOUNT,
RSL.MATERIAL_STORED_AMOUNT,
POLL.MATCHING_BASIS,
POLL.ORG_ID
INTO
l_po_header_id,
l_po_release_id,
l_po_line_id,
l_shipment_header_id,
l_po_line_location_id,
l_deliver_to_location_id,
l_to_organization_id,
l_item_id,
l_quantity_shipped,
l_source_document_code,
l_category_id,
l_unit_of_measure,
l_item_description,
l_employee_id,
l_destination_type_code,
l_destination_context,
l_subinventory,
l_routing_header_id,
l_primary_unit_of_measure,
l_ship_to_location_id,
l_amount_shipped,
l_requested_amount,
l_material_stored_amount,
l_matching_basis,
l_org_id
FROM
RCV_SHIPMENT_LINES RSL,
PO_LINE_LOCATIONS_ALL POLL
WHERE
RSL.shipment_line_id = p_shipment_line_id AND
RSL.po_line_location_id = POLL.line_location_id;
RCV_ASN_INTERFACE_TRX_INS_PKG.INSERT_ROW(
x_group_id => l_group_id,
x_transaction_type => 'CANCEL',
x_transaction_date => sysdate,
x_processing_status_code => 'PENDING',
x_processing_mode_code => 'BATCH',
x_transaction_status_code => 'PENDING',
x_last_update_date => SYSDATE,
x_last_updated_by => 1,
x_last_update_login => 1,
x_interface_source_code =>'RCV',
x_creation_date => SYSDATE,
x_created_by => 1,
x_auto_transact_code => 'CANCEL',
x_receipt_source_code => 'VENDOR',
-- Parameters whose values should be retrieved from the shipment table
X_po_header_id => l_po_header_id,
X_po_release_id => l_po_release_id,
X_po_line_id => l_po_line_id,
X_shipment_line_id => p_shipment_line_id,
X_shipment_header_id => l_shipment_header_id,
X_po_line_location_id => l_po_line_location_id,
X_deliver_to_location_id => l_deliver_to_location_id,
X_to_organization_id => l_to_organization_id,
X_item_id => null, -- l_item_id,
X_quantity_shipped => l_quantity_shipped,
X_source_document_code => l_source_document_code,
X_category_id => l_category_id,
X_unit_of_measure => l_unit_of_measure,
X_item_description => l_item_description,
X_employee_id => l_employee_id,
X_destination_type_code => l_destination_type_code,
X_destination_context => l_destination_context,
X_subinventory => l_subinventory,
X_routing_header_id => l_routing_header_id,
X_primary_unit_of_measure=> l_primary_unit_of_measure,
X_ship_to_location_id => l_ship_to_location_id,
X_vendor_id => l_vendor_id,
-- Added the following new columns for complex work
P_MATCHING_BASIS => l_matching_basis,
P_AMOUNT_SHIPPED => l_amount_shipped,
P_REQUESTED_AMOUNT => l_requested_amount,
P_MATERIAL_STORED_AMOUNT => l_material_stored_amount,
P_ORG_ID => l_org_id,
-- Rest of the required parameters from API. Pass in null for all of them
X_ROWID => l_row_id,
X_INTERFACE_TRANSACTION_ID => l_interface_transaction_id,
X_REQUEST_ID => null,
X_PROGRAM_APPLICATION_ID => null,
X_PROGRAM_ID => null,
X_PROGRAM_UPDATE_DATE => null,
X_PROCESSING_REQUEST_ID => null,
X_QUANTITY => null,
X_INTERFACE_SOURCE_LINE_ID => null,
X_INV_TRANSACTION_ID => null,
X_ITEM_REVISION => null,
X_UOM_CODE => null,
X_PRIMARY_QUANTITY => null,
X_VENDOR_SITE_ID => null,
X_FROM_ORGANIZATION_ID => null,
X_FROM_SUBINVENTORY => null,
X_INTRANSIT_OWNING_ORG_ID => null,
X_ROUTING_STEP_ID => null,
X_PARENT_TRANSACTION_ID => null,
X_PO_REVISION_NUM => null,
X_PO_UNIT_PRICE => null,
X_CURRENCY_CODE => null,
X_CURRENCY_CONVERSION_TYPE => null,
X_CURRENCY_CONVERSION_RATE => null,
X_CURRENCY_CONVERSION_DATE => null,
X_PO_DISTRIBUTION_ID => null,
X_REQUISITION_LINE_ID => null,
X_REQ_DISTRIBUTION_ID => null,
X_CHARGE_ACCOUNT_ID => null,
X_SUBSTITUTE_UNORDERED_CODE => null,
X_RECEIPT_EXCEPTION_FLAG => null,
X_ACCRUAL_STATUS_CODE => null,
X_INSPECTION_STATUS_CODE => null,
X_INSPECTION_QUALITY_CODE => null,
X_DELIVER_TO_PERSON_ID => null,
X_LOCATION_ID => null,
X_LOCATOR_ID => null,
X_WIP_ENTITY_ID => null,
X_WIP_LINE_ID => null,
X_DEPARTMENT_CODE => null,
X_WIP_REPETITIVE_SCHEDULE_ID => null,
X_WIP_OPERATION_SEQ_NUM => null,
X_WIP_RESOURCE_SEQ_NUM => null,
X_BOM_RESOURCE_ID => null,
X_SHIPMENT_NUM => null,
X_FREIGHT_CARRIER_CODE => null,
X_BILL_OF_LADING => null,
X_PACKING_SLIP => null,
X_SHIPPED_DATE => null,
X_EXPECTED_RECEIPT_DATE => null,
X_ACTUAL_COST => null,
X_TRANSFER_COST => null,
X_TRANSPORTATION_COST => null,
X_TRANSPORTATION_ACCOUNT_ID => null,
X_NUM_OF_CONTAINERS => null,
X_WAYBILL_AIRBILL_NUM => null,
X_VENDOR_ITEM_NUM => null,
X_VENDOR_LOT_NUM => null,
X_RMA_REFERENCE => null,
X_COMMENTS => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_SHIP_HEAD_ATTRIBUTE_CATEGORY => null,
X_SHIP_HEAD_ATTRIBUTE1 => null,
X_SHIP_HEAD_ATTRIBUTE2 => null,
X_SHIP_HEAD_ATTRIBUTE3 => null,
X_SHIP_HEAD_ATTRIBUTE4 => null,
X_SHIP_HEAD_ATTRIBUTE5 => null,
X_SHIP_HEAD_ATTRIBUTE6 => null,
X_SHIP_HEAD_ATTRIBUTE7 => null,
X_SHIP_HEAD_ATTRIBUTE8 => null,
X_SHIP_HEAD_ATTRIBUTE9 => null,
X_SHIP_HEAD_ATTRIBUTE10 => null,
X_SHIP_HEAD_ATTRIBUTE11 => null,
X_SHIP_HEAD_ATTRIBUTE12 => null,
X_SHIP_HEAD_ATTRIBUTE13 => null,
X_SHIP_HEAD_ATTRIBUTE14 => null,
X_SHIP_HEAD_ATTRIBUTE15 => null,
X_SHIP_LINE_ATTRIBUTE_CATEGORY => null,
X_SHIP_LINE_ATTRIBUTE1 => null,
X_SHIP_LINE_ATTRIBUTE2 => null,
X_SHIP_LINE_ATTRIBUTE3 => null,
X_SHIP_LINE_ATTRIBUTE4 => null,
X_SHIP_LINE_ATTRIBUTE5 => null,
X_SHIP_LINE_ATTRIBUTE6 => null,
X_SHIP_LINE_ATTRIBUTE7 => null,
X_SHIP_LINE_ATTRIBUTE8 => null,
X_SHIP_LINE_ATTRIBUTE9 => null,
X_SHIP_LINE_ATTRIBUTE10 => null,
X_SHIP_LINE_ATTRIBUTE11 => null,
X_SHIP_LINE_ATTRIBUTE12 => null,
X_SHIP_LINE_ATTRIBUTE13 => null,
X_SHIP_LINE_ATTRIBUTE14 => null,
X_SHIP_LINE_ATTRIBUTE15 => null,
X_USSGL_TRANSACTION_CODE => null,
X_GOVERNMENT_CONTEXT => null,
X_REASON_ID => null,
X_SOURCE_DOC_QUANTITY => null,
X_SOURCE_DOC_UNIT_OF_MEASURE => null,
X_MOVEMENT_ID => null,
X_HEADER_INTERFACE_ID => null,
X_VENDOR_CUM_SHIPPED_QTY => null,
X_ITEM_NUM => null,
X_DOCUMENT_NUM => null,
X_DOCUMENT_LINE_NUM => null,
X_TRUCK_NUM => null,
X_SHIP_TO_LOCATION_CODE => null,
X_CONTAINER_NUM => null,
X_SUBSTITUTE_ITEM_NUM => null,
X_NOTICE_UNIT_PRICE => null,
X_ITEM_CATEGORY => null,
X_LOCATION_CODE => null,
X_VENDOR_NAME => null,
X_VENDOR_NUM => null,
X_VENDOR_SITE_CODE => null,
X_FROM_ORGANIZATION_CODE => null,
X_TO_ORGANIZATION_CODE => null,
X_INTRANSIT_OWNING_ORG_CODE => null,
X_ROUTING_CODE => null,
X_ROUTING_STEP => null,
X_RELEASE_NUM => null,
X_DOCUMENT_SHIPMENT_LINE_NUM => null,
X_DOCUMENT_DISTRIBUTION_NUM => null,
X_DELIVER_TO_PERSON_NAME => null,
X_DELIVER_TO_LOCATION_CODE => null,
X_USE_MTL_LOT => null,
X_USE_MTL_SERIAL => null,
X_LOCATOR => null,
X_REASON_NAME => null,
X_VALIDATION_FLAG => null,
X_SUBSTITUTE_ITEM_ID => null,
X_QUANTITY_INVOICED => null,
X_TAX_NAME => null,
X_TAX_AMOUNT => null,
X_REQ_NUM => null,
X_REQ_LINE_NUM => null,
X_REQ_DISTRIBUTION_NUM => null,
X_WIP_ENTITY_NAME => null,
X_WIP_LINE_CODE => null,
X_RESOURCE_CODE => null,
X_SHIPMENT_LINE_STATUS_CODE => null,
X_BARCODE_LABEL => null,
X_COUNTRY_OF_ORIGIN_CODE => null,
X_FROM_LOCATOR_ID => null,
X_QA_COLLECTION_ID => null,
X_OE_ORDER_HEADER_ID => null,
X_OE_ORDER_LINE_ID => null,
X_CUSTOMER_ID => null,
X_CUSTOMER_SITE_ID => null,
X_CUSTOMER_ITEM_NUM => null,
X_CREATE_DEBIT_MEMO_FLAG => null,
X_PUT_AWAY_RULE_ID => null,
X_PUT_AWAY_STRATEGY_ID => null,
X_LPN_ID => null,
X_TRANSFER_LPN_ID => null,
X_COST_GROUP_ID => null,
X_MOBILE_TXN => null,
X_MMTT_TEMP_ID => null,
X_TRANSFER_COST_GROUP_ID => null,
X_SECONDARY_QUANTITY => null,
X_SECONDARY_UNIT_OF_MEASURE => null,
X_SECONDARY_UOM_CODE => null,
X_QC_GRADE => null,
X_OE_ORDER_NUM => null,
X_OE_ORDER_LINE_NUM => null,
X_CUSTOMER_ACCOUNT_NUMBER => null,
X_CUSTOMER_PARTY_NAME => null,
X_SOURCE_TRANSACTION_NUM => null,
X_PARENT_SOURCE_TXN_NUM => null,
X_PARENT_INTERFACE_TXN_ID => null,
X_CUSTOMER_ITEM_ID => null,
X_INTERFACE_AVAIL_QTY => null,
X_INTERFACE_TRANS_QTY => null,
X_FROM_LOCATOR => null,
X_LPN_GROUP_ID => null,
X_ORDER_TRANSACTION_ID => null,
X_LICENSE_PLATE_NUMBER => null,
X_TFR_LICENSE_PLATE_NUMBER => null,
X_AMOUNT => null,
X_JOB_ID => null,
X_PROJECT_ID => null,
X_TASK_ID => null,
X_ASN_ATTACH_ID => null,
X_TIMECARD_ID => null,
X_TIMECARD_OVN => null,
X_INTERFACE_AVAIL_AMT => null,
X_INTERFACE_TRANS_AMT => null);
PROCEDURE DELETE_WC
(
p_wc_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_return_msg OUT nocopy VARCHAR2
)
IS
l_header_interface_id NUMBER;
SELECT HEADER_INTERFACE_ID
FROM RCV_HEADERS_INTERFACE
WHERE HEADER_INTERFACE_ID = p_wc_id;
SELECT DISTINCT RTI.ASN_ATTACH_ID
FROM RCV_TRANSACTIONS_INTERFACE RTI,
FND_ATTACHED_DOCUMENTS FAD
WHERE RTI.HEADER_INTERFACE_ID = L_HEADER_INTF_ID
AND RTI.ASN_ATTACH_ID IS NOT NULL
AND TO_CHAR(RTI.ASN_ATTACH_ID) = FAD.PK1_VALUE
AND FAD.ENTITY_NAME = 'ASN_ATTACH';
/* Delete WC attachment if exists. */
OPEN l_wc_attach_csr (l_header_interface_id);
RCV_ASN_ATTACHMENT_PKG.DELETE_LINE_ATTACHMENT
(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_asn_attach_id => l_wc_attach_id
);
/* Delete WC line from interface table. */
DELETE FROM RCV_TRANSACTIONS_INTERFACE
WHERE header_interface_id = l_header_interface_id;
DELETE FROM RCV_HEADERS_INTERFACE
WHERE header_interface_id = l_header_interface_id;
LOG(FND_LOG.LEVEL_UNEXPECTED,'delete_wc',
'Unexpected error occurred');
END DELETE_WC;