Oracle EBS: How to Delete Data Definition and Data Template From DB Backend Script.

Subject: How to Delete Data Definition and Data Template From DB Backend Script. 

PLSQL Script:

BEGIN

DELETE FROM XDO_TEMPLATES_B

WHERE template_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_TEMPLATES_TL

WHERE template_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_LOBS

WHERE lob_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_DS_DEFINITIONS_B

WHERE data_source_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_DS_DEFINITIONS_TL

WHERE data_source_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

COMMIT;

END;

Oracle EBS: How to update the DBA Directory Path

 select * from dba_directories where directory_name='TLM_IFACE_INBOUND';  

--Old Path:--

/s01/oracle/interfaces/TLM/Iface/Inbound

--New Path:--

/s01/interfaces/mounts/ItemAutomation

--SQL Script:--

CREATE OR REPLACE directory TLM_IFACE_INBOUND AS '/s01/interfaces/mounts/ItemAutomation';

Oracle Fusion: How to Get ORIG_SYSTEM_REFERENCE for the Party and Party Sites while performing customer migration

Subject: When we perform bulk customer migration using FBID we need to get ORIG_SYSTEM_REFERENCE after the migration is complete.

Note: HZ_ORIG_SYS_REFERENCES  is the base table to store the ORIG_SYSTEM_REFERENCE details

--PARTY ORIG SYS REFERENCE--

SELECT ORIG_SYSTEM_REFERENCE 

FROM HZ_ORIG_SYS_REFERENCES 

WHERE OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID  --NEED TO PASS PARTY_ID

AND ORIG_SYSTEM='CSV'

AND OWNER_TABLE_NAME='HZ_PARTIES' -- FOR PARTY REFERENCE PARTY TABLE NEED TO PASS

--PARTY SITE ORIG SYS REFERENCE--

SELECT ORIG_SYSTEM_REFERENCE 

FROM HZ_ORIG_SYS_REFERENCES 

WHERE OWNER_TABLE_ID =HZ_PARTY_SITES.PARTY_SITE_ID  --NEED TO PASS PARTY_SITE_ID

AND ORIG_SYSTEM='CSV'

AND OWNER_TABLE_NAME='HZ_PARTY_SITES' -- FOR PARTY REFERENCE PARTY SITE TABLE NEED TO PASS

Oracle EBS - SQL Query to Get Item Open Cycle Count Entries

 --1. Check for physical inventory adjustments where adjustment quantity is not 0

SELECT

    msib.segment1        "Item",

    mp.organization_code,

    msib.organization_id,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    lot_control_code,

    decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5, 'At Receipt',

           6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    mtl_physical_adjustments    mpa

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') --Add Organization Code

    AND mpa.inventory_item_id = msib.inventory_item_id

    AND mpa.organization_id = msib.organization_id

    AND mpa.approval_status = 1

    AND mpa.adjustment_quantity <> 0

    AND msib.item_type='P'

ORDER BY

    mc.segment1;

--2. Check for cyclecount entries that are marked as uncounted, pre-approved (pending) or recount (See similar query exists in Note 1373918.1)

SELECT

    msib.segment1        "Item",

    mp.organization_code,

    msib.organization_id,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    lot_control_code,

    decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5, 'At Receipt',

           6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    mtl_cycle_count_entries     mcce

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1 --Not Lot Enabled Item

    AND nvl(msib.end_date_active, sysdate) >= sysdate

     AND mp.organization_code IN ('MAS') --Add Organization Code

    AND mcce.inventory_item_id = msib.inventory_item_id

    AND mcce.organization_id = msib.organization_id

    AND mcce.entry_status_code IN ( 1, 2, 3 )

    AND msib.item_type='P'

ORDER BY

    mc.segment1;

--3. Alternatively, the following query provides the name of the cycle count

