[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT deliver_to_location_id,
deliver_to_person_id,
destination_subinventory,
destination_organization_id,
destination_type_code
INTO deliver_to_loc_id,
deliver_to_person_id,
dest_subinv,
dest_org_id,
dest_type_code
FROM po_distributions
WHERE po_distribution_id = x_dist_id;
SELECT ship_to_location_id
INTO ship_to_loc_id
FROM po_line_locations
WHERE line_location_id = x_line_loc_id;
SELECT deliver_to_location_id,
deliver_to_person_id,
to_subinventory,
to_organization_id,
destination_type_code,
shipment_header_id
INTO deliver_to_loc_id,
deliver_to_person_id,
dest_subinv,
dest_org_id,
dest_type_code,
x_ship_head_id
FROM rcv_shipment_lines
WHERE shipment_line_id = x_ship_line_id;
SELECT ship_to_location_id
INTO ship_to_loc_id
FROM rcv_shipment_headers
WHERE shipment_header_id = x_ship_head_id;
SELECT location_code
INTO x_ship_to_loc_code
FROM hr_locations
WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
AND ( inactive_date IS NULL
OR inactive_date > x_trx_date)
AND location_id = ship_to_loc_id;
SELECT location_code
INTO x_deliver_to_loc_code
FROM hr_locations
WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
AND ( inactive_date IS NULL
OR inactive_date > x_trx_date)
AND location_id = deliver_to_loc_id;
SELECT 'Check to see if subinventory is valid'
INTO x_temp
FROM mtl_secondary_inventories
WHERE ( disable_date IS NULL
OR disable_date > x_trx_date)
AND organization_id = x_org_id
AND secondary_inventory_name = dest_subinv
AND ( (x_item_id IS NULL)
OR ( x_item_id IS NOT NULL
AND EXISTS(SELECT 'valid subinventory'
FROM mtl_system_items msi
WHERE msi.organization_id = x_org_id
AND msi.inventory_item_id = x_item_id
AND ( msi.restrict_subinventories_code = 2
OR ( msi.restrict_subinventories_code = 1
AND EXISTS(SELECT 'valid subinventory'
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = x_org_id
AND mis.inventory_item_id = x_item_id
AND mis.secondary_inventory = secondary_inventory_name))))
)
);
SELECT mp.organization_code
INTO x_dest_org_code
FROM HR_ORGANIZATION_UNITS HOU,
MTL_PARAMETERS MP
WHERE HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOU.organization_id = dest_org_id
AND ( HOU.DATE_TO is NULL OR HOU.DATE_To > x_trx_date);
SELECT full_name
INTO x_deliver_to_person
FROM hr_employees_current_v
WHERE ( inactive_date IS NULL
OR inactive_date > x_trx_date)
AND employee_id = deliver_to_person_id;
SELECT enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing_id,
qty_rcv_tolerance,
qty_rcv_exception_code,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
NVL(x_routing_id, receiving_routing_id),
NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
NVL(x_receipt_days_exception_code, receipt_days_exception_code)
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND NVL(organization_id, -99) = NVL(p_org_id, -99);
SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
NVL(x_routing_id, receiving_routing_id),
NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
NVL(x_receipt_days_exception_code, receipt_days_exception_code)
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
NVL(x_routing_id, receiving_routing_id),
NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
NVL(x_receipt_days_exception_code, receipt_days_exception_code)
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM rcv_parameters
WHERE organization_id = p_org_id;
SELECT 'NONE',
NULL,
3 -- 'Direct Delivery'
,
NVL(x_qty_rcv_tolerance, 0),
NVL(x_qty_rcv_exception_code, 'NONE'),
NVL(x_days_early_receipt_allowed, 0),
NVL(x_days_late_receipt_allowed, 0),
NVL(x_receipt_days_exception_code, 'NONE')
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM DUAL;
SELECT 'NONE',
NULL,
3 -- 'Direct Delivery'
,
NVL(x_qty_rcv_tolerance, 0),
NVL(x_qty_rcv_exception_code, 'NONE'),
NULL,
NULL,
NULL
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM DUAL;
SELECT NVL(x_enforce_ship_to_loc_code, 'NONE'),
NVL(x_allow_substitute_receipts, 'N'),
x_routing_id,
NVL(x_qty_rcv_tolerance, 0),
NVL(x_qty_rcv_exception_code, 'NONE'),
NVL(x_days_early_receipt_allowed, 0),
NVL(x_days_late_receipt_allowed, 0),
NVL(x_receipt_days_exception_code, 'NONE')
INTO x_enforce_ship_to_loc_code,
x_allow_substitute_receipts,
x_routing_id,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_days_early_receipt_allowed,
x_days_late_receipt_allowed,
x_receipt_days_exception_code
FROM DUAL;
SELECT routing_name
INTO x_routing_name
FROM rcv_routing_headers
WHERE routing_header_id = x_routing_id;
SELECT COUNT(1)
INTO dup_count
FROM rcv_shipment_headers
WHERE receipt_num = x_receipt_num;
SELECT COUNT(1)
INTO dup_count
FROM po_history_receipts
WHERE receipt_num = x_receipt_num;
SELECT COUNT(1)
INTO dup_count
FROM rcv_shipment_headers
WHERE shipment_num = x_shipment_num
AND receipt_source_code = 'VENDOR'
AND vendor_id = x_vendor_id;
SELECT ussgl_transaction_code,
government_context
INTO x_ussgl_trx_code,
x_govt_context
FROM po_line_locations
WHERE line_location_id = x_line_location_id;
SELECT wip_entity_id,
wip_operation_seq_num,
wip_resource_seq_num,
wip_repetitive_schedule_id,
wip_line_id,
bom_resource_id
INTO x_wip_entity_id,
x_wip_operation_seq_num,
x_wip_resource_seq_num,
x_wip_repetitive_schedule_id,
x_wip_line_id,
x_bom_resource_id
FROM po_distributions pod
WHERE pod.po_distribution_id = x_po_dist_id;
** distribution and is used for inserting the transaction rather
** than the operation_seq_num which is derived from the wip tables
** and shows the next operation rather than the current one. This
** value is used for display purposes
*/
x_progress := 10;
select we.wip_entity_name,
wn.operation_seq_num,
bd.department_code
into x_job_schedule_dsp,
x_op_seq_num_dsp,
x_department_code
from wip_entities we,
bom_departments bd,
wip_operation_resources wr,
wip_operations wn,
wip_operations wo
where wo.wip_entity_id = x_wip_entity_id
and wo.organization_id = x_organization_id
and nvl(wo.repetitive_schedule_id, -1) =
nvl(x_wip_repetitive_schedule_id, -1)
and wo.operation_seq_num = x_wip_operation_seq_num
and wr.wip_entity_id = x_wip_entity_id
and wr.organization_id = x_organization_id
and nvl(wr.repetitive_schedule_id, -1) =
nvl(x_wip_repetitive_schedule_id, -1)
and wr.operation_seq_num = x_wip_operation_seq_num
and wr.resource_seq_num = x_wip_resource_seq_num
and wn.wip_entity_id = x_wip_entity_id
and wn.organization_id = x_organization_id
and nvl(wn.repetitive_schedule_id, -1) =
nvl(x_wip_repetitive_schedule_id, -1)
and wn.operation_seq_num = decode(wr.autocharge_type,
4, nvl(wo.next_operation_seq_num,
wo.operation_seq_num),wo.operation_seq_num)
and bd.department_id = wn.department_id
and we.wip_entity_id = x_wip_entity_id
and we.organization_id = x_organization_id ;
SELECT we.wip_entity_name job
INTO x_job_schedule_dsp
FROM wip_entities we
WHERE we.wip_entity_id = x_wip_entity_id
AND we.organization_id = x_organization_id;
SELECT wn.operation_seq_num SEQUENCE,
bd.department_code department
INTO x_op_seq_num_dsp,
x_department_code
FROM bom_departments bd,
wip_operation_resources wr,
wip_operations wn,
wip_operations wo
WHERE wo.wip_entity_id = x_wip_entity_id
AND wo.organization_id = x_organization_id
AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
AND wo.operation_seq_num = x_wip_operation_seq_num
AND wr.wip_entity_id = x_wip_entity_id
AND wr.organization_id = x_organization_id
AND NVL(wr.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
AND wr.operation_seq_num = x_wip_operation_seq_num
AND wr.resource_seq_num = x_wip_resource_seq_num
AND wn.wip_entity_id = x_wip_entity_id
AND wn.organization_id = x_organization_id
AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
AND wn.operation_seq_num = DECODE(wr.autocharge_type,
4, NVL(wo.next_operation_seq_num, wo.operation_seq_num),
wo.operation_seq_num
)
AND bd.department_id = wn.department_id;
SELECT bd.department_code department
INTO x_department_code
FROM bom_departments bd,
wip_operations wn
WHERE wn.wip_entity_id = x_wip_entity_id
AND wn.organization_id = x_organization_id
AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
AND bd.department_id = wn.department_id;
SELECT wo.operation_seq_num SEQUENCE
INTO x_op_seq_num_dsp
FROM wip_operations wo
WHERE wo.wip_entity_id = x_wip_entity_id
AND wo.organization_id = x_organization_id
AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
AND wo.operation_seq_num = x_wip_operation_seq_num;
SELECT wl.line_code
INTO x_wip_line_dsp
FROM wip_lines wl
WHERE wl.organization_id = x_org_id
AND wl.line_id = x_wip_line_id;
SELECT COUNT(pon.po_note_id)
INTO x_note_count
FROM po_note_references ponr,
po_notes pon,
po_usage_attributes poua
WHERE ponr.po_note_id = pon.po_note_id
AND pon.usage_id = poua.usage_id
AND poua.note_attribute = x_note_attribute
AND ponr.table_name = x_note_table_name
AND ponr.column_name = x_note_column_name
AND ponr.foreign_id = x_foreign_id;
SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
INTO x_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
INTO x_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND --trunc(shipped_date) = trunc(X_header_record.header_record.shipped_date) and
( shipped_date IS NULL
OR shipped_date >= ADD_MONTHS(x_sysdate, -12))
AND shipment_num = x_header_record.header_record.shipment_num
AND ship_to_org_id = x_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(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12)
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num;
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND --trunc(shipped_date) = trunc(X_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(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(x_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 = x_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(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12));
SELECT MAX(shipment_header_id)
INTO x_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(x_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(x_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = x_header_record.header_record.vendor_id
AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
AND shipment_num = x_header_record.header_record.shipment_num
AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
AND shipped_date >= ADD_MONTHS(x_sysdate, -12);