Topic: SQL*Plus and PL/SQL >> Performance Tuning
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Performance Tuning |
| Srinivas |
Posted: Apr 06, 2006 08:56:33 AM |
Total Post: 7
Joined: Apr, 2006
|
Well I have this query which runs for ever i need to optmize it can any one take a trip on this
select distinct ooh.order_number order_number,
ooh.order_number order_number_grouped,
ool.line_number,
ool.line_id,
msi.segment1 item,
msi.description description,
trunc(ool.request_date) request_date,
nvl(ool.ordered_quantity,0) ordered_quantity,
nvl(ool.shipped_quantity,0) quantity_shipped,
1 Order_Line_Count_Count,
decode(ool.shipped_quantity,null,'N',0,'N','Y') Ship_Confirm_Complete,
trunc(ool.actual_shipment_date) actual_shipment_date,
wdd.released_status,
wnd.creation_date Pick_Release_Date,
rc.customer_number cmf
from wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
mtl_system_items msi,
oe_order_lines_all ool,
oe_order_headers_all ooh,
ra_customers rc
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id (+)
and ool.header_id = ooh.header_id
AND ooh.sold_to_org_id = rc.customer_id
and ool.org_id=msi.organization_id
AND wdd.source_header_id = ooh.header_id
AND msi.organization_id = 405
AND ool.inventory_item_id= msi.inventory_item_id
AND ool.line_category_code = 'ORDER'
AND nvl(msi.segment1,'null') between nvl(null, nvl(msi.segment1,'null')) and nvl(null,nvl(msi.segment1,'null'))
AND nvl(ooh.order_number,0) between nvl(null, nvl(ooh.order_number,0)) and nvl(null,nvl(ooh.order_number,0))
AND trunc(ool.request_date) between nvl(null, trunc(ool.request_date)) and nvl(null, trunc(ool.request_date))
AND nvl(ool.ordered_quantity,0) > 0
and wdd.source_line_id = ool.line_id
and decode(nvl(wdd.subinventory,'NULL'),
'STAGING',adpds_107.adpds_cogs_inv(wdd.organization_id,msi.inventory_item_id),
nvl(wdd.subinventory,'NULL'))= nvl(:P_Subinventory,nvl(wdd.subinventory,'NULL'))
Execution Plan
---------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 17360
SORT UNIQUE 1 200 17360
NESTED LOOPS 1 200 17313
NESTED LOOPS 1 195 17312
NESTED LOOPS OUTER 1 176 17311
NESTED LOOPS 1 162 17309
NESTED LOOPS 1 150 17306
NESTED LOOPS 1 116 17303
NESTED LOOPS 1 99 17301
TABLE ACCESS BY INDEX ROWID INV.MTL_SYSTEM_ITEMS_B 86 4 K 187
INDEX RANGE SCAN INV.MTL_SYSTEM_ITEMS_B_N1 86 130
TABLE ACCESS BY INDEX ROWID ONT.OE_ORDER_LINES_ALL 1 51 199
INDEX RANGE SCAN ONT.OE_ORDER_LINES_N3 262 2
TABLE ACCESS BY INDEX ROWID ONT.OE_ORDER_HEADERS_ALL 1 17 2
INDEX UNIQUE SCAN ONT.OE_ORDER_HEADERS_U1 1 1
TABLE ACCESS BY INDEX ROWID WSH.WSH_DELIVERY_DETAILS 1 34 3
INDEX RANGE SCAN WSH.WSH_DELIVERY_DETAILS_N3 1 2
TABLE ACCESS BY INDEX ROWID WSH.WSH_DELIVERY_ASSIGNMENTS 1 12 3
INDEX RANGE SCAN WSH.WSH_DELIVERY_ASSIGNMENTS_N3 1 2
TABLE ACCESS BY INDEX ROWID WSH.WSH_NEW_DELIVERIES 1 14 2
INDEX UNIQUE SCAN WSH.WSH_NEW_DELIVERIES_U1 1 1
TABLE ACCESS BY INDEX ROWID AR.HZ_CUST_ACCOUNTS 1 19 1
INDEX UNIQUE SCAN AR.HZ_CUST_ACCOUNTS_U1 1
INDEX UNIQUE SCAN AR.HZ_PARTIES_U1 1 5 1
Query is running for ever optmization required
Chill:)
|
|
|
 Subhranil |
