Delivery Note(Oracle Fusion)

 SELECT

   esi.item_number||' '||esit.description item_desc,

   hz.party_name customer_name,

  fl.source_order_number order_number,

  dd.REQUESTED_QUANTITY,

  TO_CHAR(nd.CONFIRM_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') as CONFIRM_DATE, 

  dha.header_id,

  dla.DISPLAY_LINE_NUMBER,

  fl.fulfill_line_id,

  dd.SPLIT_FROM_DELIVERY_DETAIL_ID,

  fl.status_code as status,

  fl.ordered_qty as quantity,

  dla.ordered_qty,

  dd.delivery_detail_id,

  nd.delivery_Name,

  fl.fulfillment_split_ref_id,

  fl.shipped_qty,                          

  dd.lot_number,

  DECODE (

    dd.Released_status,

    'B',

    'Backordered',

    'C',

    'Shipped',

    'D',

    'Cancelled',

    'N',

    'Not Ready for Release',

    'R',

    'Ready to Release',

    'S',

    'Released to Warehouse',

    'X',

    'Not Applicable',

    'Y',

    'Staged'

  ) "line status",   

  dd.INV_INTERFACED_FLAG,

  dd.WMS_INTERFACED_FLAG,

  dd.SHIPMENT_ADVICE_STATUS,

  nd.INTERFACE_BATCH_ID,

  nd.STATUS_CODE,

  nd.ASN_STATUS_CODE,

  dd.shipped_quantity,

  fld.TASK_TYPE,

  fld.bill_of_lading_number,

  (SELECT Distinct mumt.Unit_of_Measure

from

inv_material_txns MMT,

egp_system_items_b MSIB1,

inv_units_of_measure_tl mumt,

inv_units_of_measure_b mumb

WHERE 1=1

AND esi.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID

AND  esi.ORGANIZATION_ID = MMT.ORGANIZATION_ID

AND mumt.UNIT_OF_MEASURE_ID=mumb.UNIT_OF_MEASURE_ID

AND upper(mumb.uom_code)= upper(MMT.TRANSACTION_UOM)

and mumt.language= 'US'

and nvl(mumb.disable_date, (sysdate + 1)) > sysdate

AND ROWNUM=1) QUANTITY_UOM,

dheb.attribute_char1 flight_number,

dheb.attribute_char2 Aircraft_type,

dheb.ATTRIBUTE_TIMESTAMP1 dd1,

TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY') Flight_Departure_Time,

TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP2,'DD-MM-YYYY') Delivery_time,

to_char(dha.ORDERED_DATE ,'dd-MM-yyyy') ordered_date,

iodv.ORGANIZATION_NAME Ship_from_Organization

-- TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MON-YYYY''NLS_DATE_LANGUAGE=AMERICAN') Flight_Departure_Time,

-- TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP2,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Delivery_time

 

 

FROM

  doo_headers_all dha,

  doo_headers_eff_b dheb,

  doo_fulfill_lines_all fl,

  doo_fulfill_line_details fld,

  doo_lines_all dla,

  wsh_delivery_details dd,

  wsh_delivery_assignments da,

  wsh_new_deliveries nd,

  egp_system_items_b esi,

  EGP_SYSTEM_ITEMS_TL esit,

  HZ_PARTIES HZ,

  INV_ORGANIZATION_DEFINITIONS_V iodv

 

WHERE

  dha.header_id = fl.header_id

   AND dd.SHIP_TO_PARTY_ID = HZ.PARTY_ID

  AND dha.header_id=dheb.header_id

  AND fl.line_id = dla.line_id

  AND fld.task_type (+) = 'Shipment'

  AND dha.source_order_number = dd.SALES_ORDER_NUMBER

  AND dd.delivery_detail_id = da.delivery_detail_id (+)

  AND nd.delivery_id (+) = da.delivery_id

  AND fl.fulfill_line_id = dd.source_shipment_id

  AND SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL

  AND fld.delivery_name = nd.delivery_name (+)

  AND fl.fulfill_line_id = fld.fulfill_line_id (+)

  AND esi.inventory_item_id = dd.inventory_item_id

  AND esi.organization_id = dd.organization_id

  AND esi.INVENTORY_ITEM_ID=esit.INVENTORY_ITEM_ID

   AND esi.organization_id=esit.organization_id

   AND esi.organization_id=iodv.organization_id

    AND fl.source_order_number=NVL(:p_order_number,fl.source_order_number)

AND (fl.source_order_number IN (:p_order_number) OR 'All' IN (:p_order_number ||'All'))

AND hz.party_name=NVL(:p_custmer_name,hz.party_name)

