R13 -- Approvals In OM

Supplier Site Creation Error -- ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

It seems there is Payble Option setup for that perticulat OU

Please check:

1.  The issue appears to be caused because you do not have this org setup in AP.  Please confirm this with the following:

 select *
 from ap_system_parameters_all
 where org_id = 123;

 If this does not return a row, the problem is an AP org has not been set up.
  
2.  Navigate to Setup > Options > Payables Options and create a record for this operating unit.

Refer to Page 1-49 and following of the Payables Implementation Guide for instructions on how to set up an AP org.  Since you do not have a financials implementation, many of these options may not apply to you, but the record in AP is still required.

3.  Confirm the query in step 1 now returns a row, and then confirm the supplier page no longer gives an ORA-01403 when attempting to create a supplier site.

Oracle Fusion SCM - 2

For Fusion SCM Online Training on
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Oracle Fusion SCM -- 1

For Fusion SCM Online Training on
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Cancel Sales Order By using Script -- Unable to Cancell SO Manually

Issue: While cancelling the sales order manually, we are getting below error.

Solution: Please user attached script to cancel the SO.

While running the script, Please pass SO Number SO Header ID as Parameters.

declare

  l_hdr_id        number := &hdr_id;
  l_hdr_key       varchar2(30) := to_char(l_hdr_id);

  l_ordered_qty   number;
  l_flow_exists   varchar2(1);
  l_all_canceled  varchar2(1);
  l_user_id       number;
  l_resp_id       number;
  l_resp_appl_id  number;
  l_heading       varchar2(1) := 'N';

  cursor line_info
  is
  select
    ol.line_id
  , to_char(ol.line_id) l_lin_key
  from  oe_order_lines_all ol
  where ol.header_id     = l_hdr_id
  and  (open_flag        = 'Y'
   or   cancelled_flag   = 'N'
   or   ordered_quantity > 0
   or   flow_status_code <> 'CANCELLED')
  for update nowait;

  cursor wsh_ifaced
  is
  select
    substr(wdd.source_line_number, 1, 15) line_num
  , substr(wdd.item_description, 1, 30) item_name
  , wdd.shipped_quantity
  , wdd.source_line_id line_id
  from  wsh_delivery_details wdd, oe_order_lines_all oel
  where wdd.inv_interfaced_flag     = 'Y'
  and   nvl(wdd.shipped_quantity,0) > 0
  and   oel.line_id                 = wdd.source_line_id
  and   oel.open_flag               = 'N'
  and   oel.ordered_quantity        = 0
  and   wdd.source_header_id        = l_hdr_id
  and   wdd.source_code             = 'OE'
  and   exists
      ( select 'x'
        from  mtl_material_transactions mmt
        where wdd.delivery_detail_id   = mmt.picking_line_id
        and   mmt.trx_source_line_id   = wdd.source_line_id
        and   mmt.transaction_source_type_id in ( 2,8 ));

