allcommands_practiced

select * from po_requisition_lines_all  prla where prla.QUANTITY=10
and creation_date>sysdate-0.1;

select * from po_requisition_lines_all  prla where prla.QUANTITY=10;

select * from po_requisition_lines_all  prla where prla.QUANTITY=10
and creation_date>sysdate-0.1;

select * from po_headers_all where creation_date>sysdate-0.1;

select * from po_requisition_headers_all where segment1='14307';

SELECT * FROM EMP;

SELECT PHA.ORG_ID,PHA.SEGMENT1,PHA.VENDOR_ID FROM PO_HEADERS_ALL PHA;

SELECT * FROM DEPT;

SELECT * FROM AP_INVOICE_LINES_ALL;

SELECT * FROM OE_ORDER_HEADERS_aLL;

SELECT * FROM OE_ORDER_LINES_ALL;

SELECT * FROM WSH_DELIVERY_DETAILS;

SELECT * FROM WSH_DELIVERY_ASSIGNMENTS;

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'MTL_ITEM';

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'MTL_ITEM_P';

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'MTL_ITEM_P';

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'MT';

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'MTL';

SELECT * FROM MTL_SYSTEM_ITEMS_B;

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE ROWNUM<20;

SELECT MSIB.INVENTORY_ITEM_ID,MSIB.ORGANIZATION_ID,MSIB.SEGMENT1,MSIB.DESCRIPTION,MSIB.ITEM_TYPE,
MSIB.INVOICE_ENABLED_FLAG,MSIB.STOCK_ENABLED_FLAG,MSIB.PURCHASING_ENABLED_FLAG FROM MTL_SYSTEM_ITEMS_B MSIB WHERE ROWNUM<20;

SELECT MSIB.INVENTORY_ITEM_ID,MSIB.ORGANIZATION_ID,MSIB.SEGMENT1||'KRISHNA',MSIB.DESCRIPTION,MSIB.ITEM_TYPE,
MSIB.INVOICE_ENABLED_FLAG,MSIB.STOCK_ENABLED_FLAG,MSIB.PURCHASING_ENABLED_FLAG FROM MTL_SYSTEM_ITEMS_B MSIB WHERE ROWNUM<20;

SELECT * FROM ALL_TABLES;

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE 'MTL_IT';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE 'MTL_%';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE 'Q%';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE 'QP%';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE 'QP_%';

Create table SR_ITEM_IMPORT_STAGE
(
Segment1                                           VARCHAR2(80)  NOT NULL
,Description                           VARCHAR2(240)  
,Organization_id                        NUMBER  NOT NULL
,Template_id            NUMBER    NOT NULL
,Process_flag            NUMBER      NOT NULL
,Set_Process_id                                        NUMBER
,Transaction_Type                                    VARCHAR2(30)
,Validation_Record_Flag                VARCHAR2(1)
,Error_Message                      VARCHAR2(2000)
,Created_by                NUMBER
,Creation_date                DATE
,Last_Updated_by             NUMBER
,Last_Update_Date              DATE
,Last_Update_Login            NUMBER
);

SELECT
*
FROM
MTL_SYSTEM_ITEMS_B;

