SQL-WSH - update OP delivery from oracle

query - WSH - update OP delivery

/*  this is the script to identify open deliveries but all lines are shipped
see tar SR 3-7848981781
*/

select distinct wdd.source_header_number, wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wdd.released_status = 'C'
and wnd.status_code not in ('IT', 'CL')
order by 1;


3) If you want to just close the reported delivery 459098, then you can run the following:
UPDATE wsh_new_deliveries
SET status_code = 'CL',
last_update_date = SYSDATE,
last_updated_by = -1
WHERE delivery_id = 459098
and status_code = 'OP';


/*  this is the script to set delivery status to closed
see tar SR 3-7848981781
*/

UPDATE wsh_new_deliveries
SET    status_code      = 'CL',
       last_update_date = SYSDATE,
       last_updated_by   = -1
WHERE delivery_id IN ( select distinct wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id  = wnd.delivery_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status = 'C'
and wnd.status_code = 'OP');

wsh_picking_batches

SQL - user errors from database

select *
from user_errors
where name like 'XXCNC_PURC_VENDOR_SITE_UPD_PKG%'

SQL- UOM MAX conversion Rate

SELECT * --UOM_CODE maxuom        
           FROM MTL_UOM_CONVERSIONS M1
          WHERE     conversion_rate =
                       (SELECT MAX (CONVERSION_RATE)
                          FROM MTL_UOM_CONVERSIONS M2
                         WHERE M2.INVENTORY_ITEM_ID = M1.INVENTORY_ITEM_ID)
                --AND M1.INVENTORY_ITEM_ID = 39548;

   SELECT primary_uom_code
           FROM mtl_system_items_b msi,
                mtl_parameters mpa
          WHERE inventory_item_id     = p_inventory_item_id
            AND msi.organization_id   = mpa.organization_id
            AND mpa.organization_code = 'MAS';


api - qty conversion
inv_convert.inv_um_conversion(:cp_rpt_uom,:cp_prim_uom,:cp_item_id,x_rate);

SQL- search a db object for a string

select * from ALL_SOURCE where text like '%some string%';

SQL- Multiple rows from Dual

Query - Multiple rows from Dual
select 'x',level from dual a  connect by level < 10

SQL -PO with Rcts, PO req and Internal Req

Query - PO


--PO with receipts
  SELECT
rsh.receipt_num
--
            ph.segment1 po_num,
--         ood.organization_name,
--         pol.po_line_id,
--         pll.quantity,
--         rsh.receipt_source_code,
--         rsh.vendor_id,
--         rsh.vendor_site_id,
--         rsh.organization_id,
--         rsh.shipment_num,
--         rsh.receipt_num,
--         rsh.ship_to_location_id,
--         rsh.bill_of_lading,
--         rsl.shipment_line_id,
--         rsl.QUANTITY_SHIPPED,
--         rsl.QUANTITY_RECEIVED
--         rct.transaction_type,
--         rct.transaction_id,
--         NVL (rct.source_doc_quantity, 0) transaction_qty
    FROM
         rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pol,
         po_line_locations_all pll,
         po_headers_all ph,
         org_organization_definitions ood
   WHERE     1 = 1
        -- AND TO_CHAR (rct.creation_date, 'YYYY') IN ('2010', '2011')
         AND rct.po_header_id = ph.po_header_id
         AND rct.po_line_location_id = pll.line_location_id
         AND rct.po_line_id = pol.po_line_id
         AND rct.shipment_line_id = rsl.shipment_line_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsh.ship_to_org_id = ood.organization_id
         and ph.segment1 in ('300046929')




--Purchase Requisition Details
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prh.type_lookup_code,
       prl.line_num,
       prl.line_type_id,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'PURCHASE'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id
       --AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')


-- INternal requisitions
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prl.line_num,
       prl.line_type_id,
       prl.source_type_code,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'INTERNAL'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id(+) = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id(+)
       AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

SQL -shipped orders

EXEC apps.mo_global.set_policy_context('S','83');

  select ooh.order_number , ool.ordered_item , ool.shipping_quantity_uom , ool.shipped_quantity  , ooh.customer_number, ooh.booked_date , ool.ship_from , wdd.source_header_id
    from   oe_order_lines_v ool
    , oe_order_headers_v ooh
    ,wsh_delivery_details wdd
  where ool.header_id = ooh.header_id
  and   ool.header_id              =  wdd.source_header_id
  AND    ool.line_id                =  wdd.source_line_id
  AND    ool.inventory_item_id      =  wdd.inventory_item_id
  AND    wdd.released_status        =  'C'
   and ool.shipping_quantity_uom = 'EA'
   and ooh.customer_number in ('1000', '1020')
  AND    trunc(ool.actual_shipment_date) >   trunc(sysdate)-2 ;
  and    trunc(ooh.ordered_date) >   trunc(sysdate)-2 ;