begin

  dbms_output.put_line(chr(10)||'Updating Order Number: '||&ord_num);

  for line_rec in line_info loop

    dbms_output.put_line('Updating Line ID: '||line_rec.line_id);

    l_flow_exists := 'Y';

    update oe_order_lines_all
    set    flow_status_code   = 'CANCELLED'
    ,      open_flag          = 'N'
    ,      cancelled_flag     = 'Y'
    ,      ordered_quantity   = 0
    ,      cancelled_quantity = ordered_quantity + nvl(cancelled_quantity, 0)
    ,      last_updated_by    = -9999999
    ,      last_update_date   = sysdate
    where  line_id            = line_rec.line_id;

    Begin
      select number_value
      into   l_user_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'USER_ID';

      select number_value
      into   l_resp_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'RESPONSIBILITY_ID';

      select number_value
      into   l_resp_appl_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'APPLICATION_ID';

      Exception
        When No_Data_Found Then
          l_flow_exists := 'N';
    End;

    if l_flow_exists = 'Y' then

      fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

      wf_engine.handleerror( OE_Globals.G_WFI_LIN
                           , line_rec.l_lin_key
                           , 'CLOSE_LINE'
                           , 'RETRY'
                           , 'CANCEL'
                           );
    end if;

  end loop;

  select decode(count(*), 0, 'Y', 'N')
  into   l_all_canceled
  from   oe_order_lines_all
  where  header_id = l_hdr_id
  and    cancelled_flag = 'N';

  if l_all_canceled = 'Y' then

    l_flow_exists := 'Y';

    update oe_order_headers_all
    set    flow_status_code   = 'CANCELLED'
    ,      open_flag          = 'N'
    ,      cancelled_flag     = 'Y'
    ,      last_updated_by    = -9999999
    ,      last_update_date   = sysdate
    where  header_id          = l_hdr_id
    and   (open_flag         <> 'N'
      or   cancelled_flag    <> 'Y'
      or   flow_status_code  <> 'CANCELLED');

    Begin
      select number_value
      into   l_user_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = l_hdr_key
      and    name      = 'USER_ID';

      select number_value
      into   l_resp_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = l_hdr_key
      and    name      = 'RESPONSIBILITY_ID';
 
      select number_value
      into   l_resp_appl_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = to_char(l_hdr_id)
      and    name      = 'APPLICATION_ID';
 
      Exception
        When No_Data_Found Then
          l_flow_exists := 'N';
    End;

    if l_flow_exists = 'Y' then

      fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

      wf_engine.handleerror( OE_Globals.G_WFI_HDR
                           , l_hdr_key
                           , 'CLOSE_HEADER'
                           , 'RETRY'
                           , 'CANCEL'
                           );
    end if;

  end if;

  for wsh_ifaced_rec in wsh_ifaced loop
    if l_heading = 'N' then
      dbms_output.put_line(chr(10)||'Following Cancelled Lines have already been Interfaced to Inventory.');
      dbms_output.put_line('Onhand Qty must be manually adjusted for these Items and Quantities.'||chr(10));
      dbms_output.put_line('+---------------+------------------------------+---------------+---------------+');
      dbms_output.put_line('|Line No.       |Item Name                     |    Shipped Qty|        Line Id|');
      dbms_output.put_line('+---------------+------------------------------+---------------+---------------+');
      l_heading := 'Y';
    end if;
    dbms_output.put_line('|'||rpad(wsh_ifaced_rec.line_num, 15)||
                         '|'||rpad(wsh_ifaced_rec.item_name, 30)||
                         '|'||lpad(to_char(wsh_ifaced_rec.shipped_quantity), 15)||
                           '|'||lpad(to_char(wsh_ifaced_rec.line_id), 15)||'|');
  end loop;

  update wsh_delivery_assignments
  set    delivery_id               = null
  ,      parent_delivery_detail_id = null
  ,      last_updated_by           = -9999999
  ,      last_update_date          = sysdate
  where  delivery_detail_id        in
        (select wdd.delivery_detail_id
         from   wsh_delivery_details wdd, oe_order_lines_all oel
         where  wdd.source_line_id   = oel.line_id
          and   wdd.source_header_id = l_hdr_id
          and   wdd.source_code      = 'OE'
          and   oel.open_flag        = 'N'
          and   oel.shipped_quantity is null
          and   oel.ordered_quantity = 0
          and   released_status      <> 'D');

  update wsh_delivery_details
  set    released_status        = 'D'
  ,      src_requested_quantity = 0
  ,      requested_quantity     = 0
  ,      shipped_quantity       = 0
  ,      cycle_count_quantity   = 0
  ,      cancelled_quantity     = decode(requested_quantity,0,cancelled_quantity,requested_quantity)
  ,      subinventory           = null
  ,      locator_id             = null
  ,      lot_number             = null
  ,      serial_number          = null
  ,      revision               = null
  ,      ship_set_id            = null
  ,      inv_interfaced_flag    = 'X'
  ,      oe_interfaced_flag     = 'X'
  ,      last_updated_by        = -9999999
  ,      last_update_date       = sysdate
  where source_header_id = l_hdr_id
  and   source_code      = 'OE'
  and   released_status  <> 'D'
  and   exists 
       (select 'x'
        from   oe_order_lines_all oel
        where  oel.header_id        = source_header_id
        and    source_line_id       = oel.line_id
        and    oel.open_flag        = 'N'
        and    oel.shipped_quantity is null
        and    oel.ordered_quantity = 0);

  Exception
    when others then
      rollback;
      dbms_output.put_line(substr(sqlerrm, 1, 240));
end;
/

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

Oracle R12 Purchasing Introduction - 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 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