CREATE TABLE MTL_SYSTEM_ITEMS_INTERFACE_STG
(
  INVENTORY_ITEM_ID               NUMBER,
  ORGANIZATION_ID                 NUMBER,
  LAST_UPDATE_DATE                DATE,
  LAST_UPDATED_BY                 NUMBER,
  CREATION_DATE                   DATE,
  CREATED_BY                      NUMBER,
  LAST_UPDATE_LOGIN               NUMBER,
  SUMMARY_FLAG                    VARCHAR2(1 BYTE),
  ENABLED_FLAG                    VARCHAR2(1 BYTE),
  START_DATE_ACTIVE               DATE,
  END_DATE_ACTIVE                 DATE,
  DESCRIPTION                     VARCHAR2(240 BYTE),
  BUYER_ID                        NUMBER,
  ACCOUNTING_RULE_ID              NUMBER,
  INVOICING_RULE_ID               NUMBER,
  SEGMENT1                        VARCHAR2(40 BYTE),
  SEGMENT2                        VARCHAR2(40 BYTE),
  SEGMENT3                        VARCHAR2(40 BYTE),
  SEGMENT4                        VARCHAR2(40 BYTE),
  SEGMENT5                        VARCHAR2(40 BYTE),
  SEGMENT6                        VARCHAR2(40 BYTE),
  SEGMENT7                        VARCHAR2(40 BYTE),
  SEGMENT8                        VARCHAR2(40 BYTE),
  SEGMENT9                        VARCHAR2(40 BYTE),
  SEGMENT10                       VARCHAR2(40 BYTE),
  SEGMENT11                       VARCHAR2(40 BYTE),
  SEGMENT12                       VARCHAR2(40 BYTE),
  SEGMENT13                       VARCHAR2(40 BYTE),
  SEGMENT14                       VARCHAR2(40 BYTE),
  SEGMENT15                       VARCHAR2(40 BYTE),
  SEGMENT16                       VARCHAR2(40 BYTE),
  SEGMENT17                       VARCHAR2(40 BYTE),
  SEGMENT18                       VARCHAR2(40 BYTE),
  SEGMENT19                       VARCHAR2(40 BYTE),
  SEGMENT20                       VARCHAR2(40 BYTE),
  ATTRIBUTE_CATEGORY              VARCHAR2(30 BYTE),
  ATTRIBUTE1                      VARCHAR2(240 BYTE),
  ATTRIBUTE2                      VARCHAR2(240 BYTE),
  ATTRIBUTE3                      VARCHAR2(240 BYTE),
  ATTRIBUTE4                      VARCHAR2(240 BYTE),
  ATTRIBUTE5                      VARCHAR2(240 BYTE),
  ATTRIBUTE6                      VARCHAR2(240 BYTE),
  ATTRIBUTE7                      VARCHAR2(240 BYTE),
  ATTRIBUTE8                      VARCHAR2(240 BYTE),
  ATTRIBUTE9                      VARCHAR2(240 BYTE),
  ATTRIBUTE10                     VARCHAR2(240 BYTE),
  ATTRIBUTE11                     VARCHAR2(240 BYTE),
  ATTRIBUTE12                     VARCHAR2(240 BYTE),
  ATTRIBUTE13                     VARCHAR2(240 BYTE),
  ATTRIBUTE14                     VARCHAR2(240 BYTE),
  ATTRIBUTE15                     VARCHAR2(240 BYTE),
  PURCHASING_ITEM_FLAG            VARCHAR2(1 BYTE),
  SHIPPABLE_ITEM_FLAG             VARCHAR2(1 BYTE),
  CUSTOMER_ORDER_FLAG             VARCHAR2(1 BYTE),
  INTERNAL_ORDER_FLAG             VARCHAR2(1 BYTE),
  SERVICE_ITEM_FLAG               VARCHAR2(1 BYTE),
  INVENTORY_ITEM_FLAG             VARCHAR2(1 BYTE),
  ENG_ITEM_FLAG                   VARCHAR2(1 BYTE),
  INVENTORY_ASSET_FLAG            VARCHAR2(1 BYTE),
  PURCHASING_ENABLED_FLAG         VARCHAR2(1 BYTE),
  CUSTOMER_ORDER_ENABLED_FLAG     VARCHAR2(1 BYTE),
  INTERNAL_ORDER_ENABLED_FLAG     VARCHAR2(1 BYTE),
  SO_TRANSACTIONS_FLAG            VARCHAR2(1 BYTE),
  MTL_TRANSACTIONS_ENABLED_FLAG   VARCHAR2(1 BYTE),
  STOCK_ENABLED_FLAG              VARCHAR2(1 BYTE),
  BOM_ENABLED_FLAG                VARCHAR2(1 BYTE),
  BUILD_IN_WIP_FLAG               VARCHAR2(1 BYTE),
  REVISION_QTY_CONTROL_CODE       NUMBER,
  ITEM_CATALOG_GROUP_ID           NUMBER,
  CATALOG_STATUS_FLAG             VARCHAR2(1 BYTE),
  RETURNABLE_FLAG                 VARCHAR2(1 BYTE),
  DEFAULT_SHIPPING_ORG            NUMBER,
  COLLATERAL_FLAG                 VARCHAR2(1 BYTE),
  TAXABLE_FLAG                    VARCHAR2(1 BYTE),
  QTY_RCV_EXCEPTION_CODE          VARCHAR2(25 BYTE),
  ALLOW_ITEM_DESC_UPDATE_FLAG     VARCHAR2(1 BYTE),
  INSPECTION_REQUIRED_FLAG        VARCHAR2(1 BYTE),
  RECEIPT_REQUIRED_FLAG           VARCHAR2(1 BYTE),
  MARKET_PRICE                    NUMBER,
  HAZARD_CLASS_ID                 NUMBER,
  RFQ_REQUIRED_FLAG               VARCHAR2(1 BYTE),
  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 BYTE),
  ENFORCE_SHIP_TO_LOCATION_CODE   VARCHAR2(25 BYTE),
  ALLOW_SUBSTITUTE_RECEIPTS_FLAG  VARCHAR2(1 BYTE),
  ALLOW_UNORDERED_RECEIPTS_FLAG   VARCHAR2(1 BYTE),
  ALLOW_EXPRESS_DELIVERY_FLAG     VARCHAR2(1 BYTE),
  DAYS_EARLY_RECEIPT_ALLOWED      NUMBER,
  DAYS_LATE_RECEIPT_ALLOWED       NUMBER,
  RECEIPT_DAYS_EXCEPTION_CODE     VARCHAR2(25 BYTE),
  RECEIVING_ROUTING_ID            NUMBER,
  INVOICE_CLOSE_TOLERANCE         NUMBER,
  RECEIVE_CLOSE_TOLERANCE         NUMBER,
  AUTO_LOT_ALPHA_PREFIX           VARCHAR2(30 BYTE),
  START_AUTO_LOT_NUMBER           VARCHAR2(30 BYTE),
  LOT_CONTROL_CODE                NUMBER,
  SHELF_LIFE_CODE                 NUMBER,
  SHELF_LIFE_DAYS                 NUMBER,
  SERIAL_NUMBER_CONTROL_CODE      NUMBER,
  START_AUTO_SERIAL_NUMBER        VARCHAR2(30 BYTE),
  AUTO_SERIAL_ALPHA_PREFIX        VARCHAR2(30 BYTE),
  SOURCE_TYPE                     NUMBER,
  SOURCE_ORGANIZATION_ID          NUMBER,
  SOURCE_SUBINVENTORY             VARCHAR2(10 BYTE),
  EXPENSE_ACCOUNT                 NUMBER,
  ENCUMBRANCE_ACCOUNT             NUMBER,
  RESTRICT_SUBINVENTORIES_CODE    NUMBER,
  UNIT_WEIGHT                     NUMBER,
  WEIGHT_UOM_CODE                 VARCHAR2(3 BYTE),
  VOLUME_UOM_CODE                 VARCHAR2(3 BYTE),
  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 BYTE),
  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 BYTE),
  REPETITIVE_PLANNING_FLAG        VARCHAR2(1 BYTE),
  PLANNING_EXCEPTION_SET          VARCHAR2(10 BYTE),
  BOM_ITEM_TYPE                   NUMBER,
  PICK_COMPONENTS_FLAG            VARCHAR2(1 BYTE),
  REPLENISH_TO_ORDER_FLAG         VARCHAR2(1 BYTE),
  BASE_ITEM_ID                    NUMBER,
  ATP_COMPONENTS_FLAG             VARCHAR2(1 BYTE),
  ATP_FLAG                        VARCHAR2(1 BYTE),
  FIXED_LEAD_TIME                 NUMBER,
  VARIABLE_LEAD_TIME              NUMBER,
  WIP_SUPPLY_LOCATOR_ID           NUMBER,
  WIP_SUPPLY_TYPE                 NUMBER,
  WIP_SUPPLY_SUBINVENTORY         VARCHAR2(10 BYTE),
  PRIMARY_UOM_CODE                VARCHAR2(3 BYTE),
  PRIMARY_UNIT_OF_MEASURE         VARCHAR2(25 BYTE),
  ALLOWED_UNITS_LOOKUP_CODE       NUMBER,
  COST_OF_SALES_ACCOUNT           NUMBER,
  SALES_ACCOUNT                   NUMBER,
  DEFAULT_INCLUDE_IN_ROLLUP_FLAG  VARCHAR2(1 BYTE),
  INVENTORY_ITEM_STATUS_CODE      VARCHAR2(10 BYTE),
  INVENTORY_PLANNING_CODE         NUMBER,
  PLANNER_CODE                    VARCHAR2(10 BYTE),
  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 BYTE),
  ENGINEERING_ITEM_ID             NUMBER,
  ENGINEERING_DATE                DATE,
  SERVICE_STARTING_DELAY          NUMBER,
  VENDOR_WARRANTY_FLAG            VARCHAR2(1 BYTE),
  SERVICEABLE_COMPONENT_FLAG      VARCHAR2(1 BYTE),
  SERVICEABLE_PRODUCT_FLAG        VARCHAR2(1 BYTE),
  BASE_WARRANTY_SERVICE_ID        NUMBER,
  PAYMENT_TERMS_ID                NUMBER,
  PREVENTIVE_MAINTENANCE_FLAG     VARCHAR2(1 BYTE),
  PRIMARY_SPECIALIST_ID           NUMBER,
  SECONDARY_SPECIALIST_ID         NUMBER,
  SERVICEABLE_ITEM_CLASS_ID       NUMBER,
  TIME_BILLABLE_FLAG              VARCHAR2(1 BYTE),
  MATERIAL_BILLABLE_FLAG          VARCHAR2(30 BYTE),
  EXPENSE_BILLABLE_FLAG           VARCHAR2(1 BYTE),
  PRORATE_SERVICE_FLAG            VARCHAR2(1 BYTE),
  COVERAGE_SCHEDULE_ID            NUMBER,
  SERVICE_DURATION_PERIOD_CODE    VARCHAR2(10 BYTE),
  SERVICE_DURATION                NUMBER,
  WARRANTY_VENDOR_ID              NUMBER,
  MAX_WARRANTY_AMOUNT             NUMBER,
  RESPONSE_TIME_PERIOD_CODE       VARCHAR2(30 BYTE),
  RESPONSE_TIME_VALUE             NUMBER,
  NEW_REVISION_CODE               VARCHAR2(30 BYTE),
  INVOICEABLE_ITEM_FLAG           VARCHAR2(1 BYTE),
  TAX_CODE                        VARCHAR2(50 BYTE),
  INVOICE_ENABLED_FLAG            VARCHAR2(1 BYTE),
  MUST_USE_APPROVED_VENDOR_FLAG   VARCHAR2(1 BYTE),
  REQUEST_ID                      NUMBER,
  PROGRAM_APPLICATION_ID          NUMBER,
  PROGRAM_ID                      NUMBER,
  PROGRAM_UPDATE_DATE             DATE,
  OUTSIDE_OPERATION_FLAG          VARCHAR2(1 BYTE),
  OUTSIDE_OPERATION_UOM_TYPE      VARCHAR2(25 BYTE),
  SAFETY_STOCK_BUCKET_DAYS        NUMBER,
  AUTO_REDUCE_MPS                 NUMBER(22),
  COSTING_ENABLED_FLAG            VARCHAR2(1 BYTE),
  CYCLE_COUNT_ENABLED_FLAG        VARCHAR2(1 BYTE),
  DEMAND_SOURCE_LINE              VARCHAR2(30 BYTE),
  COPY_ITEM_ID                    NUMBER,
  SET_ID                          VARCHAR2(10 BYTE),
  REVISION                        VARCHAR2(3 BYTE),
  AUTO_CREATED_CONFIG_FLAG        VARCHAR2(1 BYTE),
  ITEM_TYPE                       VARCHAR2(30 BYTE),
  MODEL_CONFIG_CLAUSE_NAME        VARCHAR2(10 BYTE),
  SHIP_MODEL_COMPLETE_FLAG        VARCHAR2(1 BYTE),
  MRP_PLANNING_CODE               NUMBER,
  RETURN_INSPECTION_REQUIREMENT   NUMBER,
  DEMAND_SOURCE_TYPE              NUMBER,
  DEMAND_SOURCE_HEADER_ID         NUMBER,
  TRANSACTION_ID                  NUMBER,
  PROCESS_FLAG                    NUMBER,
  ORGANIZATION_CODE               VARCHAR2(3 BYTE),
  ITEM_NUMBER                     VARCHAR2(700 BYTE),
  COPY_ITEM_NUMBER                VARCHAR2(81 BYTE),
  TEMPLATE_ID                     NUMBER,
  TEMPLATE_NAME                   VARCHAR2(30 BYTE),
  COPY_ORGANIZATION_ID            NUMBER,
  COPY_ORGANIZATION_CODE          VARCHAR2(3 BYTE),
  ATO_FORECAST_CONTROL            NUMBER,
  TRANSACTION_TYPE                VARCHAR2(10 BYTE),
  MATERIAL_COST                   NUMBER,
  MATERIAL_SUB_ELEM               VARCHAR2(10 BYTE),
  MATERIAL_OH_RATE                NUMBER,
  MATERIAL_OH_SUB_ELEM            VARCHAR2(10 BYTE),
  MATERIAL_SUB_ELEM_ID            NUMBER,
  MATERIAL_OH_SUB_ELEM_ID         NUMBER,
  AUTO_REL_TIME_FENCE_CODE        NUMBER,
  AUTO_REL_TIME_FENCE_DAYS        NUMBER,
  CONTAINER_ITEM_FLAG             VARCHAR2(1 BYTE),
  VEHICLE_ITEM_FLAG               VARCHAR2(1 BYTE),
  MAXIMUM_LOAD_WEIGHT             NUMBER,
  MINIMUM_FILL_PERCENT            NUMBER,
  CONTAINER_TYPE_CODE             VARCHAR2(30 BYTE),
  INTERNAL_VOLUME                 NUMBER,
  SET_PROCESS_ID                  NUMBER        DEFAULT 0                     NOT NULL,
  CHECK_SHORTAGES_FLAG            VARCHAR2(1 BYTE),
  RELEASE_TIME_FENCE_CODE         NUMBER,
  RELEASE_TIME_FENCE_DAYS         NUMBER,
  WH_UPDATE_DATE                  DATE,
  PRODUCT_FAMILY_ITEM_ID          NUMBER,
  PURCHASING_TAX_CODE             VARCHAR2(50 BYTE),
  OVERCOMPLETION_TOLERANCE_TYPE   NUMBER,
  OVERCOMPLETION_TOLERANCE_VALUE  NUMBER,
  EFFECTIVITY_CONTROL             NUMBER,
  GLOBAL_ATTRIBUTE_CATEGORY       VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE1               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE2               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE3               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE4               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE5               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE6               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE7               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE8               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE9               VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE10              VARCHAR2(150 BYTE),
  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 BYTE),
  DEFECT_TRACKING_ON_FLAG         VARCHAR2(1 BYTE),
  USAGE_ITEM_FLAG                 VARCHAR2(1 BYTE),
  EVENT_FLAG                      VARCHAR2(1 BYTE),
  ELECTRONIC_FLAG                 VARCHAR2(1 BYTE),
  DOWNLOADABLE_FLAG               VARCHAR2(1 BYTE),
  VOL_DISCOUNT_EXEMPT_FLAG        VARCHAR2(1 BYTE),
  COUPON_EXEMPT_FLAG              VARCHAR2(1 BYTE),
  COMMS_NL_TRACKABLE_FLAG         VARCHAR2(1 BYTE),
  ASSET_CREATION_CODE             VARCHAR2(30 BYTE),
  COMMS_ACTIVATION_REQD_FLAG      VARCHAR2(1 BYTE),
  ORDERABLE_ON_WEB_FLAG           VARCHAR2(1 BYTE),
  BACK_ORDERABLE_FLAG             VARCHAR2(1 BYTE),
  WEB_STATUS                      VARCHAR2(30 BYTE),
  INDIVISIBLE_FLAG                VARCHAR2(1 BYTE),
  LONG_DESCRIPTION                VARCHAR2(4000 BYTE),
  DIMENSION_UOM_CODE              VARCHAR2(3 BYTE),
  UNIT_LENGTH                     NUMBER,
  UNIT_WIDTH                      NUMBER,
  UNIT_HEIGHT                     NUMBER,
  CARTONIZATION_GROUP_ID          NUMBER,
  BULK_PICKED_FLAG                VARCHAR2(1 BYTE),
  LOT_STATUS_ENABLED              VARCHAR2(1 BYTE),
  DEFAULT_LOT_STATUS_ID           NUMBER,
  SERIAL_STATUS_ENABLED           VARCHAR2(1 BYTE),
  DEFAULT_SERIAL_STATUS_ID        NUMBER,
  LOT_SPLIT_ENABLED               VARCHAR2(1 BYTE),
  LOT_MERGE_ENABLED               VARCHAR2(1 BYTE),
  INVENTORY_CARRY_PENALTY         NUMBER,
  OPERATION_SLACK_PENALTY         NUMBER,
  FINANCING_ALLOWED_FLAG          VARCHAR2(1 BYTE),
  EAM_ITEM_TYPE                   NUMBER,
  EAM_ACTIVITY_TYPE_CODE          VARCHAR2(30 BYTE),
  EAM_ACTIVITY_CAUSE_CODE         VARCHAR2(30 BYTE),
  EAM_ACT_NOTIFICATION_FLAG       VARCHAR2(1 BYTE),
  EAM_ACT_SHUTDOWN_STATUS         VARCHAR2(30 BYTE),
  DUAL_UOM_CONTROL                NUMBER,
  SECONDARY_UOM_CODE              VARCHAR2(3 BYTE),
  DUAL_UOM_DEVIATION_HIGH         NUMBER,
  DUAL_UOM_DEVIATION_LOW          NUMBER,
  CONTRACT_ITEM_TYPE_CODE         VARCHAR2(30 BYTE),
  SUBSCRIPTION_DEPEND_FLAG        VARCHAR2(1 BYTE),
  SERV_REQ_ENABLED_CODE           VARCHAR2(30 BYTE),
  SERV_BILLING_ENABLED_FLAG       VARCHAR2(1 BYTE),
  SERV_IMPORTANCE_LEVEL           NUMBER,
  PLANNED_INV_POINT_FLAG          VARCHAR2(1 BYTE),
  LOT_TRANSLATE_ENABLED           VARCHAR2(1 BYTE),
  DEFAULT_SO_SOURCE_TYPE          VARCHAR2(30 BYTE),
  CREATE_SUPPLY_FLAG              VARCHAR2(1 BYTE),
  SUBSTITUTION_WINDOW_CODE        NUMBER,
  SUBSTITUTION_WINDOW_DAYS        NUMBER,
  IB_ITEM_INSTANCE_CLASS          VARCHAR2(30 BYTE),
  CONFIG_MODEL_TYPE               VARCHAR2(30 BYTE),
  LOT_SUBSTITUTION_ENABLED        VARCHAR2(1 BYTE),
  MINIMUM_LICENSE_QUANTITY        NUMBER,
  EAM_ACTIVITY_SOURCE_CODE        VARCHAR2(30 BYTE),
  LIFECYCLE_ID                    NUMBER,
  CURRENT_PHASE_ID                NUMBER,
  TRACKING_QUANTITY_IND           VARCHAR2(30 BYTE),
  ONT_PRICING_QTY_SOURCE          VARCHAR2(30 BYTE),
  SECONDARY_DEFAULT_IND           VARCHAR2(30 BYTE),
  VMI_MINIMUM_UNITS               NUMBER,
  VMI_MINIMUM_DAYS                NUMBER,
  VMI_MAXIMUM_UNITS               NUMBER,
  VMI_MAXIMUM_DAYS                NUMBER,
  VMI_FIXED_ORDER_QUANTITY        NUMBER,
  SO_AUTHORIZATION_FLAG           NUMBER,
  CONSIGNED_FLAG                  NUMBER,
  ASN_AUTOEXPIRE_FLAG             NUMBER,
  VMI_FORECAST_TYPE               NUMBER,
  FORECAST_HORIZON                NUMBER,
  EXCLUDE_FROM_BUDGET_FLAG        NUMBER,
  DAYS_TGT_INV_SUPPLY             NUMBER,
  DAYS_TGT_INV_WINDOW             NUMBER,
  DAYS_MAX_INV_SUPPLY             NUMBER,
  DAYS_MAX_INV_WINDOW             NUMBER,
  DRP_PLANNED_FLAG                NUMBER,
  CRITICAL_COMPONENT_FLAG         NUMBER,
  CONTINOUS_TRANSFER              NUMBER,
  CONVERGENCE                     NUMBER,
  DIVERGENCE                      NUMBER,
  CONFIG_ORGS                     VARCHAR2(30 BYTE),
  CONFIG_MATCH                    VARCHAR2(30 BYTE),
  ATTRIBUTE16                     VARCHAR2(240 BYTE),
  ATTRIBUTE17                     VARCHAR2(240 BYTE),
  ATTRIBUTE18                     VARCHAR2(240 BYTE),
  ATTRIBUTE19                     VARCHAR2(240 BYTE),
  ATTRIBUTE20                     VARCHAR2(240 BYTE),
  ATTRIBUTE21                     VARCHAR2(240 BYTE),
  ATTRIBUTE22                     VARCHAR2(240 BYTE),
  ATTRIBUTE23                     VARCHAR2(240 BYTE),
  ATTRIBUTE24                     VARCHAR2(240 BYTE),
  ATTRIBUTE25                     VARCHAR2(240 BYTE),
  ATTRIBUTE26                     VARCHAR2(240 BYTE),
  ATTRIBUTE27                     VARCHAR2(240 BYTE),
  ATTRIBUTE28                     VARCHAR2(240 BYTE),
  ATTRIBUTE29                     VARCHAR2(240 BYTE),
  ATTRIBUTE30                     VARCHAR2(240 BYTE),
  CAS_NUMBER                      VARCHAR2(30 BYTE),
  CHILD_LOT_FLAG                  VARCHAR2(1 BYTE),
  CHILD_LOT_PREFIX                VARCHAR2(30 BYTE),
  CHILD_LOT_STARTING_NUMBER       NUMBER,
  CHILD_LOT_VALIDATION_FLAG       VARCHAR2(1 BYTE),
  COPY_LOT_ATTRIBUTE_FLAG         VARCHAR2(1 BYTE),
  DEFAULT_GRADE                   VARCHAR2(150 BYTE),
  EXPIRATION_ACTION_CODE          VARCHAR2(32 BYTE),
  EXPIRATION_ACTION_INTERVAL      NUMBER,
  GRADE_CONTROL_FLAG              VARCHAR2(1 BYTE),
  HAZARDOUS_MATERIAL_FLAG         VARCHAR2(1 BYTE),
  HOLD_DAYS                       NUMBER,
  LOT_DIVISIBLE_FLAG              VARCHAR2(1 BYTE),
  MATURITY_DAYS                   NUMBER,
  PARENT_CHILD_GENERATION_FLAG    VARCHAR2(1 BYTE),
  PROCESS_COSTING_ENABLED_FLAG    VARCHAR2(1 BYTE),
  PROCESS_EXECUTION_ENABLED_FLAG  VARCHAR2(1 BYTE),
  PROCESS_QUALITY_ENABLED_FLAG    VARCHAR2(1 BYTE),
  PROCESS_SUPPLY_LOCATOR_ID       NUMBER,
  PROCESS_SUPPLY_SUBINVENTORY     VARCHAR2(10 BYTE),
  PROCESS_YIELD_LOCATOR_ID        NUMBER,
  PROCESS_YIELD_SUBINVENTORY      VARCHAR2(10 BYTE),
  RECIPE_ENABLED_FLAG             VARCHAR2(1 BYTE),
  RETEST_INTERVAL                 NUMBER,
  CHARGE_PERIODICITY_CODE         VARCHAR2(3 BYTE),
  REPAIR_LEADTIME                 NUMBER,
  REPAIR_YIELD                    NUMBER,
  PREPOSITION_POINT               VARCHAR2(1 BYTE),
  REPAIR_PROGRAM                  NUMBER,
  SUBCONTRACTING_COMPONENT        NUMBER,
  OUTSOURCED_ASSEMBLY             NUMBER,
  SOURCE_SYSTEM_ID                NUMBER,
  SOURCE_SYSTEM_REFERENCE         VARCHAR2(255 BYTE),
  SOURCE_SYSTEM_REFERENCE_DESC    VARCHAR2(240 BYTE),
  GLOBAL_TRADE_ITEM_NUMBER        VARCHAR2(14 BYTE),
  CONFIRM_STATUS                  VARCHAR2(3 BYTE),
  CHANGE_ID                       NUMBER,
  CHANGE_LINE_ID                  NUMBER,
  ITEM_CATALOG_GROUP_NAME         VARCHAR2(820 BYTE),
  REVISION_IMPORT_POLICY          VARCHAR2(30 BYTE),
  GTIN_DESCRIPTION                VARCHAR2(240 BYTE),
  INTERFACE_TABLE_UNIQUE_ID       NUMBER,
  GDSN_OUTBOUND_ENABLED_FLAG      VARCHAR2(1 BYTE),
  TRADE_ITEM_DESCRIPTOR           VARCHAR2(35 BYTE),
  STYLE_ITEM_ID                   NUMBER,
  STYLE_ITEM_FLAG                 VARCHAR2(1 BYTE),
  STYLE_ITEM_NUMBER               VARCHAR2(700 BYTE),
  COPY_REVISION_ID                NUMBER,
  BUNDLE_ID                       NUMBER,
  MESSAGE_TIMESTAMP               DATE,
  MESSAGE_ID                      NUMBER,
  OPERATION                       VARCHAR2(80 BYTE),
  TOP_ITEM_FLAG                   VARCHAR2(1 BYTE),
  GPC_CODE                        VARCHAR2(8 BYTE),
  GLOBAL_ATTRIBUTE11              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE12              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE13              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE14              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE15              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE16              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE17              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE18              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE19              VARCHAR2(150 BYTE),
  GLOBAL_ATTRIBUTE20              VARCHAR2(150 BYTE),
  ERROR_MESSAGE                   VARCHAR2(3000),
  ERROR_FLAG                      CHAR(1)
);

