[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE create_update_delrec
(
p_api_version IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
p_action IN VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_doc_id IN NUMBER,
p_line_id IN NUMBER,
p_line_location_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(100) := 'create_update_delrec';
SAVEPOINT create_update_delrec;
PO_OTM_INTEGRATION_PVT.handle_doc_update(
p_doc_type => p_doc_type
, p_doc_id => p_doc_id
, p_action => p_action
, p_line_id => p_line_id
, p_line_loc_id => p_line_location_id);
SELECT shipping_control
INTO l_shipping_control
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
SELECT shipping_control
INTO l_shipping_control
FROM PO_RELEASES_ALL
WHERE po_release_id = p_doc_id;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Call WSH_BULK_PROCESS_GRP.create_update_delivery_details');
WSH_BULK_PROCESS_GRP.create_update_delivery_details
(
p_api_version_number => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_action_prms => l_action_rec,
p_line_rec => l_fte_rec,
x_Out_Rec => l_fte_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Call WSH_BULK_PROCESS_GRP.create_update_delivery_details');
WSH_BULK_PROCESS_GRP.create_update_delivery_details
(
p_api_version_number => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_action_prms => l_action_rec,
p_line_rec => l_fte_rec,
x_Out_Rec => l_fte_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO create_update_delrec;
ROLLBACK TO create_update_delrec;
ROLLBACK TO create_update_delrec;
SELECT transaction_id
INTO l_transaction_id
FROM rcv_fte_transaction_lines
WHERE header_id = p_header_id
AND line_id = p_line_id
AND action = p_action
AND reported_flag IN ( 'N', 'U')
AND rownum = 1;
SELECT transaction_id
INTO l_transaction_id
FROM rcv_fte_transaction_lines
WHERE header_id = p_header_id
AND line_id = p_line_id
AND action = p_action
AND reported_flag IN ( 'N', 'U')
AND rownum = 1;
SELECT
'PO',
poh.po_header_id,
pol.po_line_id,
rsl.po_release_id,
poh.vendor_id,
poh.vendor_site_id,
pol.item_id,
pol.item_description,
pol.hazard_class_id,
pll.country_of_origin_code,
pll.ship_to_location_id,
poh.user_hold_flag,
pll.qty_rcv_tolerance,
pll.receive_close_tolerance,
pll.quantity_shipped,
rt.subinventory,
pol.item_revision,
rt.locator_id,
pll.need_by_date,
pll.promised_date,
orf.party_id,
poh.freight_terms_lookup_code,
poh.fob_lookup_code,
pol.vendor_product_num,
msi.unit_weight,
msi.weight_uom_code,
msi.unit_volume,
msi.volume_uom_code,
rsh.ship_to_org_id,
poh.segment1,
DECODE (poh.type_lookup_code,
'STANDARD', 1,
'BLANKET', 2,
1),
fl.meaning, -- begin
pll.quantity,
puom.uom_code,
pll.quantity_cancelled,
rsh.waybill_airbill_num,
nvl(rsl.packing_slip, rsh.packing_slip),
poh.org_id,
pol.line_num,
rsh.gross_weight, -- end
rsh.gross_weight_uom_code,
rsh.net_weight,
rsh.net_weight_uom_code,
rsh.tar_weight,
rsh.tar_weight_uom_code,
pll.price_override,
poh.currency_code,
pol.qc_grade,
pll.secondary_quantity,
suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
pll.secondary_quantity,
rsl.secondary_quantity_shipped,
pll.secondary_quantity_cancelled,
suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
rsl.asn_lpn_id, -- used to NVL with rt, they don't care about rt
DECODE(p_action, 'ASN', rsl.quantity_shipped,
'RECEIPT', distquery.squant,
'MATCH', distquery.squant,
NVL(rt.quantity,0)),
NVL(rtuom.uom_code, muom.uom_code),
rt.secondary_quantity,
NVL(rtsuom.uom_code, suom.uom_code),
rsl.po_line_location_id,
pll.shipment_num,
por.release_num,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
poh.shipping_control,
pll.drop_ship_flag,
rsh.shipment_header_id,
rsh.shipment_num,
rsh.receipt_num,
rsh.shipped_date,
DECODE(p_action, 'ASN', rsh.expected_receipt_date, NVL(rt.transaction_date, SYSDATE)),
rsh.bill_of_lading,
rsh.num_of_containers,
rsl.container_num,
rsl.truck_num,
rsl.shipment_line_id,
pll.qty_rcv_exception_code,
DECODE(p_action, 'RECEIPT', distquery.maxtrans, 'MATCH', distquery.maxtrans, rt.transaction_id),
rsl.shipment_line_id,
rsh.shipment_header_id,
pll.closed_flag,
pll.cancel_flag,
pll.closed_code,
DECODE (PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
pll.receipt_days_exception_code,
pll.enforce_ship_to_location_code,
poh.revision_num,
por.revision_num,
pll.last_update_date,
rsl.ship_to_location_id,
rsl.item_id,
rsl.item_description,
rt.country_of_origin_code,
rsl.item_revision,
orf2.party_id,
rsh.freight_terms,
rsl.vendor_item_num,
rt.qc_grade,
rsh.asn_type
BULK COLLECT INTO
x_fte_rec.source_code,
x_fte_rec.header_id,
x_fte_rec.line_id,
x_fte_rec.source_blanket_reference_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.hold_code,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.subinventory,
x_fte_rec.revision,
x_fte_rec.locator_id,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.carrier_id,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.supplier_item_num,
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code,
x_fte_rec.organization_id,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id, -- fix
x_fte_rec.source_header_type_name, -- begin
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.tracking_number,
x_fte_rec.packing_slip_number,
x_fte_rec.org_id,
x_fte_rec.source_line_number, -- end
x_fte_rec.rcv_gross_weight,
x_fte_rec.rcv_gross_weight_uom_code,
x_fte_rec.rcv_net_weight,
x_fte_rec.rcv_net_weight_uom_code,
x_fte_rec.rcv_tare_weight,
x_fte_rec.rcv_tare_weight_uom_code,
x_fte_rec.unit_list_price,
x_fte_rec.currency_code,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.shipped_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.lpn_id,
x_fte_rec.received_quantity,
x_fte_rec.received_quantity_uom,
x_fte_rec.received_quantity2,
x_fte_rec.received_quantity2_uom,
x_fte_rec.po_shipment_line_id,
x_fte_rec.po_shipment_line_number,
x_fte_rec.source_blanket_reference_num,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.shipping_control,
x_fte_rec.drop_ship_flag,
x_fte_rec.shipment_header_id,
x_fte_rec.shipment_num,
x_fte_rec.receipt_num,
x_fte_rec.shipped_date,
x_fte_rec.expected_receipt_date,
x_fte_rec.bill_of_lading,
x_fte_rec.num_of_containers,
x_fte_rec.container_num,
x_fte_rec.truck_num,
x_fte_rec.shipment_line_id,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.rcv_transaction_id,
x_fte_rec.rcv_parent_shipment_line_id,
x_fte_rec.rcv_parent_shipment_header_id,
x_fte_rec.closed_flag,
x_fte_rec.cancelled_flag,
x_fte_rec.closed_code,
x_fte_rec.source_line_type_code,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.po_revision,
x_fte_rec.release_revision,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.rcv_ship_to_location_id,
x_fte_rec.rcv_inventory_item_id,
x_fte_rec.rcv_item_description,
x_fte_rec.rcv_country_of_origin,
x_fte_rec.rcv_revision,
x_fte_rec.rcv_carrier_id,
x_fte_rec.rcv_freight_terms_code,
x_fte_rec.rcv_supplier_item_num,
x_fte_rec.rcv_preferred_grade,
x_fte_rec.asn_type
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
rcv_transactions rt,
mtl_system_items msi,
fnd_lookup_values fl,
po_releases_all por,
mtl_units_of_measure muom,
mtl_units_of_measure suom,
mtl_units_of_measure puom,
mtl_units_of_measure rtuom,
mtl_units_of_measure rtsuom,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
org_freight orf,
org_freight orf2,
po_line_types_b plt,
(
SELECT shipment_header_id,
shipment_line_id,
sum(quantity) squant,
max(transaction_id) maxtrans
FROM rcv_transactions
WHERE shipment_header_id = p_header_id
AND transaction_type IN ('RECEIVE', 'MATCH')
AND p_action IN ('RECEIPT', 'MATCH')
GROUP BY shipment_line_id,
shipment_header_id
UNION ALL
SELECT shipment_header_id,
shipment_line_id,
0 squant,
0 maxtrans
FROM rcv_shipment_lines
WHERE shipment_header_id = p_header_id
AND p_action NOT IN ('RECEIPT', 'MATCH')
) distquery
WHERE
rsl.shipment_header_id = p_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = distquery.shipment_header_id
AND rsl.shipment_line_id = distquery.shipment_line_id
AND (distquery.maxtrans = rt.transaction_id OR
distquery.maxtrans = 0)
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id (+)
AND pol.line_type_id = plt.line_type_id
AND plt.order_type_lookup_code = 'QUANTITY' --bugfix 5525510
AND rsl.po_line_location_id = pll.line_location_id (+)
AND rsl.shipment_line_id = rt.shipment_line_id (+)
AND (rt.transaction_type = l_transaction_type
OR rt.transaction_type IS NULL)
AND (rt.transaction_id IN
(
SELECT transaction_id
FROM rcv_fte_transaction_lines
WHERE header_id = p_header_id
AND action = p_action
AND reported_flag IN ( 'N', 'U')
)
OR rt.transaction_id IN
(
SELECT max(transaction_id)
FROM rcv_transactions
WHERE shipment_header_id = p_header_id
AND transaction_type = 'RECEIVE'
AND p_action = 'RECEIPT_HEADER_UPD'
)
OR p_action NOT IN ('RTV', 'RECEIPT_ADD', 'RECEIPT', 'RECEIPT_HEADER_UPD', 'MATCH')
)
AND rsl.po_release_id = por.po_release_id (+)
AND fl.lookup_code = poh.type_lookup_code
AND fl.lookup_type = 'PO TYPE'
AND fl.language = USERENV('LANG')
AND rsl.item_id = msi.inventory_item_id (+)
AND rsl.to_organization_id = msi.organization_id (+)
AND rsl.unit_of_measure = muom.unit_of_measure (+)
AND rsl.secondary_unit_of_measure = suom.unit_of_measure (+)
AND rt.unit_of_measure = rtuom.unit_of_measure (+)
AND rt.secondary_unit_of_measure = rtsuom.unit_of_measure (+)
AND pol.unit_meas_lookup_code = puom.unit_of_measure (+)
AND pll.ship_via_lookup_code = orf.freight_code (+)
AND pll.ship_to_organization_id = orf.organization_id (+)
AND (orf.language = USERENV('LANG') OR orf.language IS NULL)
AND rsh.freight_carrier_code = orf2.freight_code (+)
AND rsh.ship_to_org_id = orf2.organization_id (+)
AND (orf2.language = USERENV('LANG') OR orf2.language IS NULL);
SELECT
'PO',
poh.po_header_id,
pol.po_line_id,
rsl.po_release_id,
poh.vendor_id,
poh.vendor_site_id,
pol.item_id,
pol.item_description,
pol.hazard_class_id,
pll.country_of_origin_code,
pll.ship_to_location_id,
poh.user_hold_flag,
pll.qty_rcv_tolerance,
pll.receive_close_tolerance,
pll.quantity_shipped,
rt.subinventory,
pol.item_revision,
rt.locator_id,
pll.need_by_date,
pll.promised_date,
orf.party_id,
poh.freight_terms_lookup_code,
poh.fob_lookup_code,
pol.vendor_product_num,
msi.unit_weight,
msi.weight_uom_code,
msi.unit_volume,
msi.volume_uom_code,
rsh.ship_to_org_id,
poh.segment1,
DECODE( poh.type_lookup_code,
'STANDARD', 1,
'BLANKET', 2,
1),
fl.meaning,
pll.quantity,
puom.uom_code,
pll.quantity_cancelled,
rsh.waybill_airbill_num,
nvl(rsl.packing_slip, rsh.packing_slip),
poh.org_id,
pol.line_num,
rsh.gross_weight,
rsh.gross_weight_uom_code,
rsh.net_weight,
rsh.net_weight_uom_code,
rsh.tar_weight,
rsh.tar_weight_uom_code,
pll.price_override,
poh.currency_code,
pol.qc_grade,
pll.secondary_quantity,
suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
pll.secondary_quantity,
rsl.secondary_quantity_shipped,
pll.secondary_quantity_cancelled,
suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
rsl.asn_lpn_id, -- fte doesn't care about rt.lpn_id
DECODE(p_action, 'ASN', rsl.quantity_shipped, NVL(rt.quantity,0)),
NVL(rtuom.uom_code, muom.uom_code),
rt.secondary_quantity,
NVL(rtsuom.uom_code, suom.uom_code),
rsl.po_line_location_id,
pll.shipment_num,
por.release_num,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
poh.shipping_control,
pll.drop_ship_flag,
rsh.shipment_header_id,
rsh.shipment_num,
rsh.receipt_num,
rsh.shipped_date,
DECODE(p_action, 'ASN', rsh.expected_receipt_date, NVL(rt.transaction_date, SYSDATE)),
rsh.bill_of_lading,
rsh.num_of_containers,
rsl.container_num,
rsl.truck_num,
rsl.shipment_line_id,
pll.qty_rcv_exception_code,
rt.transaction_id,
rsl.shipment_line_id,
rsh.shipment_header_id,
pll.closed_flag,
pll.cancel_flag,
pll.closed_code,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
pll.receipt_days_exception_code,
pll.enforce_ship_to_location_code,
poh.revision_num,
por.revision_num,
pll.last_update_date,
rsl.ship_to_location_id,
rsl.item_id,
rsl.item_description,
rt.country_of_origin_code,
rsl.item_revision,
orf2.party_id,
rsh.freight_terms,
rsl.vendor_item_num,
rt.qc_grade,
rsh.asn_type
BULK COLLECT INTO
x_fte_rec.source_code,
x_fte_rec.header_id,
x_fte_rec.line_id,
x_fte_rec.source_blanket_reference_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.hold_code,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.subinventory,
x_fte_rec.revision,
x_fte_rec.locator_id,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.carrier_id,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.supplier_item_num,
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code,
x_fte_rec.organization_id,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.tracking_number,
x_fte_rec.packing_slip_number,
x_fte_rec.org_id,
x_fte_rec.source_line_number,
x_fte_rec.rcv_gross_weight,
x_fte_rec.rcv_gross_weight_uom_code,
x_fte_rec.rcv_net_weight,
x_fte_rec.rcv_net_weight_uom_code,
x_fte_rec.rcv_tare_weight,
x_fte_rec.rcv_tare_weight_uom_code,
x_fte_rec.unit_list_price,
x_fte_rec.currency_code,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.shipped_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.lpn_id,
x_fte_rec.received_quantity,
x_fte_rec.received_quantity_uom,
x_fte_rec.received_quantity2,
x_fte_rec.received_quantity2_uom,
x_fte_rec.po_shipment_line_id,
x_fte_rec.po_shipment_line_number,
x_fte_rec.source_blanket_reference_num,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.shipping_control,
x_fte_rec.drop_ship_flag,
x_fte_rec.shipment_header_id,
x_fte_rec.shipment_num,
x_fte_rec.receipt_num,
x_fte_rec.shipped_date,
x_fte_rec.expected_receipt_date,
x_fte_rec.bill_of_lading,
x_fte_rec.num_of_containers,
x_fte_rec.container_num,
x_fte_rec.truck_num,
x_fte_rec.shipment_line_id,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.rcv_transaction_id,
x_fte_rec.rcv_parent_shipment_line_id,
x_fte_rec.rcv_parent_shipment_header_id,
x_fte_rec.closed_flag,
x_fte_rec.cancelled_flag,
x_fte_rec.closed_code,
x_fte_rec.source_line_type_code,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.po_revision,
x_fte_rec.release_revision,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.rcv_ship_to_location_id,
x_fte_rec.rcv_inventory_item_id,
x_fte_rec.rcv_item_description,
x_fte_rec.rcv_country_of_origin,
x_fte_rec.rcv_revision,
x_fte_rec.rcv_carrier_id,
x_fte_rec.rcv_freight_terms_code,
x_fte_rec.rcv_supplier_item_num,
x_fte_rec.rcv_preferred_grade,
x_fte_rec.asn_type
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
rcv_transactions rt,
mtl_system_items msi,
fnd_lookup_values fl,
po_releases_all por,
mtl_units_of_measure muom,
mtl_units_of_measure suom,
mtl_units_of_measure puom,
mtl_units_of_measure rtuom,
mtl_units_of_measure rtsuom,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
org_freight orf,
org_freight orf2,
po_line_types_b plt
WHERE
rsl.shipment_header_id = p_header_id
AND rsl.shipment_line_id = p_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id (+)
AND pol.line_type_id = plt.line_type_id
AND plt.order_type_lookup_code = 'QUANTITY' --bugfix 5525510
AND rsl.po_line_location_id = pll.line_location_id (+)
AND rsl.shipment_line_id = rt.shipment_line_id (+)
AND (rt.transaction_type = l_transaction_type
OR rt.transaction_type IS NULL)
AND (rt.transaction_id = l_transaction_id
OR l_transaction_id IS NULL)
AND rsl.po_release_id = por.po_release_id (+)
AND fl.lookup_code = poh.type_lookup_code
AND fl.lookup_type = 'PO TYPE'
AND fl.language = USERENV('LANG')
AND rsl.item_id = msi.inventory_item_id (+)
AND rsl.to_organization_id = msi.organization_id (+)
AND rsl.unit_of_measure = muom.unit_of_measure (+)
AND rt.unit_of_measure = rtuom.unit_of_measure (+)
AND rt.secondary_unit_of_measure = rtsuom.unit_of_measure(+)
AND rsl.secondary_unit_of_measure = suom.unit_of_measure (+)
AND pol.unit_meas_lookup_code = puom.unit_of_measure (+)
AND pll.ship_via_lookup_code = orf.freight_code (+)
AND pll.ship_to_organization_id = orf.organization_id (+)
AND (orf.language = USERENV('LANG') OR orf.language IS NULL)
AND rsh.freight_carrier_code = orf2.freight_code (+)
AND rsh.ship_to_org_id = orf2.organization_id (+)
AND (orf2.language = USERENV('LANG') OR orf2.language IS NULL);
WSH_BULK_PROCESS_GRP.create_update_delivery_details
(
p_api_version_number => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_action_prms => p_action_rec,
p_line_rec => x_fte_rec,
x_Out_Rec => l_fte_out_rec,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT 'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
1, -- stands for 'PO'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.shipping_control,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_code, -- Header
x_fte_rec.header_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.shipping_control,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POH.po_header_id = p_header_id
AND PDT.document_type_code = 'PO'
AND PDT.document_subtype = POH.type_lookup_code
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.approved_flag, 'N') = 'Y'
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT 'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
1, -- stands for 'PO'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.shipping_control,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_code, -- Header
x_fte_rec.header_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.shipping_control,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POH.po_header_id = p_header_id
AND PDT.document_type_code = 'PO'
AND PDT.document_subtype = POH.type_lookup_code
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.cancel_flag, 'N') = 'Y';
SELECT 'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
1, -- stands for 'PO'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.shipping_control,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_code, -- Header
x_fte_rec.header_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.shipping_control,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POH.po_header_id = p_header_id
AND PDT.document_type_code = 'PO'
AND PDT.document_subtype = POH.type_lookup_code
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') = 'OPEN'
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT 'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
1, -- stands for 'PO'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.shipping_control,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_code, -- Header
x_fte_rec.header_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.shipping_control,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POH.po_header_id = p_header_id
AND PDT.document_type_code = 'PO'
AND PDT.document_subtype = POH.type_lookup_code
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING')
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT 'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
1, -- stands for 'PO'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.shipping_control,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_code, -- Header
x_fte_rec.header_id,
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.shipping_control,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POH.po_header_id = p_header_id
AND PDT.document_type_code = 'PO'
AND PDT.document_subtype = POH.type_lookup_code
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') = 'FINALLY CLOSED';
SELECT POR.po_release_id,
POR.release_num,
POR.shipping_control,
POR.revision_num,
'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
2, -- stands for 'RELEASE'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_blanket_reference_id, -- Release Header
x_fte_rec.source_blanket_reference_num,
x_fte_rec.shipping_control,
x_fte_rec.release_revision,
x_fte_rec.source_code,
x_fte_rec.header_id, -- PO Header
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_RELEASES POR,
PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POR.po_release_id = p_header_id
AND POH.po_header_id = POR.po_header_id
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POLL.po_release_id = POR.po_release_id
AND PDT.document_type_code = 'PA'
AND PDT.document_subtype = POR.release_type
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.approved_flag, 'N') = 'Y'
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT POR.po_release_id,
POR.release_num,
POR.shipping_control,
POR.revision_num,
'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
2, -- stands for 'RELEASE'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_blanket_reference_id, -- Release Header
x_fte_rec.source_blanket_reference_num,
x_fte_rec.shipping_control,
x_fte_rec.release_revision,
x_fte_rec.source_code,
x_fte_rec.header_id, -- PO Header
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_RELEASES POR,
PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POR.po_release_id = p_header_id
AND POH.po_header_id = POR.po_header_id
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POLL.po_release_id = POR.po_release_id
AND PDT.document_type_code = 'PA'
AND PDT.document_subtype = POR.release_type
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.cancel_flag, 'N') = 'Y';
SELECT POR.po_release_id,
POR.release_num,
POR.shipping_control,
POR.revision_num,
'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
2, -- stands for 'RELEASE'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_blanket_reference_id, -- Release Header
x_fte_rec.source_blanket_reference_num,
x_fte_rec.shipping_control,
x_fte_rec.release_revision,
x_fte_rec.source_code,
x_fte_rec.header_id, -- PO Header
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_RELEASES POR,
PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POR.po_release_id = p_header_id
AND POH.po_header_id = POR.po_header_id
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POLL.po_release_id = POR.po_release_id
AND PDT.document_type_code = 'PA'
AND PDT.document_subtype = POR.release_type
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') = 'OPEN'
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT POR.po_release_id,
POR.release_num,
POR.shipping_control,
POR.revision_num,
'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
2, -- stands for 'RELEASE'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_blanket_reference_id, -- Release Header
x_fte_rec.source_blanket_reference_num,
x_fte_rec.shipping_control,
x_fte_rec.release_revision,
x_fte_rec.source_code,
x_fte_rec.header_id, -- PO Header
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_RELEASES POR,
PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POR.po_release_id = p_header_id
AND POH.po_header_id = POR.po_header_id
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POLL.po_release_id = POR.po_release_id
AND PDT.document_type_code = 'PA'
AND PDT.document_subtype = POR.release_type
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING')
AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
SELECT POR.po_release_id,
POR.release_num,
POR.shipping_control,
POR.revision_num,
'PO', -- source code
POH.po_header_id,
POH.vendor_id,
POH.vendor_site_id,
POH.user_hold_flag,
POH.freight_terms_lookup_code,
POH.fob_lookup_code,
POH.segment1,
2, -- stands for 'RELEASE'
PDT.type_name,
POH.org_id,
POH.currency_code,
POH.revision_num,
POL.po_line_id,
POL.item_id,
POL.item_description,
POL.hazard_class_id,
POL.item_revision,
POL.vendor_product_num,
POL.line_num,
DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
POLL.line_location_id,
POLL.country_of_origin_code,
POLL.ship_to_location_id,
POLL.qty_rcv_tolerance,
POLL.receive_close_tolerance,
POLL.quantity_shipped,
POLL.need_by_date,
POLL.promised_date,
POLL.ship_to_organization_id,
POLL.quantity,
MUOM.uom_code,
POLL.quantity_cancelled,
POLL.price_override,
POLL.preferred_grade,
POLL.secondary_quantity,
MUOM1.uom_code,
POLL.secondary_quantity,
POLL.secondary_quantity_cancelled,
MUOM1.uom_code,
POLL.shipment_num,
POLL.days_early_receipt_allowed,
POLL.days_late_receipt_allowed,
POLL.drop_ship_flag,
POLL.qty_rcv_exception_code,
POLL.closed_flag,
POLL.closed_code,
POLL.cancel_flag,
POLL.receipt_days_exception_code,
POLL.enforce_ship_to_location_code,
POLL.last_update_date,
FRT.party_id,
MSI.unit_weight,
MSI.weight_uom_code,
MSI.unit_volume,
MSI.volume_uom_code
BULK COLLECT INTO
x_fte_rec.source_blanket_reference_id, -- Release Header
x_fte_rec.source_blanket_reference_num,
x_fte_rec.shipping_control,
x_fte_rec.release_revision,
x_fte_rec.source_code,
x_fte_rec.header_id, -- PO Header
x_fte_rec.vendor_id,
x_fte_rec.ship_from_site_id,
x_fte_rec.hold_code,
x_fte_rec.freight_terms_code,
x_fte_rec.fob_point_code,
x_fte_rec.source_header_number,
x_fte_rec.source_header_type_id,
x_fte_rec.source_header_type_name,
x_fte_rec.org_id,
x_fte_rec.currency_code,
x_fte_rec.po_revision,
x_fte_rec.line_id, -- Line
x_fte_rec.inventory_item_id,
x_fte_rec.item_description,
x_fte_rec.hazard_class_id,
x_fte_rec.revision,
x_fte_rec.supplier_item_num,
x_fte_rec.source_line_number,
x_fte_rec.source_line_type_code,
x_fte_rec.po_shipment_line_id, -- Shipment
x_fte_rec.country_of_origin,
x_fte_rec.ship_to_location_id,
x_fte_rec.ship_tolerance_above,
x_fte_rec.ship_tolerance_below,
x_fte_rec.shipped_quantity,
x_fte_rec.request_date,
x_fte_rec.schedule_ship_date,
x_fte_rec.organization_id,
x_fte_rec.ordered_quantity,
x_fte_rec.order_quantity_uom,
x_fte_rec.cancelled_quantity,
x_fte_rec.unit_list_price,
x_fte_rec.preferred_grade,
x_fte_rec.ordered_quantity2,
x_fte_rec.ordered_quantity_uom2,
x_fte_rec.requested_quantity2,
x_fte_rec.cancelled_quantity2,
x_fte_rec.requested_quantity_uom2,
x_fte_rec.po_shipment_line_number,
x_fte_rec.days_early_receipt_allowed,
x_fte_rec.days_late_receipt_allowed,
x_fte_rec.drop_ship_flag,
x_fte_rec.qty_rcv_exception_code,
x_fte_rec.closed_flag,
x_fte_rec.closed_code,
x_fte_rec.cancelled_flag,
x_fte_rec.receipt_days_exception_code,
x_fte_rec.enforce_ship_to_location_code,
x_fte_rec.shipping_details_updated_on,
x_fte_rec.carrier_id, -- Others
x_fte_rec.net_weight,
x_fte_rec.weight_uom_code,
x_fte_rec.volume,
x_fte_rec.volume_uom_code
FROM PO_RELEASES POR,
PO_HEADERS POH,
PO_LINES POL,
PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES_B PLT,
ORG_FREIGHT_TL FRT,
MTL_SYSTEM_ITEMS_B MSI,
PO_DOCUMENT_TYPES_VL PDT,
MTL_UNITS_OF_MEASURE MUOM,
MTL_UNITS_OF_MEASURE MUOM1
WHERE POR.po_release_id = p_header_id
AND POH.po_header_id = POR.po_header_id
AND POL.po_header_id = POH.po_header_id
AND POLL.po_line_id = POL.po_line_id
AND POLL.po_release_id = POR.po_release_id
AND PDT.document_type_code = 'PA'
AND PDT.document_subtype = POR.release_type
AND POLL.line_location_id
= NVL(p_line_location_id, POLL.line_location_id)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND FRT.freight_code (+) = POH.ship_via_lookup_code
AND FRT.language (+) = USERENV('LANG')
AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MSI.inventory_item_id (+) = POL.item_id
AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
= POLL.ship_to_organization_id
AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
AND NVL(POLL.closed_code, 'OPEN') = 'FINALLY CLOSED';