Wednesday, July 22, 2015

Query to Find out In transit Shipments against IR-ISO and Drop ship Orders

Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
  FROM  oe_order_headers_all oha ,oe_order_lines_all ola
  WHERE oha.header_id=ola.header_id
  and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                      WSH_DELIVERY_ASSIGNMENTS  WDA,
                      wsh_new_deliveries WND,
                      WSH_DELIVERY_DETAILS WSH
                      WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                      AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                      and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
          --and trunc(ola.schedule_ship_date) is not null
           and ola.CANCELLED_FLAG <> 'Y'
          and rownum=1) NEED_BY_dATE,
          NULL customer_num,
          NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where  ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id (+) =rsl.shipment_line_id
and rcv.shipment_header_id (+) =rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is  null
and trunc(ve.shipped_date) <= :P_TO_DATE
  AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE)--INTO :CP_NEED_BY_DATE
  FROM  oe_order_headers_all oha ,oe_order_lines_all ola
  WHERE oha.header_id=ola.header_id
  and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                      WSH_DELIVERY_ASSIGNMENTS  WDA,
                      wsh_new_deliveries WND,
                      WSH_DELIVERY_DETAILS WSH
                      WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                      AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                      and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
          --and trunc(ola.schedule_ship_date) is not null
           and ola.CANCELLED_FLAG <> 'Y'
          and rownum=1) NEED_BY_dATE,
          NULL customer_num,
          NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id=rsl.shipment_line_id
and rcv.shipment_header_id=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
and rcv.TRANSACTION_TYPE='DELIVER' and trunc(rcv.TRANSACTION_DATE) > :P_TO_DATE and trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
rsl.shipment_line_id,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
UNION  ALL
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
  FROM  oe_order_headers_all oha ,oe_order_lines_all ola
  WHERE oha.header_id=ola.header_id
  and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                      WSH_DELIVERY_ASSIGNMENTS  WDA,
                      wsh_new_deliveries WND,
                      WSH_DELIVERY_DETAILS WSH
                      WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                      AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                      and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
          --and trunc(ola.schedule_ship_date) is not null
           and ola.CANCELLED_FLAG <> 'Y'
          and rownum=1) NEED_BY_dATE,
          NULL customer_num,
          NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS NULL
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
  FROM  oe_order_headers_all oha ,oe_order_lines_all ola
  WHERE oha.header_id=ola.header_id
  and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                      WSH_DELIVERY_ASSIGNMENTS  WDA,
                      wsh_new_deliveries WND,
                      WSH_DELIVERY_DETAILS WSH
                      WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                      AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                      and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
          --and trunc(ola.schedule_ship_date) is not null
           and ola.CANCELLED_FLAG <> 'Y'
          and rownum=1) NEED_BY_dATE,
          NULL customer_num,
          NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS not NULL
and rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR'))
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
rsl.shipment_line_id,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
SELECT   'Drop Ship' Col_Type,
         od.ship_from_org_id organizaiton_id,
         od.organization_code,
         od.organization_name,
          ola.inventory_item_id,
          ROUND (wsh.shipped_quantity, 2) qty,
           wsh.lot_number,
          ola.order_quantity_uom,
          ola.ordered_item Item,
          (SELECT DISTINCT description
                     FROM mtl_system_items_kfv
                    WHERE inventory_item_id =
                                            ola.inventory_item_id
                      AND organization_id = ola.ship_from_org_id) description,
           to_char(wnd.delivery_id) shipent_number,
           (select organization_name from org_organization_definitions where organization_id=ola.ship_from_org_id) from_organization_name,
           TRUNC (wnd.initial_pickup_date+(10.5/24)) shipped_Date,
           null shipment_line_id,
           od.REQUEST_DATE need_by_date ,
           od.customer_num,
           od.customer_name            
       FROM oe_order_headers_all oha,
         oe_order_lines_all ola,
         wsh_delivery_details wsh,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wdd,
         hz_locations hz,
         (select ooh.order_number,
        ool.line_number
       ,ool.ordered_item
       ,ool.ordered_quantity
       ,ooh.flow_status_code header_status                
       ,ool.flow_status_code line_status
       ,prha.segment1 requisition
       ,poh.segment1 po_number
       ,odss.po_header_id
       ,poh.closed_code po_status
       ,pll.quantity
       ,pll.quantity_received
       ,ool.REQUEST_DATE
       ,pll.closed_code po_shipment_status,
       ool.ship_from_org_id,
        (SELECT DISTINCT ACCOUNT_NUMBER
                     FROM hz_parties hz, hz_cust_accounts hcu
                    WHERE hz.party_id = hcu.party_id
                      AND hcu.cust_account_id = ool.sold_to_org_id)
                                                                customer_num,                                                                                                                            
(SELECT DISTINCT party_name
                     FROM hz_parties hz, hz_cust_accounts hcu
                    WHERE hz.party_id = hcu.party_id
                      AND hcu.cust_account_id = ool.sold_to_org_id)
                                                                customer_name,
       (select organization_code from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_code,
         (select organization_name from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_name
from apps.oe_order_headers_all                   ooh
    ,apps.oe_order_lines_all                     ool
    ,apps.oe_drop_ship_sources                   odss
    ,apps.po_requisition_headers_all             prha
    ,apps.po_headers_all                         poh
    ,apps.po_lines_all                           pol
    ,apps.po_line_locations_all                  pll            
where ool.header_id = ooh.header_id
and   odss.header_id = ooh.header_id
and   odss.line_id = ool.line_id
and   prha.requisition_header_id = odss.requisition_header_id
and   poh.po_header_id = odss.po_header_id
and   pol.po_line_id = odss.po_line_id
and   pol.po_header_id = poh.po_header_id
and   pll.po_line_id = pol.po_line_id
and   ool.flow_status_code='AWAITING_RECEIPT'
--and poh.segment1='41102417'
and ooh.org_id=:p_org_id) od
   WHERE oha.header_id = ola.header_id
     AND ola.line_id = wsh.source_line_id
     AND oha.header_id = wsh.source_header_id
     AND wsh.delivery_detail_id=wdd.delivery_detail_id
     AND wdd.delivery_id=wnd.delivery_id(+)
   --  AND wsh.delivery_detail_id = jail.delivery_detail_id
     AND hz.location_id=wsh.ship_to_location_id
         and wsh.source_line_id not in (select DISTINCT REFERENCE_LINE_ID  from oe_order_lines_all
                                 where REFERENCE_LINE_ID is not null)
      --  and ola.line_id=1379928
     and ola.line_number (+) =od.line_number
and ola.cust_po_number (+) =od.po_number
order by 3,9,7





******************************************************************

Some other fields that can be used in placeholders to print more descriptive information like IR number/ PO Number. Invoice number etc.....




function CF_VALUESFormula return Number is
begin
 
 
  if :COL_TYPE='Intransit' THEN
 
  ------------------IR NUMBER-------------------

 BEGIN
 
  SELECT distinct ORIG_SYS_DOCUMENT_REF INTO :CP_IR_NUM
  FROM  oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                                                                                                WSH_DELIVERY_ASSIGNMENTS  WDA,
                                                                                                wsh_new_deliveries WND,
                                                                                                WSH_DELIVERY_DETAILS WSH
                                                                                                WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                                                                                                AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                                                                                                and wnd.DELIVERY_ID=:SHIPMENT_NUM);


EXCEPTION WHEN NO_DATA_FOUND
THEN  :CP_IR_NUM := NULL;

END;



------------------IR STATUS-------------------

 BEGIN
 
  select DISTINCT AUTHORIZATION_STATUS INTO :CP_IR_STATUS from PO_REQUISITION_HEADERS_ALL where segment1 =
 (SELECT distinct ORIG_SYS_DOCUMENT_REF
  FROM  oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                                                                                                WSH_DELIVERY_ASSIGNMENTS  WDA,
                                                                                                wsh_new_deliveries WND,
                                                                                                WSH_DELIVERY_DETAILS WSH
                                                                                                WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                                                                                                AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                                                                                                and wnd.DELIVERY_ID=:SHIPMENT_NUM));