SELECT * FROM ITEM_STG_TBL;

DECLARE
CURSOR c_items_stg IS
SELECT * from ITEM_STG_TBL;
V_COUNT NUMBER;
V_organization_id NUMBER;
V_template_id  NUMBER;
v_error_flag VARCHAR2(1);
BEGIN
FOR crec IN c_items_stg LOOP
v_error_flag := 'N';
-- Validations
-- Item Validation
SELECT COUNT(*)
  INTO V_COUNT
  FROM MTL_SYSTEM_ITEMS_B
 WHERE SEGMENT1 = crec.item_number;
 --Condition for Validating the Staging Table
 IF V_COUNT = 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'V',
        validation_message = 'Valid Item'
   WHERE item_number = crec.item_number;
 ELSIF V_COUNT > 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'E',
        validation_message = 'Item Already Exixt'
   WHERE item_number = crec.item_number;
   v_error_flag := 'Y';
 END IF;

 -- Organization Validation and Get Organization ID
 BEGIN
SELECT organization_id
  INTO V_organization_id
  FROM ORG_ORGANIZATION_DEFINITIONS
 WHERE organization_name = crec.organization_name;
 EXCEPTION WHEN OTHERS THEN
 V_organization_id := 0;
 END;
 --Condition for Validating the Staging Table
 IF V_organization_id > 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'V',
        validation_message = 'Valid Organization'
   WHERE item_number = crec.item_number;
 ELSIF V_organization_id = 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'E',
        validation_message = 'Organization Does not exist'
   WHERE item_number = crec.item_number;
   v_error_flag := 'Y';
 END IF;


 -- Template Validation and Get Organization ID