SELECT

    msib.segment1        "Item",

    mp.organization_code,

    msib.organization_id,

    ch.description       cycle_count_name,

    ch.cycle_count_header_id,

    ce.cycle_count_entry_id,

    ce.entry_status_code,

    lu1.meaning          entry_status_code_meaning,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    msib.lot_control_code,

    decode(msib.serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5, 'At Receipt', 6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY"

FROM

    apps.mtl_system_items_b             msib,

    apps.mtl_item_categories            mic,

    apps.mtl_categories                 mc,

    apps.mtl_parameters                 mp,

    apps.mtl_cycle_count_headers    ch,

    apps.mtl_cycle_count_entries        ce,

    apps.mtl_item_flexfields            mif,

    apps.mfg_lookups                    lu1

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') --Add Organization Code

    AND mif.inventory_item_id = msib.inventory_item_id

    AND mif.organization_id = msib.organization_id

    AND mp.organization_id = mif.organization_id

    AND mif.inventory_item_id = ce.inventory_item_id

    AND ce.cycle_count_header_id = ch.cycle_count_header_id

    AND 'MTL_CCEOI_STATUS_FLAG' = lu1.lookup_type (+)

    AND ce.entry_status_code = lu1.lookup_code (+)

    AND ce.entry_status_code IN ( 1, 2, 3 )

    AND msib.item_type='P' --Component Item

ORDER BY

    mc.segment1;

Oracle EBS - SQL Query to Get Item having End Dated Buyer

 SELECT

    msib.segment1        "Item",

    mp.organization_code,

    msib.organization_id,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    lot_control_code,

    decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5,

           'At Receipt',

           6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY",

    (

        SELECT

            full_name

        FROM

            per_all_people_f

        WHERE

                person_id = fu.employee_id

            AND ROWNUM = 1

    )                    buyer

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    fnd_user             fu

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND msib.buyer_id = fu.employee_id (+)

    AND trunc(fu.end_date) < trunc(sysdate) --end dated buyer

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS')--Add Organization Code

    AND msib.item_type='P' --Component Item

ORDER BY

    mc.segment1

Oracle EBS - SQL Query to get Open Intransit Shipment Transaction Details

 SELECT

    xx.supply_source_id,

    msib.segment1        "Item",

    mp.organization_code,

    xx.po_num            intransit_po_num,

    xx.shipment_num      intransit_shipment_num,

    xx.line_num,

    xx.quantity_shipped,

    xx.quantity_received,

    msib.organization_id,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    lot_control_code,

    decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5, 'At Receipt', 6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    (

        SELECT

            ms.supply_source_id,

            pla.item_id,

            ms.to_organization_id,

            pha.segment1 po_num,

            rcv.shipment_num,

            rsl.line_num,

            rsl.quantity_shipped,

            rsl.quantity_received

        FROM

            mtl_supply             ms,

            po_headers_all         pha,

            po_lines_all           pla,

            po_line_locations_all  plla,

            rcv_shipment_headers   rcv,

            rcv_shipment_lines     rsl

        WHERE

                ms.destination_type_code = 'INVENTORY'

            AND ms.supply_type_code = 'SHIPMENT'

            AND ms.po_header_id = pha.po_header_id

            AND pha.po_header_id = pla.po_header_id

            AND ms.po_line_id = pla.po_line_id

            AND plla.po_header_id = pha.po_header_id

            AND plla.po_line_id = pla.po_line_id

            AND plla.line_location_id = ms.po_line_location_id

            AND ms.to_organization_id = plla.ship_to_organization_id

            AND ms.shipment_header_id = rcv.shipment_header_id

            AND rcv.shipment_header_id = rsl.shipment_header_id

            AND pla.item_id = rsl.item_id

            AND ms.to_organization_id IN ( 85, 97 )

            

--AND ms.PO_HEADER_id=23372233

--AND ms.PO_LINE_ID=23856916 

    )                    xx

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') -- Organization Code

    AND msib.item_type='P'

    AND xx.item_id = msib.inventory_item_id --item id link

    AND xx.to_organization_id = msib.organization_id --org id link 

ORDER BY

    mp.organization_code,

    xx.po_num,

    mc.segment1

Oracle EBS - SQL Query to get consigned Item not having blanket PO

 SELECT

    mp.organization_code,

    msib.organization_id,

    upper(mc.segment1)       type,

    msib.inventory_item_id,

    msib.segment1            item_code,

    msib.primary_uom_code,

    mic.segment1             ppl,

    mic.segment2             spl

FROM

    apps.mtl_system_items_b              msib,

    apps.mtl_parameters                  mp,

    apps.mtl_onhand_quantities_detail    moqd,

    apps.mtl_item_locations_kfv          milk,

    apps.mtl_item_categories_v           mic,

    apps.mtl_categories                  mc,

    apps.mtl_serial_numbers              msn

WHERE

        1 = 1

--            AND MSIB.SEGMENT1 = 'NT7E70AASIF' -- '6029PKGN406'

    AND msib.organization_id = mp.organization_id

    AND msib.inventory_item_id = moqd.inventory_item_id

    AND msib.organization_id = moqd.organization_id

    AND msib.organization_id = milk.organization_id

    AND moqd.locator_id = milk.inventory_location_id

    AND msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = msn.current_organization_id (+)

    AND moqd.subinventory_code = msn.current_subinventory_code (+)

    AND nvl(msn.current_status, 3) = 3

    AND msib.inventory_item_id = msn.inventory_item_id (+)

    AND moqd.lot_number = msn.lot_number (+)

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND lot_control_code != 2

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND moqd.is_consigned = 1  -- consiged inv

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') --Add ORGANIZATION CODE

    AND msib.item_type='P'

    AND NOT EXISTS (

        SELECT

            1

        FROM

            apps.po_headers_all        pha,

            apps.po_lines_all          pla,

            apps.hr_operating_units    hou,

            apps.mtl_system_items_b    msi

        WHERE

                1 = 1

            AND pha.po_header_id = pla.po_header_id

            AND pha.org_id = hou.organization_id

            AND pla.item_id = msi.inventory_item_id

            AND msi.organization_id IN ( 85, 97 ) --Organization ID

            AND pha.type_lookup_code = 'BLANKET'

            AND nvl(pha.closed_code, 'OPEN') = 'OPEN'

            AND pha.authorization_status = 'APPROVED'

            AND ( pha.cancel_flag <> 'Y'

                  OR pha.cancel_flag IS NULL )

            AND ( pla.cancel_flag <> 'Y'

                  OR pla.cancel_flag IS NULL )

            AND trunc(sysdate) <= trunc(nvl(pha.end_date, sysdate))

            AND trunc(sysdate) <= trunc(nvl(pla.expiration_date, sysdate))

            AND msi.inventory_item_id = msib.inventory_item_id --item id link

            AND msi.organization_id = msib.organization_id --org id link

    )

GROUP BY

    mp.organization_code,

    msib.organization_id,

    upper(mc.segment1),

    msib.inventory_item_id,

    msib.segment1,

    msib.primary_uom_code,

    mic.segment1,

    mic.segment2

ORDER BY

    msib.segment1

Oracle EBS - SQL Query to get Item Reserved Qty Details

 SELECT

    segment1 item,

    organization_code,

    reserved_qty,

    order_number,

    line_number,

    locator_id,

    location

FROM

    (

        SELECT

            msib.segment1,

            mp.organization_code,

            msib.organization_id,

            msib.purchasing_item_flag,

            msib.shippable_item_flag,

            msib.inventory_item_flag,

            lot_control_code,

            decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

                   5,

                   'At Receipt',

                   6,

                   'At Issue')         "Serial Control",

            mc.segment1                "PPL",

            mc.segment2                "SPL",

            mc.segment3                "ITEM CATEGORY",

            mr.reservation_quantity    reserved_qty,

            mr.demand_source_header_id,

            h.order_number,

            l.line_number,

            mr.locator_id,

            milk.CONCATENATED_SEGMENTS location

--            (

--                SELECT

--                    nvl(SUM(reservation_quantity), 0)

--                FROM

--                    mtl_reservations

--                WHERE

--                        inventory_item_id = msib.inventory_item_id

--                    AND organization_id = msib.organization_id

--            )                    reserved_qty

        FROM

            mtl_system_items_b    msib,

            mtl_item_categories   mic,

            mtl_categories        mc,

            mtl_parameters        mp,

            mtl_reservations      mr,

            oe_order_headers_all  h,

            oe_order_lines_all    l,

            apps.mtl_item_locations_kfv milk

        WHERE

                msib.inventory_item_id = mic.inventory_item_id

            AND msib.organization_id = mic.organization_id

            AND msib.organization_id = mp.organization_id

            AND mic.category_id = mc.category_id

            AND mic.category_set_id = 1

            AND mc.structure_id = 101

            AND msib.enabled_flag = 'Y'

            AND msib.lot_control_code = 1

--            AND msib.segment1='NTTC90ABE6'

            AND nvl(msib.end_date_active, sysdate) >= sysdate

            AND mc.disable_date IS NULL

            AND mp.organization_code IN ('MAS')  --Org CODE

            AND mr.inventory_item_id = msib.inventory_item_id

            AND mr.organization_id = msib.organization_id

            AND h.header_id = l.header_id

            AND nvl(l.cancelled_flag, 'N') <> 'Y'

            AND l.line_id = mr.demand_source_line_id

            AND mr.locator_id=milk.INVENTORY_LOCATION_ID(+)

            AND mr.organization_id = milk.organization_id(+)

            AND msib.item_type='P'

        ORDER BY

            mc.segment1

    )

WHERE

    reserved_qty <> 0