[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.po_header_id
, rcv.po_line_id
, rcv.po_line_location_id
, rcv.source_document_code
, rcv.vendor_site_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
FROM
RCV_TRANSACTIONS rcv
, hr_organization_information hoi
WHERE rcv.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND rcv.mvt_stat_status = 'NEW'
AND (rcv.transaction_type IN ('RECEIVE','RETURN TO VENDOR','MATCH')
OR (rcv.transaction_type = 'CORRECT'
AND rcv.destination_type_code = 'RECEIVING'))
AND rcv.source_document_code = 'PO'
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
ORDER BY rcv.transaction_id;
SELECT
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.po_header_id
, rcv.po_line_id
, rcv.po_line_location_id
, rcv.source_document_code
, rcv.vendor_site_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
FROM
RCV_TRANSACTIONS rcv
,RCV_SHIPMENT_HEADERS rsh
,hr_organization_information hoi
WHERE rcv.shipment_header_id = rsh.shipment_header_id
AND rcv.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND rsh.ship_to_org_id = hoi.organization_id
AND rcv.mvt_stat_status = 'NEW'
AND (rcv.transaction_type IN ('RECEIVE','RETURN TO VENDOR','MATCH')
OR (rcv.transaction_type = 'CORRECT'
AND rcv.destination_type_code = 'RECEIVING'))
AND rcv.source_document_code = 'PO'
AND rsh.receipt_num = p_movement_transaction.receipt_num
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND rcv.organization_id = p_movement_transaction.organization_id;
SELECT
count(*)
FROM
oe_drop_ship_sources
WHERE po_header_id = x_movement_transaction.po_header_id
AND po_line_id = x_movement_transaction.po_line_id
AND line_location_id = x_movement_transaction.po_line_location_id
GROUP BY line_location_id;
SELECT
transaction_id
FROM
rcv_transactions
WHERE po_header_id = x_movement_transaction.po_header_id
AND po_line_id = x_movement_transaction.po_line_id
AND po_line_location_id = x_movement_transaction.po_line_location_id
AND transaction_type = 'RECEIVE'
ORDER BY transaction_id;
SELECT
po_header_id
, po_line_id
, header_id
, line_id
, drop_ship_source_id
, destination_organization_id
FROM
OE_DROP_SHIP_SOURCES
WHERE po_header_id = x_movement_transaction.po_header_id
AND po_line_id = x_movement_transaction.po_line_id
AND line_location_id = x_movement_transaction.po_line_location_id
ORDER BY line_id;
SELECT
po.po_header_id
, po.transaction_type
, po.transaction_id
, po.parent_transaction_id
, po.movement_id
, po.po_line_id
, po.po_line_location_id
, po.organization_id
, po.currency_code
, po.currency_conversion_type
, po.currency_conversion_rate
, po.currency_conversion_date
, poh.vendor_id
, poh.vendor_site_id
, po.shipment_header_id
, po.shipment_line_id
, po.invoice_id
, rsl.item_id
, rsl.item_description
, po.uom_code
, po.source_doc_unit_of_measure
, po.quantity
, po.primary_quantity
--, nvl(cst.item_cost,0)
, poh.fob_lookup_code
, poh.ship_to_location_id
, NVL(po.po_unit_price,0)
, po.country_of_origin_code
, po.requisition_line_id
, NVL(rsh.freight_carrier_code,'3')
, po.po_release_id
, poh.type_lookup_code
, po.consigned_flag
FROM
RCV_TRANSACTIONS po
, RCV_SHIPMENT_HEADERS rsh
, RCV_SHIPMENT_LINES rsl
, PO_HEADERS_ALL poh
, PO_LINES_ALL pol
--, CST_ITEM_COSTS_FOR_GL_VIEW cst
WHERE po.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND po.shipment_line_id = rsl.shipment_line_id
AND po.po_line_id = pol.po_line_id
AND poh.po_header_id = pol.po_header_id
--AND rsl.to_organization_id = cst.organization_id (+)
--AND rsl.item_id = cst.inventory_item_id (+)
AND po.transaction_id = x_movement_transaction.rcv_transaction_id;
SELECT
muc.uom_code
FROM
MTL_UOM_CONVERSIONS_VIEW muc
WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
AND muc.organization_id = x_movement_transaction.organization_id
AND muc.unit_of_measure = l_source_unit_measure;
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
period_name
FROM
MTL_MOVEMENT_STATISTICS
WHERE movement_id = l_parent_id;
SELECT
SUM(quantity)
, SUM(primary_quantity)
FROM
rcv_transactions
WHERE parent_transaction_id = x_movement_transaction.rcv_transaction_id
AND mvt_stat_status = 'NEW'
AND transaction_type = 'CORRECT';
SELECT
oola.ship_to_org_id
, ooha.fob_point_code
, NVL(ooha.freight_terms_code, '3')
, oola.line_id
, ooha.header_id
, ooha.order_number
, oola.line_number
--, oola.ship_from_org_id keep the organization_id from drop ship PO
, oola.sold_from_org_id
-- , oola.shipped_quantity
, nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
, ooha.sold_to_org_id
, oola.order_quantity_uom
, oola.inventory_item_id
, si.description
, si.primary_uom_code
, ooha.transactional_curr_code
, ooha.conversion_type_code
, ooha.conversion_rate
, ooha.conversion_rate_date
--, nvl(cst.item_cost,0)
, NVL(oola.unit_selling_price,0)
, abs(nvl(oola.unit_selling_price,0) * nvl(oola.shipped_quantity,0)) doc_line_ext
, oola.orig_sys_line_ref
, ooha.orig_sys_document_ref
, rac.party_name
, rac.party_number
FROM
OE_ORDER_HEADERS_ALL ooha
, OE_ORDER_LINES_ALL oola
, HZ_PARTIES rac
, HZ_CUST_ACCOUNTS hzc
, MTL_SYSTEM_ITEMS si
--, CST_ITEM_COSTS_FOR_GL_VIEW cst
WHERE ooha.header_id = oola.header_id
AND oola.inventory_item_id = si.inventory_item_id
AND oola.ship_from_org_id = si.organization_id
AND rac.party_id = hzc.party_id
AND ooha.sold_to_org_id = hzc.cust_account_id
--AND oola.ship_from_org_id = cst.organization_id(+)
--AND oola.inventory_item_id = cst.inventory_item_id(+)
AND oola.line_id = x_movement_transaction.order_line_id;
SELECT
cst.item_cost
FROM
CST_ITEM_COSTS_FOR_GL_VIEW cst
, oe_order_lines_all oola
WHERE cst.organization_id = oola.ship_from_org_id
AND cst.inventory_item_id = oola.inventory_item_id
AND oola.line_id = x_movement_transaction.order_line_id;
PROCEDURE Update_PO_Transactions
( p_movement_transaction IN
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
, p_mvt_stat_status IN RCV_TRANSACTIONS.mvt_stat_status%TYPE /*Bug 7165989 */
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_PO_Transactions';
/* 7165989 - Update mvt_stat_status in RCV_TRANSACTIONS based of the records */
/* created for the RMA triangulation. Any non-RMA triangulation should be stamped*/
/* with PROCESSED status*/
IF (p_mvt_stat_status is NULL OR (p_mvt_stat_status <> 'FORDISP'
AND p_mvt_stat_status <> 'FORARVL') )
THEN
UPDATE RCV_TRANSACTIONS
SET mvt_stat_status = 'PROCESSED'
, movement_id = p_movement_transaction.movement_id
WHERE transaction_id = p_movement_transaction.rcv_transaction_id;
UPDATE RCV_TRANSACTIONS
SET mvt_stat_status = p_mvt_stat_status
, movement_id = p_movement_transaction.movement_id
WHERE transaction_id = p_movement_transaction.rcv_transaction_id;
END Update_PO_Transactions;
SELECT NVL(vendor_site_id,null)
, NVL(parent_transaction_id,null)
, transaction_type
FROM RCV_TRANSACTIONS
WHERE transaction_id = p_parent_id;
SELECT
po.po_release_id
, po.po_line_location_id
, poh.type_lookup_code
FROM
RCV_TRANSACTIONS po
, PO_HEADERS_ALL poh
WHERE po.po_header_id = poh.po_header_id
AND po.transaction_id = x_movement_transaction.rcv_transaction_id;
SELECT
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.source_document_code
, rcv.customer_site_id
, rcv.oe_order_header_id
, rcv.oe_order_line_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
, rcv.mvt_stat_status -- 7165989
FROM
RCV_TRANSACTIONS rcv
, oe_order_lines_all oola
WHERE rcv.oe_order_line_id = oola.line_id
AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
AND rcv.transaction_type IN ('DELIVER')
AND rcv.source_document_code = 'RMA'
AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
AND p_movement_transaction.entity_org_id =
XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
('SOLD_TO', oola.sold_to_org_id
, null, null, oola.org_id)
UNION
SELECT --regular case: receipt LE is same as creating LE
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.source_document_code
, rcv.customer_site_id
, rcv.oe_order_header_id
, rcv.oe_order_line_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
, rcv.mvt_stat_status -- 7165989
FROM
RCV_TRANSACTIONS rcv
, hr_organization_information hoi
WHERE rcv.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
AND rcv.transaction_type IN ('DELIVER')
AND rcv.source_document_code = 'RMA'
AND rcv.transaction_date BETWEEN p_start_date AND p_end_date;
SELECT
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.source_document_code
, rcv.customer_site_id
, rcv.oe_order_header_id
, rcv.oe_order_line_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
, rcv.mvt_stat_status -- 7165989
FROM
RCV_TRANSACTIONS rcv
, RCV_SHIPMENT_HEADERS rsh
, oe_order_lines_all oola
WHERE rcv.shipment_header_id = rsh.shipment_header_id
AND rcv.oe_order_line_id = oola.line_id
AND rsh.receipt_num = p_movement_transaction.receipt_num
AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
AND rcv.transaction_type IN ('DELIVER')
AND rcv.source_document_code = 'RMA'
AND p_movement_transaction.entity_org_id =
XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
('SOLD_TO', oola.sold_to_org_id
, null, null, oola.org_id)
UNION
SELECT
rcv.transaction_id
, rcv.parent_transaction_id
, rcv.transaction_type
, rcv.source_document_code
, rcv.customer_site_id
, rcv.oe_order_header_id
, rcv.oe_order_line_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
, rcv.mvt_stat_status -- 7165989
FROM
RCV_TRANSACTIONS rcv
, RCV_SHIPMENT_HEADERS rsh
, hr_organization_information hoi
WHERE rcv.shipment_header_id = rsh.shipment_header_id
AND rcv.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND rsh.ship_to_org_id = hoi.organization_id
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND rsh.receipt_num = p_movement_transaction.receipt_num
AND rcv.organization_id = p_movement_transaction.organization_id
AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
AND rcv.transaction_type IN ('DELIVER')
AND rcv.source_document_code = 'RMA';
SELECT
po.transaction_id
, po.organization_id
, abs(po.quantity)
, po.uom_code
--, po.transaction_date timezone support do not populate again
, abs(po.primary_quantity)
, rsl.item_id
, rsl.item_description
--, si.description
--, nvl(cst.item_cost,0)
, ooha.fob_point_code
, NVL(abs(oola.unit_selling_price),0)
--, po.oe_order_header_id
--, po.oe_order_line_id
--, ooha.ship_to_org_id
, oola.ship_to_org_id /* bug 6839063 - line details are used instead of header details */
, ooha.sold_to_org_id
, nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
, ooha.transactional_curr_code
, ooha.conversion_type_code
, ooha.conversion_rate
, ooha.conversion_rate_date
, rsl.shipment_header_id
, rsl.shipment_line_id
, ooha.org_id
, oola.order_quantity_uom
, ooha.sold_from_org_id -- 7165989
FROM
RCV_TRANSACTIONS po
, RCV_SHIPMENT_HEADERS rsh
, RCV_SHIPMENT_LINES rsl
, OE_ORDER_HEADERS_ALL ooha
, OE_ORDER_LINES_ALL oola
--, MTL_SYSTEM_ITEMS si
--, CST_ITEM_COSTS_FOR_GL_VIEW cst
WHERE po.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_Id
AND po.shipment_line_id = rsl.shipment_line_id
AND po.oe_order_header_id = ooha.header_id
AND ooha.header_id = oola.header_id
AND po.oe_order_line_id = oola.line_id
--AND rsh.organization_id = si.organization_id
--AND rsl.item_id = si.inventory_item_id
--AND si.organization_id = cst.organization_id (+)
--AND si.inventory_item_id = cst.inventory_item_id (+)
AND po.transaction_id = x_movement_transaction.rcv_transaction_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
muc.uom_code
FROM
MTL_UOM_CONVERSIONS_VIEW muc
WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
AND muc.organization_id = x_movement_transaction.organization_id
AND muc.unit_of_measure = l_receipt_transaction.primary_unit_of_measure;*/
SELECT
movement_id
, movement_status
, document_source_type
INTO
x_movement_id
, x_movement_status
, x_source_type
FROM
mtl_movement_statistics
WHERE usage_type = p_movement_transaction.usage_type
AND stat_type = p_movement_transaction.stat_type
AND zone_code = p_movement_transaction.zone_code
AND entity_org_id = p_movement_transaction.entity_org_id
AND rcv_transaction_id = p_rcv_transaction_id;
SELECT
rcv.transaction_id
, rcv.transaction_date
, rcv.organization_id
, rcv.subinventory
, rcv.requisition_line_id
, prha.segment1
FROM
rcv_transactions rcv
, po_requisition_lines_all prla
, po_requisition_headers_all prha
, oe_order_headers_all orha
, hr_organization_information hoi
WHERE rcv.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND prha.requisition_header_id = orha.source_document_id
AND orha.order_source_id = 10 --oe_order_sources tbl
AND orha.orig_sys_document_ref = prha.segment1
AND rcv.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND rcv.mvt_stat_status = 'NEW'
AND rcv.transaction_type = 'RECEIVE'
AND NVL(rcv.source_document_code,'REQ') = 'REQ'
AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
ORDER BY rcv.transaction_id;
SELECT
rcv.transaction_id
, rcv.organization_id
, rcv.movement_id
, rcv.mvt_stat_status
, rcv.currency_code
, rcv.currency_conversion_type
, rcv.currency_conversion_rate
, rcv.currency_conversion_date
, rcv.shipment_header_id
, rcv.shipment_line_id
, rsl.item_id
, rsl.item_description
, rcv.unit_of_measure
, rcv.quantity
, rcv.primary_quantity
--, nvl(cst.item_cost,0)
, NVL(rcv.po_unit_price,0)
, rcv.country_of_origin_code
, NVL(rsh.freight_carrier_code,'3')
FROM
RCV_TRANSACTIONS rcv
, RCV_SHIPMENT_HEADERS rsh
, RCV_SHIPMENT_LINES rsl
--, CST_ITEM_COSTS_FOR_GL_VIEW cst
WHERE rcv.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rcv.shipment_line_id = rsl.shipment_line_id
--AND rsl.to_organization_id = cst.organization_id (+)
--AND rsl.item_id = cst.inventory_item_id (+)
AND rcv.transaction_id = x_movement_transaction.rcv_transaction_id;
SELECT
oola.ship_to_org_id ship_to_site_use_id
, wdd.fob_code delivery_terms
, NVL(wdd.ship_method_code,'3') transport_mode
--, to_number(NULL) picking_line_id
, oola.line_id
, ooha.header_id
, ooha.order_number
, oola.line_number
, ooha.sold_to_org_id ship_to_customer_id
, nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id) bill_to_site_use_id
, ooha.sold_to_org_id bill_to_customer_id
, NVL(oola.unit_selling_price,0) doc_unit_price
, oola.source_document_id req_hd_id
, oola.source_document_line_id req_ln_id
--, to_number(NULL) pick_slip_ref
, rac.party_name cust_name
, rac.party_number cust_number
, substrb(rac.province,1,30) area
, wnd.name shipment_reference
, oola.item_type_code
FROM
WSH_NEW_DELIVERIES_OB_GRP_V wnd
, wsh_delivery_assignments_v wda
, WSH_DELIVERY_DETAILS_OB_GRP_V wdd
, OE_ORDER_HEADERS_ALL ooha
, OE_ORDER_LINES_ALL oola
, HZ_PARTIES rac
, HZ_CUST_ACCOUNTS hzc
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = oola.line_id
AND ooha.header_id = oola.header_id
AND oola.line_id = wdd.source_line_id
AND oola.header_id = wdd.source_header_id
AND rac.party_id = hzc.party_id
AND ooha.sold_to_org_id = hzc.cust_account_id
AND wdd.delivery_detail_id = x_movement_transaction.picking_line_detail_id;
SELECT
muc.uom_code
FROM
MTL_UOM_CONVERSIONS_VIEW muc
WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
AND muc.organization_id = x_movement_transaction.organization_id
AND muc.unit_of_measure = l_unit_of_measure;
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;