BEGIN
SELECT template_id
  INTO V_template_id
  FROM MTL_ITEM_TEMPLATES
 WHERE template_name = crec.template_name;
 EXCEPTION WHEN OTHERS THEN
 V_template_id := 0;
 END;
 --Condition for Validating the Staging Table
 IF V_template_id > 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'V',
        validation_message = 'Valid Template'
   WHERE item_number = crec.item_number;
 ELSIF V_template_id = 0 THEN
 UPDATE ITEM_STG_TBL
    SET validation_flag = 'E',
        validation_message = 'Template Does not exist'
   WHERE item_number = crec.item_number;
   v_error_flag := 'Y';
 END IF;

 IF v_error_flag = 'N' THEN
 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
 (SEGMENT1,
 DESCRIPTION,
 ORGANIZATION_ID,
 TEMPLATE_ID,
 TRANSACTION_TYPE,
 PROCESS_FLAG,
 CREATION_DATE,
 CREATED_BY,
 LAST_UPDATE_DATE,
 LAST_UPDATED_BY
 )
 VALUES
 (crec.item_number,
  crec.item_description,
  V_organization_id,
  v_template_id,
  'CREATE',
  1,
  sysdate,
  1318,
  sysdate,
  1318
 );
 END IF;
END LOOP;
END;

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE;

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE MSIBI WHERE MSIBI.SEGMENT1 LIKE '%GKR%';

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE MSIBI WHERE MSIBI.SEGMENT1 LIKE 'GKR%';

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE MSIBI WHERE TRUNC(MSIBI.CREATION_DATE)=TRUNC(SYSDATE);

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE MSIBI WHERE MSIBI.SEGMENT1 LIKE'GK%';

