Rishi Technologies: January 2017

Tuesday, January 31, 2017

Query - Sales Order Line Closed, Shipped, not Interfaced to Inventory

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10) "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0) "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0) "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N') "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS') "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS') "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS') "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
     -- AND ORD.HEADER_ID                  = 194000
     -- AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.INV_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     AND LIN.FLOW_STATUS_CODE = 'CLOSED'
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
, nvl(LIN.shipment_number, -1)
, nvl(LIN.option_number, -1)
, nvl(LIN.service_number, -1);

***************************************************************************
For Online Training on
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Query - Sales Order Line Closed, Shipped, not Interfaced to OM

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10) "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0) "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0) "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N') "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS') "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS') "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS') "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
    -- AND ORD.HEADER_ID                  = 194000
     -- AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.OE_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     AND LIN.FLOW_STATUS_CODE = 'CLOSED'
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
, nvl(LIN.shipment_number, -1)
, nvl(LIN.option_number, -1)
, nvl(LIN.service_number, -1);

Query - Sales Order Line has been Shipped but not Interfaced to OM

select
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10) "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0) "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0) "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N') "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS') "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS') "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS') "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
    -- AND ORD.HEADER_ID                  = 194000
    -- AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.OE_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
, nvl(LIN.shipment_number, -1)
, nvl(LIN.option_number, -1)
, nvl(LIN.service_number, -1);

***************************************************************
For Online And Classroom Training on
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Query To Find Sales Order Line Ordered Qty does not equal Shipped Qty

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10) "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0) "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0) "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N') "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS') "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS') "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS') "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN
where 1=1
     --ORD.HEADER_ID                  = 194000
     --and LIN.HEADER_ID              = 194000
     AND LIN.SHIPPED_QUANTITY IS NOT NULL
     AND LIN.ORDERED_QUANTITY != LIN.SHIPPED_QUANTITY
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
, nvl(LIN.shipment_number, -1)
, nvl(LIN.option_number, -1)
, nvl(LIN.service_number, -1);

***************************************************************
For Online And Classroom Training on
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Sunday, January 8, 2017

R12 Internal Sales Order Cycle 1

R12 Internal Sales Order Cycle 1

For Online Training on
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Oracle Fusio SCM Cloud Training