| Posted: Apr 07, 2006 12:56:23 AM |  |
|
Total Post: 28
Joined: Jul, 2005
|
can you post the table structures!
Subhranil
|
|
|
|
|
| Srinivas |
| Posted: Apr 10, 2006 04:07:23 AM | |
|
Total Post: 7
Joined: Apr, 2006
|
SQL> desc wsh_delivery_assignments
Name Null? Type
----------------------------------------- -------- ----------------------------
DELIVERY_ASSIGNMENT_ID NOT NULL NUMBER
DELIVERY_ID NUMBER
PARENT_DELIVERY_ID NUMBER
DELIVERY_DETAIL_ID NOT NULL NUMBER
PARENT_DELIVERY_DETAIL_ID NUMBER
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
ACTIVE_FLAG VARCHAR2(1)
SQL> desc wsh_delivery_details
Name Null? Type
----------------------------------------- -------- ----------------------------
DELIVERY_DETAIL_ID NOT NULL NUMBER
SOURCE_CODE NOT NULL VARCHAR2(30)
SOURCE_HEADER_ID NUMBER
SOURCE_LINE_ID NOT NULL NUMBER
SOURCE_HEADER_NUMBER VARCHAR2(150)
SOURCE_HEADER_TYPE_ID NUMBER
SOURCE_HEADER_TYPE_NAME VARCHAR2(240)
CUST_PO_NUMBER VARCHAR2(50)
CUSTOMER_ID NUMBER
SOLD_TO_CONTACT_ID NUMBER
INVENTORY_ITEM_ID NUMBER
ITEM_DESCRIPTION VARCHAR2(250)
SHIP_SET_ID NUMBER
ARRIVAL_SET_ID NUMBER
TOP_MODEL_LINE_ID NUMBER
ATO_LINE_ID NUMBER
HOLD_CODE VARCHAR2(1)
SHIP_MODEL_COMPLETE_FLAG VARCHAR2(1)
HAZARD_CLASS_ID NUMBER
COUNTRY_OF_ORIGIN VARCHAR2(50)
CLASSIFICATION VARCHAR2(30)
SHIP_FROM_LOCATION_ID NOT NULL NUMBER
ORGANIZATION_ID NUMBER
SHIP_TO_LOCATION_ID NUMBER
SHIP_TO_CONTACT_ID NUMBER
DELIVER_TO_LOCATION_ID NUMBER
DELIVER_TO_CONTACT_ID NUMBER
INTMED_SHIP_TO_LOCATION_ID NUMBER
INTMED_SHIP_TO_CONTACT_ID NUMBER
SHIP_TOLERANCE_ABOVE NUMBER
SHIP_TOLERANCE_BELOW NUMBER
SRC_REQUESTED_QUANTITY NUMBER
SRC_REQUESTED_QUANTITY_UOM VARCHAR2(3)
CANCELLED_QUANTITY NUMBER
REQUESTED_QUANTITY NOT NULL NUMBER
REQUESTED_QUANTITY_UOM NOT NULL VARCHAR2(3)
SHIPPED_QUANTITY NUMBER
DELIVERED_QUANTITY NUMBER
QUALITY_CONTROL_QUANTITY NUMBER
CYCLE_COUNT_QUANTITY NUMBER
MOVE_ORDER_LINE_ID NUMBER
SUBINVENTORY VARCHAR2(10)
REVISION VARCHAR2(3)
LOT_NUMBER VARCHAR2(32)
RELEASED_STATUS VARCHAR2(1)
CUSTOMER_REQUESTED_LOT_FLAG VARCHAR2(1)
SERIAL_NUMBER VARCHAR2(30)
LOCATOR_ID NUMBER
DATE_REQUESTED DATE
DATE_SCHEDULED DATE
MASTER_CONTAINER_ITEM_ID NUMBER
DETAIL_CONTAINER_ITEM_ID NUMBER
LOAD_SEQ_NUMBER NUMBER
SHIP_METHOD_CODE VARCHAR2(30)
CARRIER_ID NUMBER
FREIGHT_TERMS_CODE VARCHAR2(30)
SHIPMENT_PRIORITY_CODE VARCHAR2(30)
FOB_CODE VARCHAR2(30)
CUSTOMER_ITEM_ID NUMBER
DEP_PLAN_REQUIRED_FLAG VARCHAR2(1)
CUSTOMER_PROD_SEQ VARCHAR2(50)
CUSTOMER_DOCK_CODE VARCHAR2(50)
NET_WEIGHT NUMBER
WEIGHT_UOM_CODE VARCHAR2(3)
VOLUME NUMBER
VOLUME_UOM_CODE VARCHAR2(3)
SHIPPING_INSTRUCTIONS VARCHAR2(2000)
PACKING_INSTRUCTIONS VARCHAR2(2000)
PROJECT_ID NUMBER
TASK_ID NUMBER
ORG_ID NUMBER
OE_INTERFACED_FLAG VARCHAR2(1)
MVT_STAT_STATUS VARCHAR2(30)
TRACKING_NUMBER VARCHAR2(30)
TRANSACTION_TEMP_ID NUMBER
TP_ATTRIBUTE_CATEGORY VARCHAR2(240)
TP_ATTRIBUTE1 VARCHAR2(240)
TP_ATTRIBUTE2 VARCHAR2(240)
TP_ATTRIBUTE3 VARCHAR2(240)
TP_ATTRIBUTE4 VARCHAR2(240)
TP_ATTRIBUTE5 VARCHAR2(240)
TP_ATTRIBUTE6 VARCHAR2(240)
TP_ATTRIBUTE7 VARCHAR2(240)
TP_ATTRIBUTE8 VARCHAR2(240)
TP_ATTRIBUTE9 VARCHAR2(240)
TP_ATTRIBUTE10 VARCHAR2(240)
TP_ATTRIBUTE11 VARCHAR2(240)
TP_ATTRIBUTE12 VARCHAR2(240)
TP_ATTRIBUTE13 VARCHAR2(240)
TP_ATTRIBUTE14 VARCHAR2(240)
TP_ATTRIBUTE15 VARCHAR2(240)
ATTRIBUTE_CATEGORY VARCHAR2(150)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
MOVEMENT_ID NUMBER
SPLIT_FROM_DELIVERY_DETAIL_ID NUMBER
INV_INTERFACED_FLAG VARCHAR2(1)
SOURCE_LINE_NUMBER VARCHAR2(150)
SEAL_CODE VARCHAR2(30)
MINIMUM_FILL_PERCENT NUMBER
MAXIMUM_VOLUME NUMBER
MAXIMUM_LOAD_WEIGHT NUMBER
MASTER_SERIAL_NUMBER VARCHAR2(30)
GROSS_WEIGHT NUMBER
FILL_PERCENT NUMBER
CONTAINER_NAME VARCHAR2(30)
CONTAINER_TYPE_CODE VARCHAR2(30)
CONTAINER_FLAG NOT NULL VARCHAR2(1)
SHIP_TO_SITE_USE_ID NUMBER
DELIVER_TO_SITE_USE_ID NUMBER
CUSTOMER_PRODUCTION_LINE VARCHAR2(50)
CUSTOMER_JOB VARCHAR2(50)
CUST_MODEL_SERIAL_NUMBER VARCHAR2(50)
PREFERRED_GRADE VARCHAR2(4)
SRC_REQUESTED_QUANTITY2 NUMBER
SRC_REQUESTED_QUANTITY_UOM2 VARCHAR2(3)
REQUESTED_QUANTITY2 NUMBER
SHIPPED_QUANTITY2 NUMBER
DELIVERED_QUANTITY2 NUMBER
CANCELLED_QUANTITY2 NUMBER
QUALITY_CONTROL_QUANTITY2 NUMBER
CYCLE_COUNT_QUANTITY2 NUMBER
REQUESTED_QUANTITY_UOM2 VARCHAR2(3)
SUBLOT_NUMBER VARCHAR2(32)
UNIT_PRICE NUMBER
CURRENCY_CODE VARCHAR2(15)
UNIT_NUMBER VARCHAR2(30)
FREIGHT_CLASS_CAT_ID NUMBER
COMMODITY_CODE_CAT_ID NUMBER
LPN_ID NUMBER
INSPECTION_FLAG VARCHAR2(1)
ORIGINAL_SUBINVENTORY VARCHAR2(10)
PICKABLE_FLAG VARCHAR2(1)
TO_SERIAL_NUMBER VARCHAR2(30)
PICKED_QUANTITY NUMBER
PICKED_QUANTITY2 NUMBER
RECEIVED_QUANTITY NUMBER
RECEIVED_QUANTITY2 NUMBER
SOURCE_LINE_SET_ID NUMBER
BATCH_ID NUMBER
TRANSACTION_ID NUMBER
SQL> desc wsh_new_deliveries
Name Null? Type
----------------------------------------- -------- ----------------------------
DELIVERY_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
PLANNED_FLAG NOT NULL VARCHAR2(1)
STATUS_CODE NOT NULL VARCHAR2(2)
INITIAL_PICKUP_DATE DATE
INITIAL_PICKUP_LOCATION_ID NOT NULL NUMBER
ULTIMATE_DROPOFF_LOCATION_ID NOT NULL NUMBER
ULTIMATE_DROPOFF_DATE DATE
CUSTOMER_ID NUMBER
INTMED_SHIP_TO_LOCATION_ID NUMBER
POOLED_SHIP_TO_LOCATION_ID NUMBER
FREIGHT_TERMS_CODE VARCHAR2(30)
FOB_CODE VARCHAR2(30)
FOB_LOCATION_ID NUMBER
WAYBILL VARCHAR2(30)
ACCEPTANCE_FLAG VARCHAR2(1)
ACCEPTED_BY VARCHAR2(150)
ACCEPTED_DATE DATE
ACKNOWLEDGED_BY VARCHAR2(150)
CONFIRMED_BY VARCHAR2(150)
ASN_DATE_SENT DATE
ASN_STATUS_CODE VARCHAR2(15)
ASN_SEQ_NUMBER NUMBER
REASON_OF_TRANSPORT VARCHAR2(30)
DESCRIPTION VARCHAR2(30)
GROSS_WEIGHT NUMBER
NET_WEIGHT NUMBER
WEIGHT_UOM_CODE VARCHAR2(3)
VOLUME NUMBER
VOLUME_UOM_CODE VARCHAR2(3)
ADDITIONAL_SHIPMENT_INFO VARCHAR2(500)
PORT_OF_DISCHARGE VARCHAR2(150)
BOOKING_NUMBER VARCHAR2(30)
COD_AMOUNT NUMBER
COD_CURRENCY_CODE VARCHAR2(15)
SERVICE_CONTRACT VARCHAR2(30)
COD_REMIT_TO VARCHAR2(150)
COD_CHARGE_PAID_BY VARCHAR2(150)
PROBLEM_CONTACT_REFERENCE VARCHAR2(500)
BILL_FREIGHT_TO VARCHAR2(1000)
CARRIED_BY VARCHAR2(150)
PORT_OF_LOADING VARCHAR2(150)
ATTRIBUTE_CATEGORY VARCHAR2(150)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
TP_ATTRIBUTE_CATEGORY VARCHAR2(150)
TP_ATTRIBUTE1 VARCHAR2(150)
TP_ATTRIBUTE2 VARCHAR2(150)
TP_ATTRIBUTE3 VARCHAR2(150)
TP_ATTRIBUTE4 VARCHAR2(150)
TP_ATTRIBUTE5 VARCHAR2(150)
TP_ATTRIBUTE6 VARCHAR2(150)
TP_ATTRIBUTE7 VARCHAR2(150)
TP_ATTRIBUTE8 VARCHAR2(150)
TP_ATTRIBUTE9 VARCHAR2(150)
TP_ATTRIBUTE10 VARCHAR2(150)
TP_ATTRIBUTE11 VARCHAR2(150)
TP_ATTRIBUTE12 VARCHAR2(150)
TP_ATTRIBUTE13 VARCHAR2(150)
TP_ATTRIBUTE14 VARCHAR2(150)
TP_ATTRIBUTE15 VARCHAR2(150)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30)
GLOBAL_ATTRIBUTE1 VARCHAR2(150)
GLOBAL_ATTRIBUTE2 VARCHAR2(150)
GLOBAL_ATTRIBUTE3 VARCHAR2(150)
GLOBAL_ATTRIBUTE4 VARCHAR2(150)
GLOBAL_ATTRIBUTE5 VARCHAR2(150)
GLOBAL_ATTRIBUTE6 VARCHAR2(150)
GLOBAL_ATTRIBUTE7 VARCHAR2(150)
GLOBAL_ATTRIBUTE8 VARCHAR2(150)
GLOBAL_ATTRIBUTE9 VARCHAR2(150)
GLOBAL_ATTRIBUTE10 VARCHAR2(150)
GLOBAL_ATTRIBUTE11 VARCHAR2(150)
GLOBAL_ATTRIBUTE12 VARCHAR2(150)
GLOBAL_ATTRIBUTE13 VARCHAR2(150)
GLOBAL_ATTRIBUTE14 VARCHAR2(150)
GLOBAL_ATTRIBUTE15 VARCHAR2(150)
GLOBAL_ATTRIBUTE16 VARCHAR2(150)
GLOBAL_ATTRIBUTE17 VARCHAR2(150)
GLOBAL_ATTRIBUTE18 VARCHAR2(150)
GLOBAL_ATTRIBUTE19 VARCHAR2(150)
GLOBAL_ATTRIBUTE20 VARCHAR2(150)
CONFIRM_DATE DATE
SHIP_METHOD_CODE VARCHAR2(30)
DOCK_CODE VARCHAR2(30)
DELIVERY_TYPE NOT NULL VARCHAR2(30)
CARRIER_ID NUMBER
CURRENCY_CODE VARCHAR2(15)
ORGANIZATION_ID NUMBER
LOADING_SEQUENCE NUMBER
LOADING_ORDER_FLAG VARCHAR2(2)
NUMBER_OF_LPN NUMBER
BATCH_ID NUMBER
SOURCE_HEADER_ID NUMBER
HASH_VALUE NUMBER
FTZ_NUMBER VARCHAR2(35)
ROUTED_EXPORT_TXN VARCHAR2(1)
ENTRY_NUMBER VARCHAR2(35)
ROUTING_INSTRUCTIONS VARCHAR2(120)
IN_BOND_CODE VARCHAR2(35)
SHIPPING_MARKS VARCHAR2(100)
SERVICE_LEVEL VARCHAR2(30)
MODE_OF_TRANSPORT VARCHAR2(30)
ASSIGNED_TO_FTE_TRIPS VARCHAR2(1)
AUTO_SC_EXCLUDE_FLAG VARCHAR2(1)
AUTO_AP_EXCLUDE_FLAG VARCHAR2(1)
AP_BATCH_ID NUMBER
SQL> desc mtl_system_items
Name Null? Type
----------------------------------------- -------- ----------------------------
INVENTORY_ITEM_ID NOT NULL NUMBER
ORGANIZATION_ID NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
SUMMARY_FLAG NOT NULL VARCHAR2(1)
ENABLED_FLAG NOT NULL VARCHAR2(1)
START_DATE_ACTIVE DATE
END_DATE_ACTIVE DATE
DESCRIPTION VARCHAR2(240)
BUYER_ID NUMBER(9)
ACCOUNTING_RULE_ID NUMBER
INVOICING_RULE_ID NUMBER
SEGMENT1 VARCHAR2(40)
SEGMENT2 VARCHAR2(40)
SEGMENT3 VARCHAR2(40)
SEGMENT4 VARCHAR2(40)
SEGMENT5 VARCHAR2(40)
SEGMENT6 VARCHAR2(40)
SEGMENT7 VARCHAR2(40)
SEGMENT8 VARCHAR2(40)
SEGMENT9 VARCHAR2(40)
SEGMENT10 VARCHAR2(40)
SEGMENT11 VARCHAR2(40)
SEGMENT12 VARCHAR2(40)
SEGMENT13 VARCHAR2(40)
SEGMENT14 VARCHAR2(40)
SEGMENT15 VARCHAR2(40)
SEGMENT16 VARCHAR2(40)
SEGMENT17 VARCHAR2(40)
SEGMENT18 VARCHAR2(40)
SEGMENT19 VARCHAR2(40)
SEGMENT20 VARCHAR2(40)
ATTRIBUTE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
PURCHASING_ITEM_FLAG NOT NULL VARCHAR2(1)
SHIPPABLE_ITEM_FLAG NOT NULL VARCHAR2(1)
CUSTOMER_ORDER_FLAG NOT NULL VARCHAR2(1)
INTERNAL_ORDER_FLAG NOT NULL VARCHAR2(1)
SERVICE_ITEM_FLAG NOT NULL VARCHAR2(1)
INVENTORY_ITEM_FLAG NOT NULL VARCHAR2(1)
ENG_ITEM_FLAG NOT NULL VARCHAR2(1)
INVENTORY_ASSET_FLAG NOT NULL VARCHAR2(1)
PURCHASING_ENABLED_FLAG NOT NULL VARCHAR2(1)
CUSTOMER_ORDER_ENABLED_FLAG NOT NULL VARCHAR2(1)
INTERNAL_ORDER_ENABLED_FLAG NOT NULL VARCHAR2(1)
SO_TRANSACTIONS_FLAG NOT NULL VARCHAR2(1)
MTL_TRANSACTIONS_ENABLED_FLAG NOT NULL VARCHAR2(1)
STOCK_ENABLED_FLAG NOT NULL VARCHAR2(1)
BOM_ENABLED_FLAG NOT NULL VARCHAR2(1)
BUILD_IN_WIP_FLAG NOT NULL VARCHAR2(1)
REVISION_QTY_CONTROL_CODE NUMBER
ITEM_CATALOG_GROUP_ID NUMBER
CATALOG_STATUS_FLAG VARCHAR2(1)
RETURNABLE_FLAG VARCHAR2(1)
DEFAULT_SHIPPING_ORG NUMBER
COLLATERAL_FLAG VARCHAR2(1)
TAXABLE_FLAG VARCHAR2(1)
QTY_RCV_EXCEPTION_CODE VARCHAR2(25)
ALLOW_ITEM_DESC_UPDATE_FLAG VARCHAR2(1)
INSPECTION_REQUIRED_FLAG VARCHAR2(1)
RECEIPT_REQUIRED_FLAG VARCHAR2(1)
MARKET_PRICE NUMBER
HAZARD_CLASS_ID NUMBER
RFQ_REQUIRED_FLAG VARCHAR2(1)
QTY_RCV_TOLERANCE NUMBER
LIST_PRICE_PER_UNIT NUMBER
UN_NUMBER_ID NUMBER
PRICE_TOLERANCE_PERCENT NUMBER
ASSET_CATEGORY_ID NUMBER
ROUNDING_FACTOR NUMBER
UNIT_OF_ISSUE VARCHAR2(25)
ENFORCE_SHIP_TO_LOCATION_CODE VARCHAR2(25)
ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1)
ALLOW_UNORDERED_RECEIPTS_FLAG VARCHAR2(1)
ALLOW_EXPRESS_DELIVERY_FLAG VARCHAR2(1)
DAYS_EARLY_RECEIPT_ALLOWED NUMBER
DAYS_LATE_RECEIPT_ALLOWED NUMBER
RECEIPT_DAYS_EXCEPTION_CODE VARCHAR2(25)
RECEIVING_ROUTING_ID NUMBER
INVOICE_CLOSE_TOLERANCE NUMBER
RECEIVE_CLOSE_TOLERANCE NUMBER
AUTO_LOT_ALPHA_PREFIX VARCHAR2(30)
START_AUTO_LOT_NUMBER VARCHAR2(30)
LOT_CONTROL_CODE NUMBER
SHELF_LIFE_CODE NUMBER
SHELF_LIFE_DAYS NUMBER
SERIAL_NUMBER_CONTROL_CODE NUMBER
START_AUTO_SERIAL_NUMBER VARCHAR2(30)
AUTO_SERIAL_ALPHA_PREFIX VARCHAR2(30)
SOURCE_TYPE NUMBER
SOURCE_ORGANIZATION_ID NUMBER
SOURCE_SUBINVENTORY VARCHAR2(10)
EXPENSE_ACCOUNT NUMBER
ENCUMBRANCE_ACCOUNT NUMBER
RESTRICT_SUBINVENTORIES_CODE NUMBER
UNIT_WEIGHT NUMBER
WEIGHT_UOM_CODE VARCHAR2(3)
VOLUME_UOM_CODE VARCHAR2(3)
UNIT_VOLUME NUMBER
RESTRICT_LOCATORS_CODE NUMBER
LOCATION_CONTROL_CODE NUMBER
SHRINKAGE_RATE NUMBER
ACCEPTABLE_EARLY_DAYS NUMBER
PLANNING_TIME_FENCE_CODE NUMBER
DEMAND_TIME_FENCE_CODE NUMBER
LEAD_TIME_LOT_SIZE NUMBER
STD_LOT_SIZE NUMBER
CUM_MANUFACTURING_LEAD_TIME NUMBER
OVERRUN_PERCENTAGE NUMBER
MRP_CALCULATE_ATP_FLAG VARCHAR2(1)
ACCEPTABLE_RATE_INCREASE NUMBER
ACCEPTABLE_RATE_DECREASE NUMBER
CUMULATIVE_TOTAL_LEAD_TIME NUMBER
PLANNING_TIME_FENCE_DAYS NUMBER
DEMAND_TIME_FENCE_DAYS NUMBER
END_ASSEMBLY_PEGGING_FLAG VARCHAR2(1)
REPETITIVE_PLANNING_FLAG VARCHAR2(1)
PLANNING_EXCEPTION_SET VARCHAR2(10)
BOM_ITEM_TYPE NOT NULL NUMBER
PICK_COMPONENTS_FLAG NOT NULL VARCHAR2(1)
REPLENISH_TO_ORDER_FLAG NOT NULL VARCHAR2(1)
BASE_ITEM_ID NUMBER
ATP_COMPONENTS_FLAG NOT NULL VARCHAR2(1)
ATP_FLAG NOT NULL VARCHAR2(1)
FIXED_LEAD_TIME NUMBER
VARIABLE_LEAD_TIME NUMBER
WIP_SUPPLY_LOCATOR_ID NUMBER
WIP_SUPPLY_TYPE NUMBER
WIP_SUPPLY_SUBINVENTORY VARCHAR2(10)
PRIMARY_UOM_CODE VARCHAR2(3)
PRIMARY_UNIT_OF_MEASURE VARCHAR2(25)
ALLOWED_UNITS_LOOKUP_CODE NUMBER
COST_OF_SALES_ACCOUNT NUMBER
SALES_ACCOUNT NUMBER
DEFAULT_INCLUDE_IN_ROLLUP_FLAG VARCHAR2(1)
INVENTORY_ITEM_STATUS_CODE NOT NULL VARCHAR2(10)
INVENTORY_PLANNING_CODE NUMBER
PLANNER_CODE VARCHAR2(10)
PLANNING_MAKE_BUY_CODE NUMBER
FIXED_LOT_MULTIPLIER NUMBER
ROUNDING_CONTROL_TYPE NUMBER
CARRYING_COST NUMBER
POSTPROCESSING_LEAD_TIME NUMBER
PREPROCESSING_LEAD_TIME NUMBER
FULL_LEAD_TIME NUMBER
ORDER_COST NUMBER
MRP_SAFETY_STOCK_PERCENT NUMBER
MRP_SAFETY_STOCK_CODE NUMBER
MIN_MINMAX_QUANTITY NUMBER
MAX_MINMAX_QUANTITY NUMBER
MINIMUM_ORDER_QUANTITY NUMBER
FIXED_ORDER_QUANTITY NUMBER
FIXED_DAYS_SUPPLY NUMBER
MAXIMUM_ORDER_QUANTITY NUMBER
ATP_RULE_ID NUMBER
PICKING_RULE_ID NUMBER
RESERVABLE_TYPE NUMBER
POSITIVE_MEASUREMENT_ERROR NUMBER
NEGATIVE_MEASUREMENT_ERROR NUMBER
ENGINEERING_ECN_CODE VARCHAR2(50)
ENGINEERING_ITEM_ID NUMBER
ENGINEERING_DATE DATE
SERVICE_STARTING_DELAY NUMBER
VENDOR_WARRANTY_FLAG NOT NULL VARCHAR2(1)
SERVICEABLE_COMPONENT_FLAG VARCHAR2(1)
SERVICEABLE_PRODUCT_FLAG NOT NULL VARCHAR2(1)
BASE_WARRANTY_SERVICE_ID NUMBER
PAYMENT_TERMS_ID NUMBER
PREVENTIVE_MAINTENANCE_FLAG VARCHAR2(1)
PRIMARY_SPECIALIST_ID NUMBER
SECONDARY_SPECIALIST_ID NUMBER
SERVICEABLE_ITEM_CLASS_ID NUMBER
TIME_BILLABLE_FLAG VARCHAR2(1)
MATERIAL_BILLABLE_FLAG VARCHAR2(30)
EXPENSE_BILLABLE_FLAG VARCHAR2(1)
PRORATE_SERVICE_FLAG VARCHAR2(1)
COVERAGE_SCHEDULE_ID NUMBER
SERVICE_DURATION_PERIOD_CODE VARCHAR2(10)
SERVICE_DURATION NUMBER
WARRANTY_VENDOR_ID NUMBER
MAX_WARRANTY_AMOUNT NUMBER
RESPONSE_TIME_PERIOD_CODE VARCHAR2(30)
RESPONSE_TIME_VALUE NUMBER
NEW_REVISION_CODE VARCHAR2(30)
INVOICEABLE_ITEM_FLAG NOT NULL VARCHAR2(1)
TAX_CODE VARCHAR2(50)
INVOICE_ENABLED_FLAG NOT NULL VARCHAR2(1)
MUST_USE_APPROVED_VENDOR_FLAG NOT NULL VARCHAR2(1)
REQUEST_ID NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
OUTSIDE_OPERATION_FLAG NOT NULL VARCHAR2(1)
OUTSIDE_OPERATION_UOM_TYPE VARCHAR2(25)
SAFETY_STOCK_BUCKET_DAYS NUMBER
AUTO_REDUCE_MPS NUMBER(22)
COSTING_ENABLED_FLAG NOT NULL VARCHAR2(1)
AUTO_CREATED_CONFIG_FLAG NOT NULL VARCHAR2(1)
CYCLE_COUNT_ENABLED_FLAG NOT NULL VARCHAR2(1)
ITEM_TYPE VARCHAR2(30)
MODEL_CONFIG_CLAUSE_NAME VARCHAR2(10)
SHIP_MODEL_COMPLETE_FLAG VARCHAR2(1)
MRP_PLANNING_CODE NUMBER
RETURN_INSPECTION_REQUIREMENT NUMBER
ATO_FORECAST_CONTROL NUMBER
RELEASE_TIME_FENCE_CODE NUMBER
RELEASE_TIME_FENCE_DAYS NUMBER
CONTAINER_ITEM_FLAG VARCHAR2(1)
VEHICLE_ITEM_FLAG VARCHAR2(1)
MAXIMUM_LOAD_WEIGHT NUMBER
MINIMUM_FILL_PERCENT NUMBER
CONTAINER_TYPE_CODE VARCHAR2(30)
INTERNAL_VOLUME NUMBER
WH_UPDATE_DATE DATE
PRODUCT_FAMILY_ITEM_ID NUMBER
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(150)
GLOBAL_ATTRIBUTE1 VARCHAR2(150)
GLOBAL_ATTRIBUTE2 VARCHAR2(150)
GLOBAL_ATTRIBUTE3 VARCHAR2(150)
GLOBAL_ATTRIBUTE4 VARCHAR2(150)
GLOBAL_ATTRIBUTE5 VARCHAR2(150)
GLOBAL_ATTRIBUTE6 VARCHAR2(150)
GLOBAL_ATTRIBUTE7 VARCHAR2(150)
GLOBAL_ATTRIBUTE8 VARCHAR2(150)
GLOBAL_ATTRIBUTE9 VARCHAR2(150)
GLOBAL_ATTRIBUTE10 VARCHAR2(150)
PURCHASING_TAX_CODE VARCHAR2(50)
OVERCOMPLETION_TOLERANCE_TYPE NUMBER
OVERCOMPLETION_TOLERANCE_VALUE NUMBER
EFFECTIVITY_CONTROL NUMBER
CHECK_SHORTAGES_FLAG VARCHAR2(1)
OVER_SHIPMENT_TOLERANCE NUMBER
UNDER_SHIPMENT_TOLERANCE NUMBER
OVER_RETURN_TOLERANCE NUMBER
UNDER_RETURN_TOLERANCE NUMBER
EQUIPMENT_TYPE NUMBER
RECOVERED_PART_DISP_CODE VARCHAR2(30)
DEFECT_TRACKING_ON_FLAG VARCHAR2(1)
USAGE_ITEM_FLAG VARCHAR2(1)
EVENT_FLAG VARCHAR2(1)
ELECTRONIC_FLAG VARCHAR2(1)
DOWNLOADABLE_FLAG VARCHAR2(1)
VOL_DISCOUNT_EXEMPT_FLAG VARCHAR2(1)
COUPON_EXEMPT_FLAG VARCHAR2(1)
COMMS_NL_TRACKABLE_FLAG VARCHAR2(1)
ASSET_CREATION_CODE VARCHAR2(30)
COMMS_ACTIVATION_REQD_FLAG VARCHAR2(1)
ORDERABLE_ON_WEB_FLAG VARCHAR2(1)
BACK_ORDERABLE_FLAG VARCHAR2(1)
WEB_STATUS VARCHAR2(30)
INDIVISIBLE_FLAG VARCHAR2(1)
DIMENSION_UOM_CODE VARCHAR2(3)
UNIT_LENGTH NUMBER
UNIT_WIDTH NUMBER
UNIT_HEIGHT NUMBER
BULK_PICKED_FLAG VARCHAR2(1)
LOT_STATUS_ENABLED VARCHAR2(1)
DEFAULT_LOT_STATUS_ID NUMBER
SERIAL_STATUS_ENABLED VARCHAR2(1)
DEFAULT_SERIAL_STATUS_ID NUMBER
LOT_SPLIT_ENABLED VARCHAR2(1)
LOT_MERGE_ENABLED VARCHAR2(1)
INVENTORY_CARRY_PENALTY NUMBER
OPERATION_SLACK_PENALTY NUMBER
FINANCING_ALLOWED_FLAG VARCHAR2(1)
EAM_ITEM_TYPE NUMBER
EAM_ACTIVITY_TYPE_CODE VARCHAR2(30)
EAM_ACTIVITY_CAUSE_CODE VARCHAR2(30)
EAM_ACT_NOTIFICATION_FLAG VARCHAR2(1)
EAM_ACT_SHUTDOWN_STATUS VARCHAR2(30)
DUAL_UOM_CONTROL NUMBER
SECONDARY_UOM_CODE VARCHAR2(3)
DUAL_UOM_DEVIATION_HIGH NUMBER
DUAL_UOM_DEVIATION_LOW NUMBER
CONTRACT_ITEM_TYPE_CODE VARCHAR2(30)
SUBSCRIPTION_DEPEND_FLAG VARCHAR2(1)
SERV_REQ_ENABLED_CODE VARCHAR2(30)
SERV_BILLING_ENABLED_FLAG VARCHAR2(1)
SERV_IMPORTANCE_LEVEL NUMBER
PLANNED_INV_POINT_FLAG VARCHAR2(1)
LOT_TRANSLATE_ENABLED VARCHAR2(1)
DEFAULT_SO_SOURCE_TYPE NOT NULL VARCHAR2(30)
CREATE_SUPPLY_FLAG NOT NULL VARCHAR2(1)
SUBSTITUTION_WINDOW_CODE NUMBER
SUBSTITUTION_WINDOW_DAYS NUMBER
IB_ITEM_INSTANCE_CLASS VARCHAR2(30)
CONFIG_MODEL_TYPE VARCHAR2(30)
LOT_SUBSTITUTION_ENABLED VARCHAR2(1)
MINIMUM_LICENSE_QUANTITY NUMBER
EAM_ACTIVITY_SOURCE_CODE VARCHAR2(30)
LIFECYCLE_ID NUMBER
CURRENT_PHASE_ID NUMBER
OBJECT_VERSION_NUMBER NUMBER(9)
SQL> desc oe_order_lines_all
Name Null? Type
----------------------------------------- -------- ----------------------------
LINE_ID NOT NULL NUMBER
ORG_ID NUMBER
HEADER_ID NOT NULL NUMBER
LINE_TYPE_ID NOT NULL NUMBER
LINE_NUMBER NOT NULL NUMBER
ORDERED_ITEM VARCHAR2(2000)
REQUEST_DATE DATE
PROMISE_DATE DATE
SCHEDULE_SHIP_DATE DATE
ORDER_QUANTITY_UOM VARCHAR2(3)
PRICING_QUANTITY NUMBER
PRICING_QUANTITY_UOM VARCHAR2(3)
CANCELLED_QUANTITY NUMBER
SHIPPED_QUANTITY NUMBER
ORDERED_QUANTITY NUMBER
FULFILLED_QUANTITY NUMBER
SHIPPING_QUANTITY NUMBER
SHIPPING_QUANTITY_UOM VARCHAR2(3)
DELIVERY_LEAD_TIME NUMBER
TAX_EXEMPT_FLAG VARCHAR2(1)
TAX_EXEMPT_NUMBER VARCHAR2(80)
TAX_EXEMPT_REASON_CODE VARCHAR2(30)
SHIP_FROM_ORG_ID NUMBER
SHIP_TO_ORG_ID NUMBER
INVOICE_TO_ORG_ID NUMBER
DELIVER_TO_ORG_ID NUMBER
SHIP_TO_CONTACT_ID NUMBER
DELIVER_TO_CONTACT_ID NUMBER
INVOICE_TO_CONTACT_ID NUMBER
INTMED_SHIP_TO_ORG_ID NUMBER
INTMED_SHIP_TO_CONTACT_ID NUMBER
SOLD_FROM_ORG_ID NUMBER
SOLD_TO_ORG_ID NUMBER
CUST_PO_NUMBER VARCHAR2(50)
SHIP_TOLERANCE_ABOVE NUMBER
SHIP_TOLERANCE_BELOW NUMBER
DEMAND_BUCKET_TYPE_CODE VARCHAR2(30)
VEH_CUS_ITEM_CUM_KEY_ID NUMBER
RLA_SCHEDULE_TYPE_CODE VARCHAR2(30)
CUSTOMER_DOCK_CODE VARCHAR2(50)
CUSTOMER_JOB VARCHAR2(50)
CUSTOMER_PRODUCTION_LINE VARCHAR2(50)
CUST_MODEL_SERIAL_NUMBER VARCHAR2(50)
PROJECT_ID NUMBER
TASK_ID NUMBER
INVENTORY_ITEM_ID NOT NULL NUMBER
TAX_DATE DATE
TAX_CODE VARCHAR2(50)
TAX_RATE NUMBER
INVOICE_INTERFACE_STATUS_CODE VARCHAR2(30)
DEMAND_CLASS_CODE VARCHAR2(30)
PRICE_LIST_ID NUMBER
PRICING_DATE DATE
SHIPMENT_NUMBER NOT NULL NUMBER
AGREEMENT_ID NUMBER
SHIPMENT_PRIORITY_CODE VARCHAR2(30)
SHIPPING_METHOD_CODE VARCHAR2(30)
FREIGHT_CARRIER_CODE VARCHAR2(30)
FREIGHT_TERMS_CODE VARCHAR2(30)
FOB_POINT_CODE VARCHAR2(30)
TAX_POINT_CODE VARCHAR2(30)
PAYMENT_TERM_ID NUMBER(15)
INVOICING_RULE_ID NUMBER(15)
ACCOUNTING_RULE_ID NUMBER(15)
SOURCE_DOCUMENT_TYPE_ID NUMBER
ORIG_SYS_DOCUMENT_REF VARCHAR2(50)
SOURCE_DOCUMENT_ID NUMBER
ORIG_SYS_LINE_REF VARCHAR2(50)
SOURCE_DOCUMENT_LINE_ID NUMBER
REFERENCE_LINE_ID NUMBER
REFERENCE_TYPE VARCHAR2(30)
REFERENCE_HEADER_ID NUMBER
ITEM_REVISION VARCHAR2(3)
UNIT_SELLING_PRICE NUMBER
UNIT_LIST_PRICE NUMBER
TAX_VALUE NUMBER
CONTEXT VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(240)
ATTRIBUTE2 VARCHAR2(240)
ATTRIBUTE3 VARCHAR2(240)
ATTRIBUTE4 VARCHAR2(240)
ATTRIBUTE5 VARCHAR2(240)
ATTRIBUTE6 VARCHAR2(240)
ATTRIBUTE7 VARCHAR2(240)
ATTRIBUTE8 VARCHAR2(240)
ATTRIBUTE9 VARCHAR2(240)
ATTRIBUTE10 VARCHAR2(240)
ATTRIBUTE11 VARCHAR2(240)
ATTRIBUTE12 VARCHAR2(240)
ATTRIBUTE13 VARCHAR2(240)
ATTRIBUTE14 VARCHAR2(240)
ATTRIBUTE15 VARCHAR2(240)
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30)
GLOBAL_ATTRIBUTE1 VARCHAR2(240)
GLOBAL_ATTRIBUTE2 VARCHAR2(240)
GLOBAL_ATTRIBUTE3 VARCHAR2(240)
GLOBAL_ATTRIBUTE4 VARCHAR2(240)
GLOBAL_ATTRIBUTE5 VARCHAR2(240)
GLOBAL_ATTRIBUTE6 VARCHAR2(240)
GLOBAL_ATTRIBUTE7 VARCHAR2(240)
GLOBAL_ATTRIBUTE8 VARCHAR2(240)
GLOBAL_ATTRIBUTE9 VARCHAR2(240)
GLOBAL_ATTRIBUTE10 VARCHAR2(240)
GLOBAL_ATTRIBUTE11 VARCHAR2(240)
GLOBAL_ATTRIBUTE12 VARCHAR2(240)
GLOBAL_ATTRIBUTE13 VARCHAR2(240)
GLOBAL_ATTRIBUTE14 VARCHAR2(240)
GLOBAL_ATTRIBUTE15 VARCHAR2(240)
GLOBAL_ATTRIBUTE16 VARCHAR2(240)
GLOBAL_ATTRIBUTE17 VARCHAR2(240)
GLOBAL_ATTRIBUTE18 VARCHAR2(240)
GLOBAL_ATTRIBUTE19 VARCHAR2(240)
GLOBAL_ATTRIBUTE20 VARCHAR2(240)
PRICING_CONTEXT VARCHAR2(30)
PRICING_ATTRIBUTE1 VARCHAR2(240)
PRICING_ATTRIBUTE2 VARCHAR2(240)
PRICING_ATTRIBUTE3 VARCHAR2(240)
PRICING_ATTRIBUTE4 VARCHAR2(240)
PRICING_ATTRIBUTE5 VARCHAR2(240)
PRICING_ATTRIBUTE6 VARCHAR2(240)
PRICING_ATTRIBUTE7 VARCHAR2(240)
PRICING_ATTRIBUTE8 VARCHAR2(240)
PRICING_ATTRIBUTE9 VARCHAR2(240)
PRICING_ATTRIBUTE10 VARCHAR2(240)
INDUSTRY_CONTEXT VARCHAR2(30)
INDUSTRY_ATTRIBUTE1 VARCHAR2(240)
INDUSTRY_ATTRIBUTE2 VARCHAR2(240)
INDUSTRY_ATTRIBUTE3 VARCHAR2(240)
INDUSTRY_ATTRIBUTE4 VARCHAR2(240)
INDUSTRY_ATTRIBUTE5 VARCHAR2(240)
INDUSTRY_ATTRIBUTE6 VARCHAR2(240)
INDUSTRY_ATTRIBUTE7 VARCHAR2(240)
INDUSTRY_ATTRIBUTE8 VARCHAR2(240)
INDUSTRY_ATTRIBUTE9 VARCHAR2(240)
INDUSTRY_ATTRIBUTE10 VARCHAR2(240)
INDUSTRY_ATTRIBUTE11 VARCHAR2(240)
INDUSTRY_ATTRIBUTE13 VARCHAR2(240)
INDUSTRY_ATTRIBUTE12 VARCHAR2(240)
INDUSTRY_ATTRIBUTE14 VARCHAR2(240)
INDUSTRY_ATTRIBUTE15 VARCHAR2(240)
INDUSTRY_ATTRIBUTE16 VARCHAR2(240)
INDUSTRY_ATTRIBUTE17 VARCHAR2(240)
INDUSTRY_ATTRIBUTE18 VARCHAR2(240)
INDUSTRY_ATTRIBUTE19 VARCHAR2(240)
INDUSTRY_ATTRIBUTE20 VARCHAR2(240)
INDUSTRY_ATTRIBUTE21 VARCHAR2(240)
INDUSTRY_ATTRIBUTE22 VARCHAR2(240)
INDUSTRY_ATTRIBUTE23 VARCHAR2(240)
INDUSTRY_ATTRIBUTE24 VARCHAR2(240)
INDUSTRY_ATTRIBUTE25 VARCHAR2(240)
INDUSTRY_ATTRIBUTE26 VARCHAR2(240)
INDUSTRY_ATTRIBUTE27 VARCHAR2(240)
INDUSTRY_ATTRIBUTE28 VARCHAR2(240)
INDUSTRY_ATTRIBUTE29 VARCHAR2(240)
INDUSTRY_ATTRIBUTE30 VARCHAR2(240)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
TOP_MODEL_LINE_ID NUMBER
LINK_TO_LINE_ID NUMBER
COMPONENT_SEQUENCE_ID NUMBER
COMPONENT_CODE VARCHAR2(1000)
CONFIG_DISPLAY_SEQUENCE NUMBER
SORT_ORDER VARCHAR2(2000)
ITEM_TYPE_CODE VARCHAR2(30)
OPTION_NUMBER NUMBER
OPTION_FLAG VARCHAR2(1)
DEP_PLAN_REQUIRED_FLAG VARCHAR2(1)
VISIBLE_DEMAND_FLAG VARCHAR2(1)
LINE_CATEGORY_CODE NOT NULL VARCHAR2(30)
ACTUAL_SHIPMENT_DATE DATE
CUSTOMER_TRX_LINE_ID NUMBER
RETURN_CONTEXT VARCHAR2(30)
RETURN_ATTRIBUTE1 VARCHAR2(240)
RETURN_ATTRIBUTE2 VARCHAR2(240)
RETURN_ATTRIBUTE3 VARCHAR2(240)
RETURN_ATTRIBUTE4 VARCHAR2(240)
RETURN_ATTRIBUTE5 VARCHAR2(240)
RETURN_ATTRIBUTE6 VARCHAR2(240)
RETURN_ATTRIBUTE7 VARCHAR2(240)
RETURN_ATTRIBUTE8 VARCHAR2(240)
RETURN_ATTRIBUTE9 VARCHAR2(240)
RETURN_ATTRIBUTE10 VARCHAR2(240)
RETURN_ATTRIBUTE11 VARCHAR2(240)
RETURN_ATTRIBUTE12 VARCHAR2(240)
RETURN_ATTRIBUTE13 VARCHAR2(240)
RETURN_ATTRIBUTE14 VARCHAR2(240)
RETURN_ATTRIBUTE15 VARCHAR2(240)
ACTUAL_ARRIVAL_DATE DATE
ATO_LINE_ID NUMBER
AUTO_SELECTED_QUANTITY NUMBER
COMPONENT_NUMBER NUMBER
EARLIEST_ACCEPTABLE_DATE DATE
EXPLOSION_DATE DATE
LATEST_ACCEPTABLE_DATE DATE
MODEL_GROUP_NUMBER NUMBER
SCHEDULE_ARRIVAL_DATE DATE
SHIP_MODEL_COMPLETE_FLAG VARCHAR2(1)
SCHEDULE_STATUS_CODE VARCHAR2(30)
SOURCE_TYPE_CODE VARCHAR2(30)
CANCELLED_FLAG VARCHAR2(1)
OPEN_FLAG NOT NULL VARCHAR2(1)
BOOKED_FLAG NOT NULL VARCHAR2(1)
SALESREP_ID NUMBER(15)
RETURN_REASON_CODE VARCHAR2(30)
ARRIVAL_SET_ID NUMBER
SHIP_SET_ID NUMBER
SPLIT_FROM_LINE_ID NUMBER
CUST_PRODUCTION_SEQ_NUM VARCHAR2(50)
AUTHORIZED_TO_SHIP_FLAG VARCHAR2(1)
OVER_SHIP_REASON_CODE VARCHAR2(30)
OVER_SHIP_RESOLVED_FLAG VARCHAR2(1)
ORDERED_ITEM_ID NUMBER
ITEM_IDENTIFIER_TYPE VARCHAR2(30)
CONFIGURATION_ID NUMBER
COMMITMENT_ID NUMBER
SHIPPING_INTERFACED_FLAG VARCHAR2(1)
CREDIT_INVOICE_LINE_ID NUMBER
FIRST_ACK_CODE VARCHAR2(30)
FIRST_ACK_DATE DATE
LAST_ACK_CODE VARCHAR2(30)
LAST_ACK_DATE DATE
PLANNING_PRIORITY NUMBER
ORDER_SOURCE_ID NUMBER
ORIG_SYS_SHIPMENT_REF VARCHAR2(50)
CHANGE_SEQUENCE VARCHAR2(50)
DROP_SHIP_FLAG VARCHAR2(1)
CUSTOMER_LINE_NUMBER VARCHAR2(50)
CUSTOMER_SHIPMENT_NUMBER VARCHAR2(50)
CUSTOMER_ITEM_NET_PRICE NUMBER
CUSTOMER_PAYMENT_TERM_ID NUMBER
FULFILLED_FLAG VARCHAR2(1)
END_ITEM_UNIT_NUMBER VARCHAR2(30)
CONFIG_HEADER_ID NUMBER
CONFIG_REV_NBR NUMBER
MFG_COMPONENT_SEQUENCE_ID NUMBER
SHIPPING_INSTRUCTIONS VARCHAR2(2000)
PACKING_INSTRUCTIONS VARCHAR2(2000)
INVOICED_QUANTITY NUMBER
REFERENCE_CUSTOMER_TRX_LINE_ID NUMBER
SPLIT_BY VARCHAR2(30)
LINE_SET_ID NUMBER
SERVICE_TXN_REASON_CODE VARCHAR2(30)
SERVICE_TXN_COMMENTS VARCHAR2(2000)
SERVICE_DURATION NUMBER
SERVICE_START_DATE DATE
SERVICE_END_DATE DATE
SERVICE_COTERMINATE_FLAG VARCHAR2(1)
UNIT_LIST_PERCENT NUMBER
UNIT_SELLING_PERCENT NUMBER
UNIT_PERCENT_BASE_PRICE NUMBER
SERVICE_NUMBER NUMBER
SERVICE_PERIOD VARCHAR2(3)
SHIPPABLE_FLAG VARCHAR2(1)
MODEL_REMNANT_FLAG VARCHAR2(1)
RE_SOURCE_FLAG VARCHAR2(1)
FLOW_STATUS_CODE VARCHAR2(30)
TP_CONTEXT VARCHAR2(30)
TP_ATTRIBUTE1 VARCHAR2(240)
TP_ATTRIBUTE2 VARCHAR2(240)
TP_ATTRIBUTE3 VARCHAR2(240)
TP_ATTRIBUTE4 VARCHAR2(240)
TP_ATTRIBUTE5 VARCHAR2(240)
TP_ATTRIBUTE6 VARCHAR2(240)
TP_ATTRIBUTE7 VARCHAR2(240)
TP_ATTRIBUTE8 VARCHAR2(240)
TP_ATTRIBUTE9 VARCHAR2(240)
TP_ATTRIBUTE10 VARCHAR2(240)
TP_ATTRIBUTE11 VARCHAR2(240)
TP_ATTRIBUTE12 VARCHAR2(240)
TP_ATTRIBUTE13 VARCHAR2(240)
TP_ATTRIBUTE14 VARCHAR2(240)
TP_ATTRIBUTE15 VARCHAR2(240)
FULFILLMENT_METHOD_CODE VARCHAR2(30)
MARKETING_SOURCE_CODE_ID NUMBER
SERVICE_REFERENCE_TYPE_CODE VARCHAR2(30)
SERVICE_REFERENCE_LINE_ID NUMBER
SERVICE_REFERENCE_SYSTEM_ID NUMBER
CALCULATE_PRICE_FLAG VARCHAR2(1)
UPGRADED_FLAG VARCHAR2(1)
REVENUE_AMOUNT NUMBER
FULFILLMENT_DATE DATE
ATTRIBUTE16 VARCHAR2(240)
ATTRIBUTE17 VARCHAR2(240)
ATTRIBUTE18 VARCHAR2(240)
ATTRIBUTE19 VARCHAR2(240)
ATTRIBUTE20 VARCHAR2(240)
PREFERRED_GRADE VARCHAR2(4)
ORDERED_QUANTITY2 NUMBER
ORDERED_QUANTITY_UOM2 VARCHAR2(3)
SHIPPING_QUANTITY2 NUMBER
CANCELLED_QUANTITY2 NUMBER
SHIPPED_QUANTITY2 NUMBER
SHIPPING_QUANTITY_UOM2 VARCHAR2(3)
FULFILLED_QUANTITY2 NUMBER
MFG_LEAD_TIME NUMBER
LOCK_CONTROL NUMBER
SUBINVENTORY VARCHAR2(10)
UNIT_LIST_PRICE_PER_PQTY NUMBER
UNIT_SELLING_PRICE_PER_PQTY NUMBER
PRICE_REQUEST_CODE VARCHAR2(240)
ORIGINAL_INVENTORY_ITEM_ID NUMBER
ORIGINAL_ORDERED_ITEM_ID NUMBER
ORIGINAL_ORDERED_ITEM VARCHAR2(2000)
ORIGINAL_ITEM_IDENTIFIER_TYPE VARCHAR2(30)
ITEM_SUBSTITUTION_TYPE_CODE VARCHAR2(30)
OVERRIDE_ATP_DATE_CODE VARCHAR2(30)
LATE_DEMAND_PENALTY_FACTOR NUMBER
ACCOUNTING_RULE_DURATION NUMBER(15)
USER_ITEM_DESCRIPTION VARCHAR2(1000)
UNIT_COST NUMBER
ITEM_RELATIONSHIP_TYPE NUMBER
BLANKET_LINE_NUMBER NUMBER
BLANKET_NUMBER NUMBER
BLANKET_VERSION_NUMBER NUMBER
SALES_DOCUMENT_TYPE_CODE VARCHAR2(30)
FIRM_DEMAND_FLAG VARCHAR2(1)
EARLIEST_SHIP_DATE DATE
SQL> desc oe_order_headers_all
Name Null? Type
----------------------------------------- -------- ----------------------------
HEADER_ID NOT NULL NUMBER
ORG_ID NUMBER
ORDER_TYPE_ID NOT NULL NUMBER
ORDER_NUMBER NOT NULL NUMBER
VERSION_NUMBER NOT NULL NUMBER
EXPIRATION_DATE DATE
ORDER_SOURCE_ID NUMBER
SOURCE_DOCUMENT_TYPE_ID NUMBER
ORIG_SYS_DOCUMENT_REF VARCHAR2(50)
SOURCE_DOCUMENT_ID NUMBER
ORDERED_DATE DATE
REQUEST_DATE DATE
PRICING_DATE DATE
SHIPMENT_PRIORITY_CODE VARCHAR2(30)
DEMAND_CLASS_CODE VARCHAR2(30)
PRICE_LIST_ID NUMBER
TAX_EXEMPT_FLAG VARCHAR2(1)
TAX_EXEMPT_NUMBER VARCHAR2(80)
TAX_EXEMPT_REASON_CODE VARCHAR2(30)
CONVERSION_RATE NUMBER
CONVERSION_TYPE_CODE VARCHAR2(30)
CONVERSION_RATE_DATE DATE
PARTIAL_SHIPMENTS_ALLOWED VARCHAR2(1)
SHIP_TOLERANCE_ABOVE NUMBER
SHIP_TOLERANCE_BELOW NUMBER
TRANSACTIONAL_CURR_CODE VARCHAR2(15)
AGREEMENT_ID NUMBER
TAX_POINT_CODE VARCHAR2(30)
CUST_PO_NUMBER VARCHAR2(50)
INVOICING_RULE_ID NUMBER(15)
ACCOUNTING_RULE_ID NUMBER(15)
PAYMENT_TERM_ID NUMBER(15)
SHIPPING_METHOD_CODE VARCHAR2(30)
FREIGHT_CARRIER_CODE VARCHAR2(30)
FOB_POINT_CODE VARCHAR2(30)
FREIGHT_TERMS_CODE VARCHAR2(30)
SOLD_FROM_ORG_ID NUMBER
SOLD_TO_ORG_ID NUMBER
SHIP_FROM_ORG_ID NUMBER
SHIP_TO_ORG_ID NUMBER
INVOICE_TO_ORG_ID NUMBER
DELIVER_TO_ORG_ID NUMBER
SOLD_TO_CONTACT_ID NUMBER
SHIP_TO_CONTACT_ID NUMBER
INVOICE_TO_CONTACT_ID NUMBER
DELIVER_TO_CONTACT_ID NUMBER
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
CONTEXT VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(240)
ATTRIBUTE2 VARCHAR2(240)
ATTRIBUTE3 VARCHAR2(240)
ATTRIBUTE4 VARCHAR2(240)
ATTRIBUTE5 VARCHAR2(240)
ATTRIBUTE6 VARCHAR2(240)
ATTRIBUTE7 VARCHAR2(240)
ATTRIBUTE8 VARCHAR2(240)
ATTRIBUTE9 VARCHAR2(240)
ATTRIBUTE10 VARCHAR2(240)
ATTRIBUTE11 VARCHAR2(240)
ATTRIBUTE12 VARCHAR2(240)
ATTRIBUTE13 VARCHAR2(240)
ATTRIBUTE14 VARCHAR2(240)
ATTRIBUTE15 VARCHAR2(240)
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30)
GLOBAL_ATTRIBUTE1 VARCHAR2(240)
GLOBAL_ATTRIBUTE2 VARCHAR2(240)
GLOBAL_ATTRIBUTE3
|
|
|
|
|
| Subhranil |
| Posted: Apr 10, 2006 07:45:04 AM | |
|
Total Post: 28
Joined: Jul, 2005
|
table ra_customers is not there
|
|
|
|
|
| Srinivas |
| Posted: Apr 10, 2006 07:58:36 AM | |
|
Total Post: 7
Joined: Apr, 2006
|
SQL> desc ra_customers
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
CUSTOMER_ID NOT NULL NUMBER(15)
PARTY_ID NOT NULL NUMBER(15)
PARTY_NUMBER NOT NULL VARCHAR2(30)
PARTY_TYPE NOT NULL VARCHAR2(30)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
CUSTOMER_NAME VARCHAR2(50)
CUSTOMER_NUMBER NOT NULL VARCHAR2(30)
ORIG_SYSTEM_REFERENCE NOT NULL VARCHAR2(240)
STATUS NOT NULL VARCHAR2(1)
LAST_UPDATE_LOGIN NUMBER(15)
CUSTOMER_TYPE VARCHAR2(30)
CUSTOMER_PROSPECT_CODE CHAR(8)
CUSTOMER_CLASS_CODE VARCHAR2(30)
PRIMARY_SALESREP_ID NUMBER(15)
SALES_CHANNEL_CODE VARCHAR2(30)
SIC_CODE VARCHAR2(30)
ORDER_TYPE_ID NUMBER(15)
PRICE_LIST_ID NUMBER(15)
ATTRIBUTE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
REQUEST_ID NUMBER(15)
PROGRAM_APPLICATION_ID NUMBER(15)
PROGRAM_ID NUMBER(15)
PROGRAM_UPDATE_DATE DATE
ANALYSIS_FY VARCHAR2(5)
CUSTOMER_CATEGORY_CODE VARCHAR2(30)
CUSTOMER_GROUP_CODE VARCHAR2
CUSTOMER_KEY VARCHAR2(500)
CUSTOMER_SUBGROUP_CODE VARCHAR2
FISCAL_YEAREND_MONTH VARCHAR2(30)
NET_WORTH NUMBER
NUM_OF_EMPLOYEES NUMBER
POTENTIAL_REVENUE_CURR_FY NUMBER
POTENTIAL_REVENUE_NEXT_FY NUMBER
RANK VARCHAR2
REFERENCE_USE_FLAG VARCHAR2(1)
TAX_CODE VARCHAR2(50)
TAX_REFERENCE VARCHAR2(50)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
THIRD_PARTY_FLAG VARCHAR2(1)
ACCESS_TEMPLATE_ENTITY_CODE VARCHAR2
PRIMARY_SPECIALIST_ID NUMBER(15)
SECONDARY_SPECIALIST_ID NUMBER(15)
COMPETITOR_FLAG VARCHAR2(1)
DUNNING_SITE_USE_ID NUMBER
STATEMENT_SITE_USE_ID NUMBER
ORIG_SYSTEM VARCHAR2
YEAR_ESTABLISHED NUMBER
COTERMINATE_DAY_MONTH VARCHAR2(6)
FOB_POINT VARCHAR2(30)
FREIGHT_TERM VARCHAR2(30)
GSA_INDICATOR VARCHAR2(1)
SHIP_PARTIAL VARCHAR2(1)
SHIP_VIA VARCHAR2(30)
WAREHOUSE_ID NUMBER(15)
PAYMENT_TERM_ID NUMBER(15)
TAX_EXEMPT VARCHAR2
TAX_EXEMPT_NUM VARCHAR2
TAX_EXEMPT_REASON_CODE VARCHAR2
JGZZ_FISCAL_CODE VARCHAR2(20)
DO_NOT_MAIL_FLAG VARCHAR2(1)
MISSION_STATEMENT VARCHAR2(2000)
CUSTOMER_NAME_PHONETIC VARCHAR2(320)
TAX_HEADER_LEVEL_FLAG VARCHAR2(1)
TAX_ROUNDING_RULE VARCHAR2(30)
WH_UPDATE_DATE DATE
GLOBAL_ATTRIBUTE1 VARCHAR2(150)
GLOBAL_ATTRIBUTE2 VARCHAR2(150)
GLOBAL_ATTRIBUTE3 VARCHAR2(150)
GLOBAL_ATTRIBUTE4 VARCHAR2(150)
GLOBAL_ATTRIBUTE5 VARCHAR2(150)
GLOBAL_ATTRIBUTE6 VARCHAR2(150)
GLOBAL_ATTRIBUTE7 VARCHAR2(150)
GLOBAL_ATTRIBUTE8 VARCHAR2(150)
GLOBAL_ATTRIBUTE9 VARCHAR2(150)
GLOBAL_ATTRIBUTE10 VARCHAR2(150)
GLOBAL_ATTRIBUTE11 VARCHAR2(150)
GLOBAL_ATTRIBUTE12 VARCHAR2(150)
GLOBAL_ATTRIBUTE13 VARCHAR2(150)
GLOBAL_ATTRIBUTE14 VARCHAR2(150)
GLOBAL_ATTRIBUTE15 VARCHAR2(150)
GLOBAL_ATTRIBUTE16 VARCHAR2(150)
GLOBAL_ATTRIBUTE17 VARCHAR2(150)
GLOBAL_ATTRIBUTE18 VARCHAR2(150)
GLOBAL_ATTRIBUTE19 VARCHAR2(150)
GLOBAL_ATTRIBUTE20 VARCHAR2(150)
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30)
URL VARCHAR2(2000)
LANGUAGE VARCHAR2
TRANSLATED_CUSTOMER_NAME VARCHAR2
PERSON_PRE_NAME_ADJUNCT VARCHAR2(30)
PERSON_FIRST_NAME VARCHAR2(150)
PERSON_MIDDLE_NAME VARCHAR2(60)
PERSON_LAST_NAME VARCHAR2(150)
PERSON_SUFFIX VARCHAR2(30)
PERSON_FIRST_NAME_PHONETIC VARCHAR2(60)
PERSON_LAST_NAME_PHONETIC VARCHAR2(60)
SHIP_SETS_INCLUDE_LINES_FLAG VARCHAR2(1)
ARRIVALSETS_INCLUDE_LINES_FLAG VARCHAR2(1)
SCHED_DATE_PUSH_FLAG VARCHAR2(1)
OVER_SHIPMENT_TOLERANCE NUMBER
UNDER_SHIPMENT_TOLERANCE NUMBER
OVER_RETURN_TOLERANCE NUMBER
UNDER_RETURN_TOLERANCE NUMBER
ITEM_CROSS_REF_PREF VARCHAR2(30)
DATE_TYPE_PREFERENCE VARCHAR2(20)
DATES_NEGATIVE_TOLERANCE NUMBER
DATES_POSITIVE_TOLERANCE NUMBER
INVOICE_QUANTITY_RULE VARCHAR2(30)
ATTRIBUTE16 VARCHAR2(150)
ATTRIBUTE17 VARCHAR2(150)
ATTRIBUTE18 VARCHAR2(150)
ATTRIBUTE19 VARCHAR2(150)
ATTRIBUTE20 VARCHAR2(150)
DUNS_NUMBER NUMBER
DUNS_NUMBER_C VARCHAR2(30)
PARTY_LAST_UPDATE_DATE NOT NULL DATE
SIC_CODE_TYPE VARCHAR2(30)
SQL> spool off
|
|
|
|
|
| tim |
| Posted: Apr 19, 2006 02:16:19 PM | |
|
Total Post: 1
Joined: Apr, 2006
|
well an oracle guru i'm not but i would point out a couple things about your query.
first in the query itself you have 3 redundant lines
AND trunc(ool.request_date) between nvl(null, trunc(ool.request_date)) and nvl(null, trunc(ool.request_date))
AND nvl(msi.segment1,'null') between nvl(null, nvl(msi.segment1,'null')) and nvl(null,nvl(msi.segment1,'null'))
AND nvl(ooh.order_number,0) between nvl(null, nvl(ooh.order_number,0)) and nvl(null,nvl(ooh.order_number,0))
these lines as far as i can tell are asking mathmatically if X between Y and Y, as nvl(null, trunc(ool.request_date)) will always = nvl(null, trunc(ool.request_date))
i might make a view as below and query the view.
create view stuff_view as
select distinct
nvl(ooh.order_number,0) order_number,
ooh.order_number order_number_grouped,
ool.line_number,
ool.line_id,
nvl(ool.ordered_quantity,0) ordered_quantity
nvl(msi.segment1,0) item,
wdd.released_status,
wnd.creation_date Pick_Release_Date,
rc.customer_number cmf
1 Order_Line_Count_Count,
msi.description description,
ool.request_date,
ool.actual_shipment_date,
nvl(ool.ordered_quantity,0) ordered_quantity,
nvl(ool.shipped_quantity,0) quantity_shipped,
decode(ool.shipped_quantity,null,'N',0,'N','Y') Ship_Confirm_Complete,
from wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
mtl_system_items msi,
oe_order_lines_all ool,
oe_order_headers_all ooh,
ra_customers rc
where
wdd.delivery_detail_id = wda.delivery_detail_id
and ool.header_id = ooh.header_id
AND ooh.sold_to_org_id = rc.customer_id
and ool.org_id=msi.organization_id
AND wdd.source_header_id = ooh.header_id
AND ool.inventory_item_id= msi.inventory_item_id
and wdd.source_line_id = ool.line_id
and wda.delivery_id = wnd.delivery_id (+)
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 272 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