AND TRUNC(TO_DATE(TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY'),'DD-MM-YYYY'))=NVL(:P_Flight_departure_Dt,TRUNC(TO_DATE(TO_CHAR(dheb.ATTRIBUTE_TIMESTAMP1,'DD-MM-YYYY'),'DD-MM-YYYY')))

and dheb.attribute_char1=NVL(:p_flight_number,dheb.attribute_char1

Transfer Order(Oracle Fusion)

 --Oracle Fusion Transfer Order

SELECT 

         iop.organization_code

,ir.subinventory_code subinv

        ,esib.item_number

,msit.description item_description

         ,itoh.header_number  Transfer_orders

,nvl(ir.primary_reservation_quantity,0) reserved_qty

,itol.requested_qty demand_qty

,(nvl(itol.requested_qty,0) -  nvl(ir.primary_reservation_quantity,0)) remaining_qty

,esib.primary_uom_code uom

,(SELECT meaning

FROM fnd_lookup_values_vl flvv

WHERE  flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES'

AND flvv.lookup_code = ir.demand_source_type_id

AND flvv.view_application_id = 0) demand_source_type_name  

       ,to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') required_date

   ,to_char(itoh.ordered_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') ordered_date

   ,to_char(itol.SCHEDULED_SHIP_DATE, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') requested_date  

   ,(NVL(sum(ioqd.primary_transaction_quantity),0)) onhand_qty

   , itol.DESTINATION_ORGANIZATION_ID

   ,hl.location_name Destination_location

   -- ,ioqd.primary_transaction_quantity onhand_qty

    /*   ,ir.revision rev

   -- ,ir.lot_number lot_number

   ,ir.locator_id loc_id

   ,ir.demand_source_type_id type_id

   -- ,ir.demand_source_name source  

       -- ,iop.organization_code    

   ,itst.transaction_source_type_name  source_type

   ,iil.locator_name

   ,itst.transaction_source_type_id

   ,ecs.category_set_name

   ,ec.category_code

   ,ec.category_name */

FROM 

inv_transfer_order_headers itoh

    ,inv_transfer_order_lines itol

    ,inv_sales_orders iso

     ,inv_reservations ir

,egp_system_items_b esib

  ,EGP_SYSTEM_ITEMS_TL msit

,inv_org_parameters iop

,inv_txn_source_types_tl itst

,egp_categories_vl ec 

,egp_item_categories  eic

,egp_category_sets_tl  ecs

,inv_generic_dispositions igd

,gl_code_combinations gl

,inv_item_locations iil

,inv_onhand_quantities_detail ioqd

,hr_locations hl

WHERE 1=1

AND esib.INVENTORY_ITEM_ID=msit.INVENTORY_ITEM_ID

AND itoh.header_id     = itol.header_id

AND itol.source_organization_id = ir.organization_id

AND itol.inventory_item_id    = ir.inventory_item_id

AND iso.sales_order_id        = ir.demAND_source_header_id

AND iso.source_order_number  = itoh.header_number

AND ir.organization_id        = esib.organization_id

AND ir.inventory_item_id      = esib.inventory_item_id

AND iop.organization_id      = esib.organization_id

AND ir.organization_id        = msit.organization_id

AND ir.inventory_item_id      = msit.inventory_item_id

AND iop.organization_id      = msit.organization_id

AND ir.demand_source_type_id = itst.transaction_source_type_id

AND itst.language            = 'US'

AND eic.inventory_item_id    = esib.inventory_item_id

AND eic.organization_id      = esib.organization_id

AND ecs.category_set_id      = eic.category_set_id

AND ec.category_id            = eic.category_id 

AND ecs.language              = 'US'

AND ir.demAND_source_header_id  = gl.code_combination_id(+)

AND ir.demAND_source_header_id  = igd.disposition_id(+)

AND  ir.locator_id              = iil.inventory_location_id(+)

AND ioqd.organization_id = esib.organization_id(+)

  AND ioqd.inventory_item_id = esib.inventory_item_id (+)

AND itol.destination_location_id = hl.location_id(+)

-- AND esib.item_number = 'ABC123'

--AND itoh.header_number='1234'

 

Group by 

          iop.organization_code

,ir.subinventory_code 

        ,esib.item_number

,msit.description

         ,itoh.header_number

,nvl(ir.primary_reservation_quantity,0)

,itol.requested_qty 

,(nvl(itol.requested_qty,0) -  nvl(ir.primary_reservation_quantity,0)) 

,esib.primary_uom_code

,ir.demand_source_type_id

,to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

        ,to_char(itoh.ordered_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

,to_char(itol.SCHEDULED_SHIP_DATE, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

,itol.DESTINATION_ORGANIZATION_ID

,hl.location_name

        order by 

to_char(ir.requirement_date, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') desc,

esib.item_number

 

Item availability Report

 --Item availability Report --> 

Select  

       a.organization_code Organization_name,

   a.secondary_inventory_name  Subinventory,

   a.item_number,

   a.onhand_qty,

   a.Primary_Reserv_qty,

   a.onhand_qty - a.Primary_Reserv_qty  Available_to_Reserve,

       a.onhand_qty-( a.onhand_qty - a.Primary_Reserv_qty) Reserve_Quantity    

from 

(

SELECT

  iop.organization_code ,

    isi.secondary_inventory_name ,

    esib.item_number  ,

  (NVL(sum(ioqd.primary_transaction_quantity), 0)) onhand_qty,

(select  (nvl(sum(ir.PRIMARY_RESERVATION_QUANTITY),0)) 

from inv_reservations ir  

where 1=1

AND esib.inventory_item_id = ir.inventory_item_id 

AND esib.organization_id = ir.organization_id 

and ioqd.subinventory_code = ir.subinventory_code  )

Primary_Reserv_qty,

(Select sum(TRANSACTION_QUANTITY) 

from inv_material_txns imt

where  esib.inventory_item_id = imt.inventory_item_id 

AND esib.organization_id = imt.organization_id 

and ioqd.subinventory_code = imt.subinventory_code ) mtr_qty

 

 

FROM

  egp_system_items_v esib,

  inv_org_parameters iop,

  inv_secondary_inventories isi,

  inv_item_locations iil,

  inv_onhand_quantities_detail ioqd

  -- inv_reservations ir

WHERE

  iop.organization_id = esib.organization_id

  AND iop.organization_id = isi.organization_id

  AND isi.secondary_inventory_name = ioqd.subinventory_code(+)

  AND isi.organization_id = ioqd.organization_id (+)

  AND ioqd.organization_id = esib.organization_id(+)

  AND ioqd.inventory_item_id = esib.inventory_item_id (+)

  AND ioqd.locator_id = iil.inventory_location_id(+)

  AND ioqd.organization_id = iil.organization_id(+)

  -- AND esib.inventory_item_id = ir.inventory_item_id 

-- AND iop.organization_id = ir.organization_id 

-- AND isi.subinventory_id = ir.subinventory_id 

  -- and esib.ITEM_NUMBvER in ('ABC123','ABC234','ABC456')

GROUP BY

  iop.organization_code,

  esib.item_number,

  esib.primary_uom_code,

  esib.description,

  isi.secondary_inventory_name,

  isi.description,

  iil.locator_name,

  ioqd.revision,

  ioqd.locator_id,

  esib.inventory_item_id,

  isi.subinventory_id,

  esib.organization_id,

  ioqd.subinventory_code

ORDER BY

  isi.secondary_inventory_name,

  esib.item_number

  ) A

  order by  a.item_number,a.secondary_inventory_name

frm-40654 record has been updated. requery block

 You can keep the log as below at Lock procedure and compare both LHS and RHS values are same.

if not same then check what is the reason.

You have to add the log as below and compile then form and open that form at application and enter the value at that block level. Then the following second snapshot will appear.

AR Open Invoices for customer

select aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND aps.status = 'OP'
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND aps.amount_due_remaining <> 0
and hc.cust_account_id=1234

Ref Cursor

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

  REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database

 Disadvantages:
           REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REFCURSOR.
           A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manne

The cursor variable is specified as an IN OUT parameter so that the result set is made available to the caller of the procedure:

CREATE OR REPLACE PROCEDURE emp_by_job (
    p_job           VARCHAR2,
    p_emp_refcur    IN OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
END;

The EMP_BY_JOB procedure is invoked in the following anonymous block by assigning the procedure's IN OUT parameter to a cursor variable that was declared in the anonymous block's declaration section. The result set is fetched using this cursor variable.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE := 'SALESMAN';
    v_emp_refcur    SYS_REFCURSOR;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    emp_by_job(v_job, v_emp_refcur);
    LOOP
        FETCH v_emp_refcur INTO v_empno, v_ename;
        EXIT WHEN v_emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE v_emp_refcur;
END;

The following example output is generated when the anonymous block executes:

EMPLOYEES WITH JOB SALESMAN
EMPNO    ENAME
-----    -------
7499     ALLEN
7521     WARD
7654     MARTIN
7844     TURNER

Usage of concatenate and like operator in where condition

select * from ORG_ORGANIZATION_DEFINITIONS where ORGANIZATION_NAME like '%' || :your_var || '%'