EXCEPTION WHEN NO_DATA_FOUND
THEN  :CP_IR_NUM := NULL;

END;

----------------------------RECEIPT DATE IF REPORT RUN BACK DATED------------------

BEGIN

select transaction_Date into :cp_rec_date
from rcv_transactions
where shipment_line_id=:shipment_line_id
and transaction_type='RECEIVE'
and rownum=1;

EXCEPTION WHEN NO_DATA_FOUND
THEN  :cp_rec_date := NULL;

END;

--------------------AR INVOICE NUMBER ---------------------

begin
   
 select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
                    where to_char(INTERFACE_HEADER_ATTRIBUTE1) in (select to_char(order_number) from oe_order_headers_all
                    where orig_sys_document_ref in   (SELECT ORIG_SYS_DOCUMENT_REF
                                               FROM  oe_order_headers_all WHERE HEADER_ID in (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                                                                                                WSH_DELIVERY_ASSIGNMENTS  WDA,
                                                                                                wsh_new_deliveries WND,
                                                                                                WSH_DELIVERY_DETAILS WSH
                                                                                                WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                                                                                                AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                                                                                                and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
                    and rownum=1 ;


EXCEPTION WHEN NO_DATA_FOUND
THEN  :CP_TRX_NUMBER := NULL;

END;


END IF;



 if :COL_TYPE='Drop Ship' THEN
 
  ------------------IR NUMBER-------------------

 begin
 
  SELECT distinct CUST_PO_NUMBER INTO :CP_IR_NUM
  FROM  oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                                                                                                WSH_DELIVERY_ASSIGNMENTS  WDA,
                                                                                                wsh_new_deliveries WND,
                                                                                                WSH_DELIVERY_DETAILS WSH
                                                                                                WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                                                                                                AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                                                                                                and wnd.DELIVERY_ID=:SHIPMENT_NUM);


EXCEPTION WHEN NO_DATA_FOUND
THEN   :CP_IR_NUM := NULL;

END;


--------------------AR INVOICE NUMBER ---------------------

begin

 select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
                    where to_char(INTERFACE_HEADER_ATTRIBUTE1) = (select to_char(order_number) from oe_order_headers_all
                    where orig_sys_document_ref =   (SELECT ORIG_SYS_DOCUMENT_REF
                                               FROM  oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
                                                                                                WSH_DELIVERY_ASSIGNMENTS  WDA,
                                                                                                wsh_new_deliveries WND,
                                                                                                WSH_DELIVERY_DETAILS WSH
                                                                                                WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
                                                                                                AND WND.DELIVERY_ID= WDA.DELIVERY_ID
                                                                                                and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
                    and rownum=1 ;

EXCEPTION WHEN NO_DATA_FOUND
THEN  :CP_TRX_NUMBER := NULL;

END;



                                                                                               
                RETURN (0);
               
 
  EXCEPTION
      WHEN NO_DATA_FOUND
          THEN RETURN(0);
      WHEN OTHERS THEN
            SRW.MESSAGE(999, 'Error In Formula CF_VALUES : ' || SQLERRM);
          RAISE SRW.PROGRAM_ABORT;
 
end;

No comments:

Post a Comment

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...