DELETE FROM ITEM_STG_TBL;

DROP TABLE ITEM_STG_TBL;

CREATE TABLE ITEM_STG_TBL
( ITEM_NUMBER VARCHAR2(40),
  ITEM_DESCRIPTION VARCHAR2(240),
  ORGANIZATION_NAME VARCHAR2(240),
  TEMPLATE_NAME VARCHAR2(100),
  CREATION_DATE  DATE,
  CREATED_BY  NUMBER,
  LAST_UPDATE_DATE DATE,
  LAST_UPDATED_BY NUMBER,
  TRANSACTION_TYPE VARCHAR2(40),
  PROCESS_FLAG NUMBER,
  VALIDATION_FLAG VARCHAR2(2),
  VALIDATION_MESSAGE VARCHAR2(2000),
  ERROR_FLAG         VARCHAR2(2)
);

SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE MSIBI WHERE MSIBI.SEGMENT1 LIKE'G%';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID;

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOHA.ORDER_NUMBER='66405';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOHA.ORDER_NUMBER='66405'
AND OOLA.ORDERED_ITEM LIKE 'AS54%';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOHA.ORDER_NUMBER='66405'
--AND OOLA.ORDERED_ITEM LIKE 'AS54%';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66405';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS",WDD.RELEASED_STATUS "RS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66405';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOHA.ORDER_NUMBER='66406';

ALTER table SR_ITEM_IMPORT_STAGE ADD INVENTORY_ITEM_ID NUMBER(12);

ALTER table SR_ITEM_IMPORT_STAGE DROP COLUMN INVENTORY_ITEM_ID;

DELETE  SR_ITEM_IMPORT_STAGE;

-- Creating Package Specification
---------------------------------
CREATE OR REPLACE Package SR_ITEM_IMPORT_PKG IS

PROCEDURE item_import (pErrBuf        OUT VARCHAR2
                          ,pRetCode       OUT NUMBER
                          ,pActionType    IN  VARCHAR2
                );

END SR_ITEM_IMPORT_PKG;
/

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE OOHA.ORDER_NUMBER='66392'
AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID(+)
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID(+)
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID(+)
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID(+)=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID(+)
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID(+)
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND ROWNUM<10;

SELECT
OOHA.ORG_ID,OOHA.ORDER_NUMBER,WDD.ORGANIZATION_ID,WDD.ORIGINAL_SUBINVENTORY
,RSA.SALESREP_ID,QLH.LIST_HEADER_ID,OOHA.PRICE_LIST_ID,OOLA.LINE_ID,WND.STATUS_CODE,OOHA.FLOW_STATUS_CODE
,OOLA.FLOW_STATUS_CODE,WDD.RELEASED_STATUS,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_SALESREPS_ALL RSA,
QP_LIST_HEADERS QLH,
AR_CUSTOMERS AC,
OE_TRANSACTION_TYPES_TL OTTT,
MTL_SYSTEM_ITEMS_B MSIB
WHERE --OOHA.ORDER_NUMBER='66392'
--AND
OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID(+)
AND QLH.LIST_HEADER_ID(+)=OOHA.PRICE_LIST_ID
AND AC.CUSTOMER_ID=OOHA.SOLD_FROM_ORG_ID
AND RSA.SALESREP_ID=OOHA.SALESREP_ID(+)
AND MSIB.INVENTORY_ITEM_ID=OOLA.SHIP_FROM_ORG_ID(+)
AND MSIB.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND ROWNUM<10;

SELECT PH.SEGMENT1 FROM PO_HEADERS_ALL PH;

SELECT PH.SEGMENT1 FROM PO_HEADERS_ALL PH where trunc(creation_date)=trunc(sysdate);

SELECT DISTINCT hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
          WHERE hp.party_id = hps.party_id(+)
            AND hp.party_id = hca.party_id(+)
            AND hcasa1.party_site_id(+) = hps.party_site_id
            AND hcasa2.party_site_id(+) = hps.party_site_id
            AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
            AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
            AND hcsua1.site_use_code(+) = 'bill_to'
            AND hcsua2.site_use_code(+) = 'ship_to'
            AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hps.location_id = hl.location_id
            AND hl.country = ftt.territory_code
            AND ftt.LANGUAGE = USERENV ('lang')
       ORDER BY customer_number;

SELECT PH.SEGMENT1
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID;

SELECT PH.SEGMENT1
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+);

SELECT PH.SEGMENT1
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD';

SELECT DISTINCT TYPE_LOOKUP_CODE FROM PO_HEADERS_ALL;

SELECT DISTINCT PRICE_DISCOUNT FROM PO_LINE_LOCATIONS_ALL;

