[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_CREATED_BY IN NUMBER,
P_SHIPMENT_NUM IN VARCHAR2,
P_VENDOR_NAME IN VARCHAR2,
P_VENDOR_ID IN NUMBER,
P_VENDOR_SITE_CODE IN VARCHAR2,
P_VENDOR_SITE_ID IN NUMBER,
P_BILL_OF_LADING IN VARCHAR2,
P_PACKING_SLIP IN VARCHAR2,
P_SHIPPED_DATE IN VARCHAR2,
P_FREIGHT_CARRIER_CODE IN VARCHAR2,
P_EXPECTED_RECEIPT_DATE IN VARCHAR2,
P_NUM_OF_CONTAINERS IN NUMBER,
P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
P_COMMENTS IN VARCHAR2,
P_PACKAGING_CODE IN VARCHAR2,
P_CARRIER_METHOD IN VARCHAR2,
P_CARRIER_EQUIPMENT IN VARCHAR2,
P_SPECIAL_HANDLING_CODE IN VARCHAR2,
P_INVOICE_NUM IN VARCHAR2,
P_INVOICE_DATE IN VARCHAR2,
P_TOTAL_INVOICE_AMOUNT IN NUMBER,
P_PAYMENT_TERMS_ID IN NUMBER,
P_HAZARD_CODE IN VARCHAR2,
P_FREIGHT_TERMS IN VARCHAR2,
P_FREIGHT_AMOUNT IN NUMBER,
P_CURRENCY_CODE IN VARCHAR2,
P_CURRENCY_CONVERSION_TYPE IN VARCHAR2,
P_CURRENCY_CONVERSION_RATE IN NUMBER,
P_CURRENCY_CONVERSION_DATE IN VARCHAR2,
p_gross_weight IN NUMBER,
p_gross_weight_uom IN VARCHAR2 ,
p_net_weight IN NUMBER ,
p_net_weight_uom IN VARCHAR2 ,
p_tar_weight IN NUMBER ,
p_tar_weight_uom IN VARCHAR2 ,
p_freight_bill_num IN VARCHAR2 ,
/* rcv transaction interface parameters */
P_QUANTITY_T IN NUMBER,
P_UNIT_OF_MEASURE_T IN VARCHAR2,
P_ITEM_ID_T IN NUMBER,
P_ITEM_REVISION_T IN VARCHAR2,
P_SHIP_TO_LOCATION_CODE_T IN VARCHAR2,
P_SHIP_TO_ORG_ID_T IN NUMBER,
P_PO_HEADER_ID_T IN NUMBER,
P_PO_REVISION_NUM_T IN NUMBER,
P_PO_LINE_ID_T IN NUMBER,
P_PO_LINE_LOCATION_ID_T IN NUMBER,
P_PO_UNIT_PRICE_T IN NUMBER,
P_PACKING_SLIP_T IN VARCHAR2,
P_SHIPPED_DATE_T IN VARCHAR2,
P_EXPECTED_RECEIPT_DATE_T IN VARCHAR2,
P_NUM_OF_CONTAINERS_T IN NUMBER,
P_VENDOR_ITEM_NUM_T IN VARCHAR2,
P_VENDOR_LOT_NUM_T IN VARCHAR2,
P_COMMENTS_T IN VARCHAR2,
P_TRUCK_NUM_T IN VARCHAR2,
P_CONTAINER_NUM_T IN VARCHAR2,
P_DELIVER_TO_LOCATION_CODE_T IN VARCHAR2,
P_BARCODE_LABEL_T IN VARCHAR2,
P_COUNTRY_OF_ORIGIN_CODE_T IN VARCHAR2,
P_DOCUMENT_LINE_NUM_T IN NUMBER,
P_DOCUMENT_SHIPMENT_LINE_NUM_T IN NUMBER,
p_error_code IN OUT NOCOPY VARCHAR2,
p_error_message IN OUT NOCOPY VARCHAR2,
P_PAYMENT_TERMS_NAME IN VARCHAR2,
P_OPERATING_UNIT_ID IN NUMBER,
P_PO_RELEASE_ID IN NUMBER,
p_tax_amount IN VARCHAR2,
p_license_plate_number in varchar2,
p_lpn_group_id in number) --mji
IS
x_count number := 0;
select WIP_ENTITY_ID ,
WIP_LINE_ID ,
WIP_OPERATION_SEQ_NUM ,
PO_DISTRIBUTION_ID
from po_distributions
where line_location_id = linelocid;
select org_id
into l_org_id
from po_headers_all
where po_header_id = p_po_header_id_t;
select ship_to_location_id
into x_ship_to_location_id
from po_line_locations_all
where line_location_id = P_PO_LINE_LOCATION_ID_T;
/* Insert into RHI only if a record for the same ship_to_org
and ship_to_location is not already inserted for the group id */
/* Commented out ship_to_location */
select count(*)
into x_count
from rcv_headers_interface
where ship_to_organization_id = p_ship_to_org_id_t
--and location_id = x_ship_to_location_id
and vendor_id = P_VENDOR_ID
and vendor_site_id = P_VENDOR_SITE_ID
and group_id = p_group_id
and shipment_num = p_shipment_num;
SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO l_header_id
from dual;
insert into rcv_headers_interface
(HEADER_INTERFACE_ID ,
GROUP_ID ,
PROCESSING_STATUS_CODE ,
-- PROCESSING_REQUEST_ID ,
RECEIPT_SOURCE_CODE ,
TRANSACTION_TYPE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
-- LOCATION_CODE ,
-- LOCATION_ID ,
SHIP_TO_ORGANIZATION_ID ,
VENDOR_ID ,
VENDOR_SITE_ID ,
SHIPPED_DATE ,
ASN_TYPE ,
SHIPMENT_NUM ,
EXPECTED_RECEIPT_DATE ,
PACKING_SLIP ,
WAYBILL_AIRBILL_NUM ,
BILL_OF_LADING ,
FREIGHT_CARRIER_CODE ,
FREIGHT_TERMS ,
NUM_OF_CONTAINERS ,
COMMENTS ,
CARRIER_METHOD ,
CARRIER_EQUIPMENT ,
PACKAGING_CODE ,
SPECIAL_HANDLING_CODE ,
INVOICE_NUM ,
INVOICE_DATE ,
TOTAL_INVOICE_AMOUNT ,
FREIGHT_AMOUNT ,
TAX_NAME ,
TAX_AMOUNT ,
CURRENCY_CODE ,
CONVERSION_RATE_TYPE ,
CONVERSION_RATE ,
CONVERSION_RATE_DATE ,
PAYMENT_TERMS_ID ,
PAYMENT_TERMS_NAME ,
VALIDATION_FLAG
)
VALUES
(
l_header_id ,
P_GROUP_ID ,
'PENDING' ,
-- P_GROUP_ID ,
'VENDOR' ,
'NEW' ,
sysdate ,
P_LAST_UPDATED_BY ,
P_LAST_UPDATE_LOGIN ,
sysdate ,
P_CREATED_BY ,
-- P_SHIP_TO_LOCATION_CODE_T ,
-- x_ship_to_location_id ,
P_SHIP_TO_ORG_ID_T ,
P_VENDOR_ID ,
P_VENDOR_SITE_ID ,
to_date(P_SHIPPED_DATE,'YYYY-MM-DD'),
decode(P_INVOICE_NUM, NULL, 'ASN', 'ASBN'),
P_SHIPMENT_NUM ,
to_date(P_EXPECTED_RECEIPT_DATE,'YYYY-MM-DD'),
P_PACKING_SLIP ,
P_WAYBILL_AIRBILL_NUM ,
P_BILL_OF_LADING ,
P_FREIGHT_CARRIER_CODE ,
P_FREIGHT_TERMS ,
P_NUM_OF_CONTAINERS ,
P_COMMENTS ,
P_CARRIER_METHOD ,
P_CARRIER_EQUIPMENT ,
P_PACKAGING_CODE ,
P_SPECIAL_HANDLING_CODE ,
P_INVOICE_NUM ,
to_date(P_INVOICE_DATE,'YYYY-MM-DD'),
P_TOTAL_INVOICE_AMOUNT ,
P_FREIGHT_AMOUNT ,
null , /* TAX_NAME */
p_tax_amount , /* TAX_AMOUNT */
P_CURRENCY_CODE ,
P_CURRENCY_CONVERSION_TYPE ,
P_CURRENCY_CONVERSION_RATE ,
to_date(P_CURRENCY_CONVERSION_DATE,'YYYY-MM-DD'),
P_PAYMENT_TERMS_ID ,
P_PAYMENT_TERMS_NAME ,
'Y' );
SELECT header_interface_id
into l_header_id
from rcv_headers_interface
where ship_to_organization_id = p_ship_to_org_id_t
--and location_id = x_ship_to_location_id
and vendor_id = P_VENDOR_ID
and vendor_site_id = P_VENDOR_SITE_ID
and group_id = p_group_id
and shipment_num = p_shipment_num;
select count(*)
into x_pla_count
from po_location_associations PLA
where pla.location_id =
(select location_id from hr_locations_all
where location_code = P_SHIP_TO_LOCATION_CODE_T) and
pla.vendor_id is not null and pla.vendor_site_id is not null;
select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
into l_iface_txn_id
from dual;
select unit_meas_lookup_code,
item_id,
item_revision
into l_primary_unit_of_measure,
l_item_id,
l_item_revision
from po_lines_all
where po_line_id = P_PO_LINE_ID_T;
insert into rcv_transactions_interface
( INTERFACE_TRANSACTION_ID ,
HEADER_INTERFACE_ID ,
GROUP_ID ,
TRANSACTION_TYPE ,
TRANSACTION_DATE ,
PROCESSING_STATUS_CODE ,
PROCESSING_MODE_CODE ,
TRANSACTION_STATUS_CODE ,
AUTO_TRANSACT_CODE ,
RECEIPT_SOURCE_CODE ,
SOURCE_DOCUMENT_CODE ,
PO_HEADER_ID ,
PO_LINE_ID ,
PO_LINE_LOCATION_ID ,
QUANTITY ,
PRIMARY_QUANTITY ,
UNIT_OF_MEASURE ,
PRIMARY_UNIT_OF_MEASURE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ITEM_ID ,
ITEM_REVISION ,
EXPECTED_RECEIPT_DATE ,
COMMENTS ,
BARCODE_LABEL ,
CONTAINER_NUM ,
COUNTRY_OF_ORIGIN_CODE ,
VENDOR_ITEM_NUM ,
VENDOR_LOT_NUM ,
TRUCK_NUM ,
NUM_OF_CONTAINERS ,
PACKING_SLIP ,
VALIDATION_FLAG ,
WIP_ENTITY_ID ,
WIP_LINE_ID ,
WIP_OPERATION_SEQ_NUM ,
PO_DISTRIBUTION_ID ,
DOCUMENT_LINE_NUM ,
DOCUMENT_SHIPMENT_LINE_NUM ,
VENDOR_ID ,
VENDOR_SITE_ID ,
QUANTITY_INVOICED ,
SHIP_TO_LOCATION_CODE ,
SHIP_TO_LOCATION_ID ,
PO_RELEASE_ID,
license_plate_number,
lpn_group_id)
values
( l_iface_txn_id ,
l_header_id ,
P_GROUP_ID ,
l_transaction_type ,
sysdate ,
'PENDING' ,
'BATCH' ,
'RUNNING',
l_auto_transact_code ,
'VENDOR' ,
'PO' ,
P_PO_HEADER_ID_T ,
P_PO_LINE_ID_T ,
P_PO_LINE_LOCATION_ID_T ,
P_QUANTITY_T ,
l_converted_qty ,
P_UNIT_OF_MEASURE_T ,
l_primary_unit_of_measure ,
sysdate ,
P_LAST_UPDATED_BY ,
P_LAST_UPDATE_LOGIN ,
sysdate ,
P_CREATED_BY ,
P_ITEM_ID_T ,
l_item_revision ,
to_date(P_EXPECTED_RECEIPT_DATE_T,'YYYY-MM-DD') ,
P_COMMENTS_T ,
P_BARCODE_LABEL_T ,
P_CONTAINER_NUM_T ,
P_COUNTRY_OF_ORIGIN_CODE_T ,
P_VENDOR_ITEM_NUM_T ,
P_VENDOR_LOT_NUM_T ,
P_TRUCK_NUM_T ,
P_NUM_OF_CONTAINERS_T ,
P_PACKING_SLIP_T ,
'Y' ,
dis_details_rec.WIP_ENTITY_ID ,
dis_details_rec.WIP_LINE_ID ,
dis_details_rec.WIP_OPERATION_SEQ_NUM ,
dis_details_rec.PO_DISTRIBUTION_ID ,
P_DOCUMENT_LINE_NUM_T ,
P_DOCUMENT_SHIPMENT_LINE_NUM_T ,
P_VENDOR_ID ,
P_VENDOR_SITE_ID ,
l_quantity_invoiced ,
P_SHIP_TO_LOCATION_CODE_T ,
x_ship_to_location_id ,
P_PO_RELEASE_ID,
p_license_plate_number,
p_lpn_group_id);
select agent_id
into l_buyer_id
from po_headers_all
where po_header_id = p_po_header_id_t;
select count(*)
into x_note_count
from rcv_transactions_interface
where header_interface_id = l_header_id;
P_LAST_UPDATED_BY,
l_supplier_username,
l_supplier_displayname);
SELECT nvl(pll.quantity, 0),
nvl(pll.quantity_received, 0),
nvl(pll.quantity_shipped, 0),
nvl(pll.quantity_cancelled,0),
1 + (nvl(pll.qty_rcv_tolerance,0)/100),
pll.qty_rcv_exception_code,
pl.item_id,
pl.unit_meas_lookup_code
INTO x_quantity_ordered,
x_quantity_received,
x_quantity_shipped,
x_quantity_cancelled,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_item_id,
x_po_uom
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pll.line_location_id = p_line_location_id
AND pll.po_line_id = pl.po_line_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(primary_quantity),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
AND po_line_location_id = p_line_location_id;
SELECT Nvl(PDSH.CLM_FLAG,'N') CLM_FLAG
INTO l_clm_flag
FROM PO_HEADERS_ALL POH,
PO_LINE_LOCATIONS_ALL POLL,
PO_DOC_STYLE_HEADERS PDSH
WHERE POLL.LINE_LOCATION_ID = l_line_location_id AND
POLL.PO_HEADER_ID = POH.PO_HEADER_ID AND
NVL(POH.STYLE_ID, 1) = PDSH.STYLE_ID (+) AND
PDSH.STATUS (+) = 'ACTIVE' AND
ROWNUM =1;
SELECT pl.item_id,
pl.unit_meas_lookup_code
INTO x_item_id,
x_po_uom
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pll.line_location_id = p_line_location_id
AND pll.po_line_id = pl.po_line_id;
select max(po_header_id)
into l_po_header_id
from rcv_transactions_interface rti, rcv_headers_interface rhi
where rhi.group_id = p_groupId
and rhi.header_interface_id = rti.header_interface_id
group by rti.header_interface_id;
select org_id
into l_org_id
from po_headers_all
where po_header_id = l_po_header_id;
select count(*)
into l_count
from ORG_FREIGHT
where
freight_code = p_freight_code and
organization_id = p_organization_id;