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

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

IB Error -- Stale Data

Issue -- While Transferring the Item Instance from one account to another account , we received below error.

Error: Stale Data

The requested page contains stale data. This error could have been caused through the use of the browser's navigation buttons (the browser Back button, for example).

Cause:
The view object OwnerPartyAM.LocationRN542_LocationRN_oracle_apps_csi_instance_location_server_LocationAM.LocationPartySiteVO contained no record. The displayed records may have been deleted, or the current record for the view object may not have been properly initialized.

Solution -- Please apply the patch -- 19821318:R12.CSI.B  and test the issue in any teset instance and then move to PROD.

***************************************************************
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

Generate COGS Recognition Events -- Error in CST_RevenueCogsMatch_PVT . Match_RevenueCOGS ( 50 )

Issue -- While Running "Generate COGS Recognition Events" , Program error outwith below error.

program_exception EXCEPTION IN Create_CogsRecognitionEvents
PROGRAM_EXCEPTION IN Match_RevenueCOGS at (50):
Failed to insert COGS Recognition Events into MMT due to a failed validation during bulk insertion.
Invalid item
Inventory item ID is invalid or does not have the flags enabled correctly to be processed by material processor
Error in CST_RevenueCogsMatch_PVT . Match_RevenueCOGS ( 50 )


Reason and solution.-- Few items are not defined as inventory items. ( In Our case Initially we defined item as Inventory item and created Sales Order and later user disabled the inventory flag. Due to this while running COGS Program we are getting below error ) . To find out the item details, Use below Query and enable  INVENTORY_ITEM_FLAG as 'Y" and then run "Generate COGS Recognition Events" Program.

SELECT organization_id,
MSI.SEGMENT1 ITEM_NUMBER,
MSI.DESCRIPTION,
MSI.INVENTORY_ITEM_FLAG
FROM apps.MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_FLAG = 'N'
AND MSI.INVENTORY_ITEM_ID IN (SELECT CRTT.INVENTORY_ITEM_ID FROM apps.CST_REVENUE_COGS_MATCH_LINES CRTT
WHERE CRTT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CRTT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EXISTS (SELECT 1 FROM apps.cst_revenue_recognition_lines crrl
   WHERE crrl.revenue_om_line_id=crtt.revenue_om_line_id
    AND potentially_unmatched_flag IS NOT null)) ORDER BY MSI.SEGMENT1;

For more details, Please refer Metalink Note - 971355.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