Sales Order Without Picking And Shipping : Process : Part : 2

Sales Order Without Picking And Shipping : Process : Part : 2

Sales Order Without Picking And Shipping : Setups : Part : 1

Sales Order Without Picking And Shipping : Setups : Part : 1

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

Query to find Details Of OM and Install Base

SELECT okh.contract_number         "Contract Number",cl.ATTRIBUTE6,
       okh.scs_code                "Code",
       ooh.order_number            "Order Number",
       TO_NUMBER(sl.line_number)   "SL Line",
       sl.lse_id                   "SL LSE ID",
       TO_NUMBER(cl.line_number)   "CL Line",
       cl.lse_id                   "CL LSE ID",
       TO_CHAR(cl.id)              "CL ID",
       msi.inventory_item_id,
       msi.segment1                "Item Number",
       cii.serial_number           "Serial Number",
       okh.start_date              "Contract Start Date",
       sl.start_date               "Line Start Date",
       cl.start_date               "Subline Start Date",
       cii.*
  FROM apps.okc_k_headers_all_b   okh,
       apps.okc_k_lines_b         sl,
       apps.okc_k_lines_b         cl,
       apps.okc_k_items           oki,
       apps.okc_k_rel_objs        rel,
       apps.csi_item_instances    cii,
       apps.mtl_system_items_b    msi,
       apps.oe_order_headers_all  ooh
 WHERE 1=1
   AND okh.id                   =  sl.chr_id
   AND cl.cle_id                =  sl.id
   AND sl.cle_id IS NULL
   AND cl.id                    =  oki.cle_id(+)
   AND oki.object1_id1          =  cii.instance_id
   AND cii.inventory_item_id    =  msi.inventory_item_id
   AND okh.inv_organization_id  =  msi.organization_id
   AND rel.chr_id               =  okh.id
   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'
   AND rel.object1_id1          =  ooh.header_id
   AND oki.object1_id2          =  '#'
   AND okh.contract_number = 'US000016348'
  -- AND cii.serial_number = 'FM1407CB00D'
 -- AND TO_CHAR(cl.id) = '97803583007615608399812980983947089161'
   -- AND okh.cust_po_number       =  ooh.cust_po_number
   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id
   /* if subline start date is earlier than line start date */
   -- AND sl.start_date > cl.start_date
   -- AND sl.line_number      = 1
 ORDER BY okh.contract_number,
       TO_NUMBER(sl.line_number),
       TO_NUMBER(cl.line_number);

How to Add Change Reason Codes in Oracle Order Management

1. Go To

Order Management Resp > Setup -> Quick Codes->Order Management

Search with Type as > CANCEL_CODE

Test Reason_1
Test Reason_2

2. Go To Sales Order Line Level and now we can see the above two newly added Reasons. 

COGS and Deferred COGS In Oracle R12

The deferred COGS account is the new feature introduced in Release 12. The key fundamental behind the feature is that the COGS is now directly matched to the Revenue. In simple terms, this means, COGS for an order line will be recognized only if the revenue is recognized for that line making sure that the revenue and COGS are posted in the same month. Matching percentage is also taken care which ensures that revenue and cost are always in sync.

SETUP > Inventory Resp >  Setup > Organization > Parameters > Other Accounts

Here Enter Deferred COGS Account

STEP:
==========

1. Create Sales Order and do Pick Confirm
2. Complete Shipping Confirm Process and run interface trip stop Program.

 Below two Accounts Will hit.

Cr      > Inventory Valuation accoun
Dr      > Deferred COGS Accoun

Go To Inventory responsibility and Review the Accounts in Material Transactions form.

3. Run Workflow Background Process and AutoInvoice Master Program to Create Invoice in AR.

Now AR invoice Got Created, But Revenue not yet Recognized.

Go To AR Resp > Control > Accounting And Search with Sales Order Number.

Revenue Not Yet Recognized.

4. Recognize the revenue in AR.

NAV >  Accounts Receivables Resp > Control > Run "Revenue Recognition" Program.

After running the program, again check for Revenue.

5. Now we have to accept the Revenue.

Click on "Manage Revenue".

6. Run Below Set Of Concurrent Programs.

Record Order Management Transactions: records new sales order transaction activity such as shipments and RMA returns in Oracle Order Management.

Collect Revenue Recognition Information: determines the percentage of recognized or earned revenue related to invoiced sales order shipment lines in Oracle Receivables.

Generate COGS Recognition Events: creates and costs COGS recognition events for new sales order shipments/returns and changes in revenue recognition and credits for invoiced sales order shipment lines.

After Running ABove Programs, Verify "Material Transaction" Form. A non-physical transaction has been generated Transaction Type= COGS Recognition

Click on "Distributions" Button and verify Accounts.

Deffered COGS : Credit
COGS : Debit.

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