[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
wdd.delivery_detail_id picking_line_detail_id
, wdd.organization_id warehouse_id
, ol.ship_to_org_id ultimate_ship_to_id
, wnd.initial_pickup_date date_closed
, ol.line_id
, oh.order_number
, ras.bill_to_site_use_id
, ol.item_type_code
, ol.link_to_line_id
FROM
WSH_NEW_DELIVERIES_OB_GRP_V wnd
, wsh_delivery_assignments_v wda
, WSH_DELIVERY_DETAILS_OB_GRP_V wdd
, hr_organization_information hoi
, OE_ORDER_LINES_ALL ol
, OE_ORDER_HEADERS_ALL oh
, HZ_CUST_SITE_USES_ALL ras
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = ol.line_id
AND ol.header_id = oh.header_id
AND wdd.source_code = 'OE'
AND wnd.organization_id = hoi.organization_id --fix perf bug 4912552
AND hoi.org_information_context = 'Accounting Information'
AND ol.ship_to_org_id = ras.site_use_id
--Bugfix 16214456: OE schema is getting obsoleted.
--AND OE_INSTALL.Get_Active_Product = 'ONT'
AND oh.order_source_id = 10
AND wdd.shipped_quantity > 0
AND wnd.status_code in ('IT','CL')
AND wdd.mvt_stat_status in ('NEW','MODIFIED')
--AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND wnd.initial_pickup_date between p_start_date and p_end_date;
SELECT
wdd.delivery_detail_id picking_line_detail_id
, wdd.organization_id warehouse_id
, ol.ship_to_org_id ultimate_ship_to_id
, wnd.initial_pickup_date date_closed
, ol.line_id
, oh.order_number
, ras.bill_to_site_use_id
, ol.item_type_code
, ol.link_to_line_id
FROM
WSH_NEW_DELIVERIES_OB_GRP_V wnd
, wsh_delivery_assignments_v wda
, WSH_DELIVERY_DETAILS_OB_GRP_V wdd
, hr_organization_information hoi
, OE_ORDER_LINES_ALL ol
, OE_ORDER_HEADERS_ALL oh
, HZ_CUST_SITE_USES_ALL ras
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = ol.line_id
AND ol.header_id = oh.header_id
AND wdd.source_code = 'OE'
AND wnd.organization_id = hoi.organization_id --fix perf bug2812364
AND hoi.org_information_context = 'Accounting Information'
AND ol.ship_to_org_id = ras.site_use_id
--Bugfix 16214456: OE schema is getting obsoleted.
--AND OE_INSTALL.Get_Active_Product = 'ONT'
AND wdd.shipped_quantity > 0
AND wnd.status_code in ('IT','CL')
AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORDISP')
--AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND wnd.initial_pickup_date between p_start_date and p_end_date;
SELECT
wdd.delivery_detail_id picking_line_detail_id
, wdd.organization_id warehouse_id
, ol.ship_to_org_id ultimate_ship_to_id
, wnd.initial_pickup_date date_closed
, ol.line_id
, oh.order_number
, ras.bill_to_site_use_id
, ol.item_type_code
, ol.link_to_line_id
FROM
WSH_NEW_DELIVERIES_OB_GRP_V wnd
, wsh_delivery_assignments_v wda
, WSH_DELIVERY_DETAILS_OB_GRP_V wdd
, OE_ORDER_LINES_ALL ol
, OE_ORDER_HEADERS_ALL oh
, HZ_CUST_SITE_USES_ALL ras
, hr_organization_information hoi
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = ol.line_id
AND ol.header_id = oh.header_id
AND wdd.source_code = 'OE'
AND wnd.organization_id = hoi.organization_id --fix perf bug2812364
AND hoi.org_information_context = 'Accounting Information'
AND ol.ship_to_org_id = ras.site_use_id
--Bugfix 16214456: OE schema is getting obsoleted.
--AND OE_INSTALL.Get_Active_Product = 'ONT'
AND wdd.shipped_quantity > 0
AND wnd.status_code in ('IT','CL')
AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORDISP')
--AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND wnd.initial_pickup_date is NOT NULL
AND wnd.name = p_movement_transaction.shipment_reference;
SELECT
wdd.delivery_detail_id picking_line_detail_id
, wdd.organization_id warehouse_id
, ol.ship_to_org_id ultimate_ship_to_id
, wnd.initial_pickup_date date_closed
, ol.line_id
, wdd.source_header_number
, ol.item_type_code
, ol.link_to_line_id
FROM
WSH_NEW_DELIVERIES_OB_GRP_V wnd
, wsh_delivery_assignments_v wda
, WSH_DELIVERY_DETAILS_OB_GRP_V wdd
, OE_ORDER_LINES_ALL ol
, hr_organization_information hoi /*Bug 8467743*/
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = ol.line_id
AND wdd.source_code = 'OE'
--Bugfix 16214456: OE schema is getting obsoleted.
--AND OE_INSTALL.Get_Active_Product = 'ONT'
AND wdd.shipped_quantity > 0
AND ol.order_source_id <> 10
AND wnd.status_code in ('IT','CL')
AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORARVL')
AND wnd.initial_pickup_date between p_start_date and p_end_date
AND hoi.org_information_context = 'Operating Unit Information' /*Bug 8467743*/
AND hoi.organization_id = nvl(ol.org_id,ol.sold_from_org_id) /*Bug 8467743*/
--AND p_movement_transaction.entity_org_id =TO_NUMBER(hoi.org_information2); /*Bug 8467743*/
SELECT
--oola.ship_to_org_id
wdd.fob_code
, NVL(wdd.ship_method_code,'3')
, wdd.delivery_detail_id
--, oola.line_id
, ooha.header_id
, ooha.order_number
, oola.line_number
, wdd.organization_id
--, oola.sold_from_org_id
, oola.org_id
, wdd.delivery_detail_id
, wdd.shipped_quantity
, wdd.mvt_stat_status
, wdd.movement_id
, ooha.sold_to_org_id
, nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
, ooha.sold_to_org_id
, wdd.requested_quantity_uom
, wdd.inventory_item_id
, si.description
, si.primary_uom_code
, ooha.transactional_curr_code
, ooha.conversion_type_code
, ooha.conversion_rate
, ooha.conversion_rate_date
, oola.unit_selling_price
, oola.orig_sys_line_ref
, ooha.orig_sys_document_ref
, ooha.order_source_id
, rac.party_name
--Bugfix 9676611: customer_number should come from HZ_CUST_ACCOUNTS
--, rac.party_number
, hzc.account_number
, substrb(rac.province,1,30)
, wnd.name
, oola.item_type_code
, oola.order_quantity_uom
FROM
OE_ORDER_HEADERS_ALL ooha
, OE_ORDER_LINES_ALL oola
, wsh_delivery_details_ob_grp_v wdd
, wsh_new_deliveries_ob_grp_v wnd
, wsh_delivery_assignments_v wda
, HZ_PARTIES rac
, HZ_CUST_ACCOUNTS hzc
, MTL_SYSTEM_ITEMS si
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND ooha.header_id = oola.header_id
AND oola.line_id = wdd.source_line_id
AND rac.party_id = hzc.party_id
AND ooha.sold_to_org_id = hzc.cust_account_id
AND wdd.inventory_item_id = si.inventory_item_id
AND wdd.organization_id = si.organization_id
AND wdd.delivery_detail_id = x_movement_transaction.picking_line_detail_id;
SELECT
item_cost
FROM
CST_ITEM_COSTS_FOR_GL_VIEW
WHERE organization_id = x_movement_transaction.organization_id
AND inventory_item_id = x_movement_transaction.inventory_item_id;
SELECT
TO_NUMBER(HOI2.ORG_INFORMATION3)
INTO
l_shipment_transaction.org_id
FROM
HR_ORGANIZATION_INFORMATION HOI1
, HR_ORGANIZATION_INFORMATION HOI2
, MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND HOI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND HOI2.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND mp.organization_id = x_movement_transaction.organization_id;
SELECT
oola.line_number
, oola.unit_selling_price
, NVL(oola.shipped_quantity, oola.fulfilled_quantity)
, oola.order_quantity_uom
, oola.order_quantity_uom
, oola.ship_from_org_id
, oola.inventory_item_id
, msi.description
, msi.primary_uom_code
FROM
oe_order_lines_all oola
, mtl_system_items msi
WHERE oola.inventory_item_id = msi.inventory_item_id
AND oola.ship_from_org_id = msi.organization_id
AND line_id = p_link_to_line_id;
SELECT
source_document_id
, source_document_line_id
FROM
oe_order_lines_all oola
, po_requisition_headers_all prha
WHERE prha.requisition_header_id = oola.source_document_id
AND line_id = x_movement_transaction.order_line_id;
SELECT
source_organization_id
, destination_organization_id
FROM
po_requisition_lines_all
WHERE
requisition_line_id = x_movement_transaction.requisition_line_id;
PROCEDURE Update_SO_Transactions
( p_movement_transaction IN
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
, p_status IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_SO_Transactions';
SELECT mvt_stat_status
INTO l_mvt_stat_status
FROM wsh_delivery_details_ob_grp_v
WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
UPDATE wsh_delivery_details
SET mvt_stat_status = 'FORDISP'
, movement_id = p_movement_transaction.movement_id
WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
UPDATE wsh_delivery_details
SET mvt_stat_status = 'FORARVL'
, movement_id = p_movement_transaction.movement_id
WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
UPDATE wsh_delivery_details
SET mvt_stat_status = 'PROCESSED'
, movement_id = p_movement_transaction.movement_id
WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
UPDATE wsh_delivery_details
SET mvt_stat_status = 'PROCESSED'
, movement_id = p_movement_transaction.movement_id
WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
END Update_SO_Transactions;
PROCEDURE Update_KIT_SO_Transactions
( p_movement_id IN NUMBER
, p_delivery_detail_id IN NUMBER
, p_link_to_line_id IN NUMBER
, p_status IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_KIT_SO_Transactions';
SELECT mvt_stat_status
INTO l_mvt_stat_status
FROM wsh_delivery_details_ob_grp_v
WHERE delivery_detail_id = p_delivery_detail_id;
UPDATE wsh_delivery_details
SET mvt_stat_status = 'FORDISP'
, movement_id = p_movement_id
WHERE source_line_id IN (SELECT line_id --fix bug 4185582
FROM oe_order_lines_all
WHERE link_to_line_id = p_link_to_line_id);
UPDATE wsh_delivery_details
SET mvt_stat_status = 'FORARVL'
, movement_id = p_movement_id
WHERE source_line_id IN (SELECT line_id --fix bug 4185582
FROM oe_order_lines_all
WHERE link_to_line_id = p_link_to_line_id);
UPDATE wsh_delivery_details
SET mvt_stat_status = 'PROCESSED'
, movement_id = p_movement_id
WHERE source_line_id IN (SELECT line_id --fix bug 4185582
FROM oe_order_lines_all
WHERE link_to_line_id = p_link_to_line_id);
UPDATE wsh_delivery_details
SET mvt_stat_status = 'PROCESSED'
, movement_id = p_movement_id
WHERE source_line_id IN (SELECT line_id --fix bug 4185582
FROM oe_order_lines_all
WHERE link_to_line_id = p_link_to_line_id);
END Update_KIT_SO_Transactions;
SELECT
mvt_stat_status
FROM
wsh_delivery_details
WHERE
delivery_detail_id = p_delivery_detail_id;
SELECT
mvt_stat_status
FROM
wsh_delivery_details
WHERE
delivery_detail_id = p_delivery_detail_id;