SELECT PH.SEGMENT1
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT PH.SEGMENT1
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
--WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
WHERE PH.SEGMENT1=6035
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT *
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
--WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
WHERE PH.SEGMENT1=6035
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT *
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
--WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
WHERE PH.SEGMENT1='6035'
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT *
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES_ALL PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
--WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
WHERE PH.SEGMENT1='6035'
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT *
FROM PO_LOOKUP_CODES FOB,
PO_LOOKUP_CODES FOBT,
PO_VENDORS vn,
PO_VENDOR_SITES_ALL PVS,
PER_PEOPLE_F PPF,
AP_TERMS TERMS,
PO_HEADERS_ALL PH,
FND_TERRITORIES_TL SUPPLIER_TERRITORY,
FND_TERRITORIES_TL SHIPTO_TERRITORY,
WSH_CARRIERS_V F,
HR_LOCATIONS HL
--WHERE PH.SEGMENT1 BETWEEN NVL(:PO_NUM_FROM,PH.SEGMENT1) AND NVL(:PO_NUM_TO,PH.SEGMENT1)
WHERE PH.SEGMENT1='6035'
AND vn.VENDOR_ID=PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID=PH.VENDOR_SITE_ID
AND PH.AGENT_ID=PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND PH.TERMS_ID=TERMS.TERM_ID(+)
AND PH.TYPE_LOOKUP_CODE='STANDARD'
AND PH.APPROVED_FLAG='Y'
AND FOB.LOOKUP_CODE(+)=PH.FOB_LOOKUP_CODE
AND FOB.LOOKUP_TYPE(+)='FOB'
AND FOBT.LOOKUP_CODE(+)=PH.FREIGHT_TERMS_LOOKUP_CODE
AND FOBT.LOOKUP_TYPE(+)='FREIGHT TERMS'
AND F.FREIGHT_CODE=PH.SHIP_VIA_LOOKUP_CODE
AND PVS.COUNTRY=SUPPLIER_TERRITORY.TERRITORY_CODE(+)
AND HL.COUNTRY=SHIPTO_TERRITORY.TERRITORY_CODE(+)
AND PVS.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND  SUPPLIER_TERRITORY.LANGUAGE(+)=USERENV('LANG')
AND SHIPTO_TERRITORY.LANGUAGE(+)=USERENV('LANG')
ORDER BY PH.SEGMENT1;

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id;

select
       h.order_number,
       org.name customer_name,
       h.ordered_date order_date,
       ot.name order_type,
       s.name sales_rep,
       l.line_id,
       l.line_number,
       l.inventory_item_id,
       si.segment1,
       l.ordered_quantity,
       l.unit_selling_price,
       nvl(l.ordered_quantity,0) * nvl(l.unit_selling_price,0) amount,
       h.transactional_curr_code currency_code
from ra_salesreps s,
        oe_transaction_types_tl ot,
        oe_sold_to_orgs_v org,
       mtl_system_items_vl si,
       oe_order_lines_all l,
       oe_order_headers_all h
 where h.order_number= 14463
   and h.org_id  = 204
   and l.header_id = h.header_id
   and h.sold_to_org_id = org.organization_id
   and  (h.cancelled_flag is null or h.cancelled_flag = 'N')
   and  h.open_flag='Y'
  and l.open_flag = 'Y'
  and l.service_reference_line_id is null
  and l.inventory_item_id = si.inventory_item_id
  and nvl(si.organization_id,0) = 204 --Item master orgn
  and h.order_type_id = ot.transaction_type_id
  and h.salesrep_id=s.salesrep_id
  and h.org_id=s.org_id
order by l.line_id;

SELECT * FROM GL_SETS_OF_BOOKS;

SELECT * FROM PO_HEADERS_ALL WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE);

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE);

SELECT *
FROM PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PER_PEOPLE_F PAPF,
GL_SETS_OF_BOOKS SOB,
--FINACIALS_SYSTEM_PARAMETERS FSP,
PO_LINE_TYPES PLT,
PO_UN_NUMBERS POUN,
HR_ORGANIZATION_UNITS HOU,
HR_LOCATIONS_ALL_TL HRL1,
PO_DISTRIBUTIONS_ALL PRD,
GL_CODE_COMBINATIONS_KFV GCK
WHERE PRH.SEGMENT1='14310'
AND PAPF.PERSON_ID=PRH.PREPARER_ID
AND PRL.REQUISITION_HEADER_ID=PRH.REQUISITION_HEADER_ID
AND PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND POUN.HAZARD_CLASS_ID(+)=PRL.HAZARD_CLASS_ID
AND HOU.ORGANIZATION_ID=PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID(+)=PRL.DELIVER_TO_LOCATION_ID
AND PRD.CODE_COMBINATION_ID=GCK.CODE_COMBINATION_ID;

SELECT PRH.SEGMENT1,
PRH.TYPE_LOOKUP_CODE,
PAPF.FULL_NAME,
PRH.AUTHORIZATION_STATUS,
PRL.QUANTITY*PRL.UNIT_PRICE TOTAL_AMOUNT,
SOB.CURRENCY_CODE,
PRL.LINE_NUM,
PRL.LINE_TYPE_ID,
PLT.LINE_TYPE,
PRL.ITEM_ID,
PRL.ITEM_DESCRIPTION,
PRL.UNIT_MEAS_LOOKUP_CODE,
PRL.QUANTITY,
PRL.UNIT_PRICE,
PRL.AMOUNT,
PAPF.FULL_NAME,
HOU.NAME INVENTORY_ORG,
HOU.NAME OPERATING_UNIT,
GCK.CONCATENATED_SEGMENTS
FROM PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PER_PEOPLE_F PAPF,
GL_SETS_OF_BOOKS SOB,
--FINACIALS_SYSTEM_PARAMETERS FSP,
PO_LINE_TYPES PLT,
PO_UN_NUMBERS POUN,
HR_ORGANIZATION_UNITS HOU,
HR_LOCATIONS_ALL_TL HRL1,
PO_DISTRIBUTIONS_ALL PRD,
GL_CODE_COMBINATIONS_KFV GCK
WHERE PRH.SEGMENT1='14310'
AND PAPF.PERSON_ID=PRH.PREPARER_ID
AND PRL.REQUISITION_HEADER_ID=PRH.REQUISITION_HEADER_ID
AND PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND POUN.HAZARD_CLASS_ID(+)=PRL.HAZARD_CLASS_ID
AND HOU.ORGANIZATION_ID=PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID(+)=PRL.DELIVER_TO_LOCATION_ID
AND PRD.CODE_COMBINATION_ID=GCK.CODE_COMBINATION_ID;

SELECT DISTINCT PRH.SEGMENT1,
PRH.TYPE_LOOKUP_CODE,
PAPF.FULL_NAME,
PRH.AUTHORIZATION_STATUS,
PRL.QUANTITY*PRL.UNIT_PRICE TOTAL_AMOUNT,
SOB.CURRENCY_CODE,
PRL.LINE_NUM,
PRL.LINE_TYPE_ID,
PLT.LINE_TYPE,
PRL.ITEM_ID,
PRL.ITEM_DESCRIPTION,
PRL.UNIT_MEAS_LOOKUP_CODE,
PRL.QUANTITY,
PRL.UNIT_PRICE,
PRL.AMOUNT,
PAPF.FULL_NAME,
HOU.NAME INVENTORY_ORG,
HOU.NAME OPERATING_UNIT,
GCK.CONCATENATED_SEGMENTS
FROM PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PER_PEOPLE_F PAPF,
GL_SETS_OF_BOOKS SOB,
--FINACIALS_SYSTEM_PARAMETERS FSP,
PO_LINE_TYPES PLT,
PO_UN_NUMBERS POUN,
HR_ORGANIZATION_UNITS HOU,
HR_LOCATIONS_ALL_TL HRL1,
PO_DISTRIBUTIONS_ALL PRD,
GL_CODE_COMBINATIONS_KFV GCK
WHERE PRH.SEGMENT1='14310'
AND PAPF.PERSON_ID=PRH.PREPARER_ID
AND PRL.REQUISITION_HEADER_ID=PRH.REQUISITION_HEADER_ID
AND PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND POUN.HAZARD_CLASS_ID(+)=PRL.HAZARD_CLASS_ID
AND HOU.ORGANIZATION_ID=PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID(+)=PRL.DELIVER_TO_LOCATION_ID
AND PRD.CODE_COMBINATION_ID=GCK.CODE_COMBINATION_ID;

SELECT  PRH.SEGMENT1,
PRH.TYPE_LOOKUP_CODE,
PAPF.FULL_NAME,
PRH.AUTHORIZATION_STATUS,
PRL.QUANTITY*PRL.UNIT_PRICE TOTAL_AMOUNT,
SOB.CURRENCY_CODE,
PRL.LINE_NUM,
PRL.LINE_TYPE_ID,
PLT.LINE_TYPE,
PRL.ITEM_ID,
PRL.ITEM_DESCRIPTION,
PRL.UNIT_MEAS_LOOKUP_CODE,
PRL.QUANTITY,
PRL.UNIT_PRICE,
PRL.AMOUNT,
PAPF.FULL_NAME,
HOU.NAME INVENTORY_ORG,
HOU.NAME OPERATING_UNIT,
GCK.CONCATENATED_SEGMENTS
FROM PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PER_PEOPLE_F PAPF,
GL_SETS_OF_BOOKS SOB,
--FINACIALS_SYSTEM_PARAMETERS FSP,
PO_LINE_TYPES PLT,
PO_UN_NUMBERS POUN,
HR_ORGANIZATION_UNITS HOU,
HR_LOCATIONS_ALL_TL HRL1,
PO_DISTRIBUTIONS_ALL PRD,
GL_CODE_COMBINATIONS_KFV GCK
WHERE PRH.SEGMENT1='14310'
AND PAPF.PERSON_ID=PRH.PREPARER_ID
AND PRL.REQUISITION_HEADER_ID=PRH.REQUISITION_HEADER_ID
AND PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND POUN.HAZARD_CLASS_ID(+)=PRL.HAZARD_CLASS_ID
AND HOU.ORGANIZATION_ID=PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID(+)=PRL.DELIVER_TO_LOCATION_ID
AND PRD.CODE_COMBINATION_ID=GCK.CODE_COMBINATION_ID;

select * from ra_addresses_all;

SELECT  /*+ leading(ool) */   --Added by Harish 11-Feb-09 suggestion from Rakesh tikku
DISTINCT
  wdd.delivery_detail_id line_d ,
  SUBSTR ( ooh.cust_po_number , 1 , 20 ) po# ,
  --Changed on 18-Nov-06 as per BSR-A0006B28S??
  --SUBSTR ( c.customer_name , 1 , 25 ) customer ,
  rac.customer_name customer ,
  ooh.booked_date order_date ,
  ooh.order_number sales_order ,
  msi.segment1 item ,
  msi.description description ,
  NVL ( ool.pricing_quantity , 0 ) order_qty ,
  NVL ( wdd.shipped_quantity , 0 ) shipped_qty ,
  NVL ( wdd.shipped_quantity , 0 ) * NVL ( ool.unit_selling_price , 0 ) extended_price ,
  NVL ( ool.unit_selling_price , 0 ) selling_price ,
  NVL ( ool.unit_list_price , 0 ) list_price,                                                                                                     -- added by Ganga Ram on 16-Apr-09 as per CO64306
  --LDMFCUBL_PKG.GET_CUST_ITEM_PALVALIDATE(ool.org_id ,NVL(ool.ship_from_org_id,116) ,ool.sold_to_org_id ,ool.inventory_item_id) pallet_validation, -- Added by Ganga Ram on 16-Apr-09 as per CO64306
  ool.attribute17 enforce_pallet,                                                                                                                 -- Added by Ganga Ram on 16-Apr-09 as per CO64306
  NVL(wdd.requested_quantity, ool.ordered_quantity) balquantity,                                                                                  --Added by Ganga Ram on 16-Apr-09 as per CO64306
  (SELECT rctl.customer_trx_id
  FROM RA_CUSTOMER_TRX_LINES rctl
  WHERE rctl.interface_line_attribute3                    = wnd.name
  AND rctl.inventory_item_id                              = ool.inventory_item_id
  AND rctl.interface_line_attribute6                      = TO_CHAR ( ool.line_id )
  AND NVL ( rctl.interface_line_context , 'ORDER ENTRY' ) = 'ORDER ENTRY'
  AND rctl.sales_order                                    = TO_CHAR ( ooh.order_number )
  ) trx_id ,
  (SELECT wc.freight_code
  FROM WSH_CARRIERS wc ,
    WSH_CARRIER_SERVICES wcs --  Change view to table and supress id fields for tunning on 11-Dec-06
  WHERE wcs.carrier_id+0   = wc.carrier_id +0
  AND wcs.ship_method_code = wnd.ship_method_code
  ) carrier ,
  wnd.NAME delivery ,
  wnd.initial_pickup_date shipped_date ,
  ool.request_date request_date ,
  --Changed on 1-Jan-07 as per BSR-A0006B28S??
  --wdi.sequence_number bill_of_lading ,
  wdd.tracking_number bill_of_lading ,
  wdd.date_scheduled ,
  wdd.subinventory ,
  wnd.waybill ,
  wdd.FOB_code ,
  --wnd.freight_terms_code ,
  fr_mean.meaning freight_terms_code,
  wnd.gross_weight ,
  wnd.attribute6 pro# ,
  wnd.attribute5 cartons ,
  wnd.attribute1 freight_cost ,
  rac.customer_id customer_id ,
  msi.inventory_item_id inventory_item_id ,
  raa.state ,
 -- LOGI_COMMON_PKG.GET_LINE_NUMBER ( ool.line_id ) line_number ,
  msi.organization_id ,
  (SELECT ffv.description
  FROM FND_FLEX_VALUES_VL ffv ,
    FND_FLEX_VALUE_SETS fvs
  WHERE ffv.flex_value        = rac.attribute4
  AND fvs.flex_value_set_name = 'LOGI_ACCOUNT_SPECIALIST'
  AND ffv.flex_value_set_id   = fvs.flex_value_set_id
  ) acc_specialist ,
(SELECT ffv.description
  FROM FND_FLEX_VALUES_VL ffv ,
    FND_FLEX_VALUE_SETS fvs
  WHERE ffv.flex_value        = rt.segment3
  AND fvs.flex_value_set_name = 'LOGI_SUBREGION_KFF'
  AND ffv.flex_value_set_id   = fvs.flex_value_set_id
  and ffv.enabled_flag='Y') sub_region,       -- Added by Murali on 07-jan-2010 for CO86658
  ool.schedule_ship_date ,
  msi.item_catalog_group_id ,
  rt.segment1 st_country ,
  rt.segment2 area,
  ool.order_quantity_uom,
  ooh.header_id,
  ool.line_id,
  wda.delivery_id,
  ooh.transactional_curr_code currency,
  --LOGI_COMMON_PKG.LINT_GET_CUST_ITEM(ooh.sold_to_org_id ,msi.inventory_item_id ,ool.invoice_to_org_id ,ool.ship_to_org_id,0) sku_number ,
  rsus.location ship_to_location,
  ool.deliver_to_org_id,
  flv.description sub_sales_channel,
  raa.address1
  ||'  '
  ||raa.address2
  ||'  '
  ||raa.address3
  ||'  '
  ||raa.address4 "Address",
  raa.city,
  raa.postal_code,
  raa.country address_country,
  wdd.fob_code Incoterm,
  ood.organization_name warehouse,
  wnd.attribute5 shipped_carton_count,
  wt.attribute4 Delivered_Carton,
  wnd.creation_date Pick_release_date,
  wt.attribute1 actual_carrier_pickup_date,
  wt.attribute2 est_delivery_date,
  wnd.attribute2 cust_appt_req_date,
  wnd.attribute3 cust_appt_confirm_date,
  wnd.attribute4 sch_del_appt_date,
  wt.attribute3 trailer_drop_date,
  wt.attribute7 actual_delivery_date,
  wt.attribute6 cust_rej_shipment_date,
 -- lint_pick_release_outbound_pkg.lint_date_qualifier_data(ooh.header_id,ooh.org_id) must_arrive_date,
wdl.pod_by pod_signed_by,
wt.attribute5 delivery_remark
FROM oe_order_headers_all ooh,
  oe_order_lines_all ool,
  wsh_delivery_details wdd,
  WSH_DELIVERY_ASSIGNMENTS wda,
  wsh_new_deliveries wnd,
  WSH_PICKING_BATCHES wpb ,
  wsh_delivery_legs wdl,
  wsh_trip_stops wts,
  wsh_trips wt,
  oe_lookups fr_mean,
--ra_customer_trx_lines rctl
  mtl_system_items msi,
  RA_SITE_USES_all rsus,
  ra_site_uses_all rsui,
  ra_addresses_all raa,
  ra_customers rac,
  JTF_RS_SALESREPS jrs,
  ra_territories rt,
  gl_code_combinations gcc,
  fnd_flex_value_sets ffv,
  fnd_flex_values_vl flv,
org_organization_definitions ood
WHERE ooh.header_id         = ool.header_id
--AND ooh.order_number        = 11495874
AND ool.line_category_code  = 'ORDER'
AND ooh.header_id           = wdd.source_header_id
AND ool.line_id             = wdd.source_line_id
AND wdd.source_code         = 'OE'
AND wdd.shipped_quantity    > 0
AND wdd.delivery_detail_id  = wda.delivery_detail_id
AND wnd.delivery_id ( + )   = wda.delivery_id
AND wdd.batch_id            = wpb.batch_id
AND wnd.delivery_id         = wdl.delivery_id
AND wts.stop_id             = wdl.drop_off_stop_id
AND wt.trip_id              = wts.trip_id
AND wnd.freight_terms_code = fr_mean.lookup_code(+)
AND fr_mean.lookup_type(+) = 'FREIGHT_TERMS'
/*Uncomment the lines below and the table rctl in the from clause above
to fix the issue with the invoice number parameter
ANd rctl.sales_order = to_char(ooh.order_number)
AND rctl.interface_line_attribute6 = to_char(ool.line_id)
AND NVL ( rctl.interface_line_context , 'ORDER ENTRY' ) = 'ORDER ENTRY'*/
AND msi.inventory_item_id   = wdd.inventory_item_id
AND msi.organization_id     = wdd.organization_id
AND ooh.ship_to_org_id      = rsus.site_use_id
AND ooh.invoice_to_org_id   = rsui.site_use_id
AND raa.address_id          = rsus.address_id
AND rac.customer_id         = raa.customer_id
AND jrs.salesrep_id         = ooh.salesrep_id
AND jrs.org_id = ooh.org_id
AND rsui.territory_id ( + ) = rt.territory_id
and gcc.code_combination_id = rsui.gl_id_rev
and gcc.segment5 = flv.flex_value
and ffv.flex_value_set_id = flv.flex_value_set_id
and ffv.flex_value_set_name = 'LOGI_GL_SALESCHANNEL'
and ool.ship_from_org_id = ood.organization_id
and flv.flex_value = nvl(:p_sub_sales_channel, flv.flex_value)
--AND ool.actual_shipment_date BETWEEN :p_ship_date_low AND TO_DATE ( :p_ship_date_high ) + 0.99999
AND 1 = 1
ORDER BY wnd.name;

SELECT  OOHA.ORDER_NUMBER,OOHA.HEADER_ID,OOHA.FLOW_STATUS_CODE
FROM OE_ORDER_HEADERS_ALL OOHA
WHERE OOHA.ORDER_NUMBER='66407';

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOHA.ORDER_NUMBER='66407';

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND  OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66407';

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND  OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66407';

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED,WDA.DELIVERY_ID,WDA.DELIVERY_DETAIL_ID,WND.DELIVERY_ID,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND  OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66407';

SELECT PRHA.SEGMENT1 "REQ_NO",PRHA.AUTHORIZATION_STATUS
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA
--PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
--AND   PRLA.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
AND PRHA.SEGMENT1='6037';

SELECT PHA.SEGMENT1 "PO_NO",PHA.AUTHORIZATION_STATUS,PLA.LINE_NUM,PHA.PO_HEADER_ID,PLLA.PO_LINE_ID,PLLA.LINE_LOCATION_ID
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PHA.SEGMENT1='6037';

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND  OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66408';

SELECT OOHA.CUST_PO_NUMBER,OOHA.ORDERED_DATE,OOHA.INVOICE_TO_ORG_ID,OOHA.SHIP_FROM_ORG_ID,OOHA.SHIP_TO_ORG_ID,OOHA.SALESREP_ID,
OOHA.BOOKED_FLAG,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY,OOLA.ORDER_QUANTITY_UOM,OOLA.SCHEDULE_SHIP_DATE,OOLA.SHIPPABLE_FLAG,
OOHA.FLOW_STATUS_CODE "HS",OOLA.FLOW_STATUS_CODE "LS"
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.LINE_ID
AND OOHA.ORDER_NUMBER='66409';

CREATE OR REPLACE PACKAGE XX_WF_ADD IS
PROCEDURE  XX_WF_ADD_P(ITEMTYPE IN VARCHAR2
,ITEMKEY IN VARCHAR2
,ACTID IN NUMBER
,FUNCMODE IN VARCHAR2
,RESULTOUT IN OUT VARCHAR2 );
END XX_WF_ADD;

CREATE OR REPLACE PACKAGE BODY XX_WF_ADD IS
PROCEDURE  XX_WF_ADD_P (ITEMTYPE IN VARCHAR2
,ITEMKEY IN VARCHAR2
,ACTID IN NUMBER
,FUNCMODE IN VARCHAR2
,RESULTOUT IN OUT VARCHAR2 )
AS
L_A  NUMBER:=0;
L_B  NUMBER:=0;
L_SUM  NUMBER:=0;
BEGIN
L_A:=WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE,ITEMKEY,'A');
L_B:=WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE,ITEMKEY,'B');
L_SUM:=TO_NUMBER(L_A)+TO_NUMBER(L_B);
WF_ENGINE.SETITEMATTRNUMBER(ITEMTYPE,ITEMKEY,'C',TO_NUMBER(L_SUM));
COMMIT;
DBMS_OUTPUT.PUT_LINE('THE VARIABLES ARE:'||L_A||','||L_B||','||L_SUM);
END;
END XX_WF_ADD;

SELECT TEXT FROM USER_SOURCE WHERE NAME LIKE 'WF%';

SELECT TEXT FROM USER_SOURCE WHERE NAME LIKE 'WF_ADD%';

SELECT TEXT FROM USER_SOURCE WHERE NAME LIKE 'WF_A%';

Create or replace package wf_add_pkg as
procedure wf_add_proc(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funcmode in varchar2
,resultout in out varchar2);
END wf_add_pkg;

Create or replace package body wf_add_pkg as
procedure wf_add_proc(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funcmode in varchar2
,resultout in out varchar2) as
l_a number :=0;
l_b number :=0;
l_sum number :=0;
begin
l_a :=wf_engine.GetItemAttrNumber(Itemtype,ItemKey,'A');
l_b :=wf_engine.GetItemAttrNumber(Itemtype,ItemKey,'B');
l_sum :=to_number(l_a)+to_number(l_b);
wf_engine.SetItemAttrNUmber(Itemtype,ItemKey,'C',to_number(l_sum));
commit;
dbms_output.put_line('The Var are :'||l_a||','||l_b||','||l_sum);
end;
end wf_add_pkg ;