Oracle EBS - R12 - Inventory - Oracle API to Update Lot Origin Date of Item Lot Numbers

 -- R12 - INV - Sample Script to Update Lot Number Lot Origin Date usinginv_lot_api_pub.update_inv_lot

DECLARE

    l_object_id      NUMBER;

    l_return_status  VARCHAR2(1);

    l_msg_count      NUMBER;

    l_msg_data       VARCHAR2(4000);

    l_expire_date    DATE;

    x_lot_rec        mtl_lot_numbers%rowtype;

    l_lot_rec        mtl_lot_numbers%rowtype;

    l_source         NUMBER;

    l_api_version    NUMBER := 1.0;

    l_init_msg_list  VARCHAR2(1000) := fnd_api.g_false;

    l_commit         VARCHAR2(1000) := fnd_api.g_false;

    CURSOR c_item_info IS

    SELECT

        inventory_item_id,

        organization_id,

        lot_number,

        creation_date,

        origination_date,

        c_attribute1

    FROM

        mtl_lot_numbers

    WHERE

            1 = 1

        AND trunc(origination_date) BETWEEN trunc(TO_DATE('03/03/24', 'MM/DD/YY')) AND trunc(TO_DATE('03/05/24', 'MM/DD/YY'))

        AND trunc(creation_date) != trunc(origination_date)

        AND organization_id IN ( 85, 97 )

--AND TRUNC(CREATION_DATE) >= SYSDATE-150

--and C_ATTRIBUTE1='WMOTCWRJAB'

--and c_attribute1 is not null

        AND lot_number = '1710010' --'1542009' --'1423007'

        ;

BEGIN

-- initialization required for R12

    mo_global.set_policy_context('S', 102);

    mo_global.init('INV');

-- Initialization for Organization_id

    inv_globals.set_org_id(102);

-- initialize environment

    fnd_global.apps_initialize(user_id => 11445,-----------------------------------> pass in user_id User Id=11445 ,

                              resp_id => 20634,

                              resp_appl_id => 401);

    

    FOR i IN c_item_info LOOP

    

    l_lot_rec.inventory_item_id:=i.inventory_item_id;

    l_lot_rec.organization_id:=i.organization_id;

    l_lot_rec.lot_number:=i.lot_number;

    l_lot_rec.origination_date:=i.creation_date;

    

        dbms_output.put_line('Calling inv_lot_api_pub.update_inv_lot API to Update Lot Numbers');

        dbms_output.put_line('*********************************************');

        inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status, x_msg_count => l_msg_count,

                                      x_msg_data => l_msg_data,

                                      x_lot_rec => x_lot_rec,

                                      p_lot_rec => l_lot_rec,

                                      p_source => l_source,

                                      p_api_version => 1.0,

                                      p_init_msg_list => l_init_msg_list,

                                      p_commit => l_commit);

        dbms_output.put_line('The Status Returned by the API is => ' || l_return_status);

        IF l_return_status = fnd_api.g_ret_sts_success THEN

            COMMIT;

        ELSE

            ROLLBACK;

        END IF;

        dbms_output.put_line('x_msg_count :' || l_msg_count);

        dbms_output.put_line('x_msg_data :' || l_msg_data);

    END LOOP;

END;

Oracle EBS - SQL Query to find Cloned Date, Version Details, URL of Oracle Applications

Subject: SQL Query to find Cloned Date, Version Details, URL of Oracle Applications

Query to get Instance Version details

SELECT  product, version, status FROM  product_component_version;  

Query to  get cloned date of an oracle instance

SELECT resetlogs_time FROM   v$database; 

Query to get the front end URL from back-end

SELECT home_url FROM   icx_parameters;   

Oracle EBS - Not able to create Payment in Past Date

 

Issue: 

While Creating Payment and Enter Payment Date for Past Open Period getting below error message

Error:

APP-SQLAP-10026: The Payment Date must be on or after the System Date.

Resolution:

Navigation: Payable Responsibility > Setup > Payable Option > Payment Tab > Check All Pre-Date

Oracle EBS: How to Create Request Group and Assign Request Group to Responsibility

  

Step1: Create a Request Group.

Navigate: System Administrator Responsibility > Security > Responsibility > Request

Group Name: TLM OM Inquiry

Application: Order Management

Type: Program

Name: Telamon SO Cancelled Item Details Report

 

Step2: Assign Request Group to Responsibility.

Navigate: System Administrator Responsibility > Security > Responsibility > Define

Query Responsibility Name: TLM Order Management Inquiry

Add Request Group: TLM OM Inquiry

Save it.

 

 

Oracle EBS - Order Management Sales Order Line Need to Split using Oracle API

set serveroutput on;

DECLARE

 l_header_rec OE_ORDER_PUB.Header_Rec_Type;

 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;

 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;

 l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 l_request_rec OE_ORDER_PUB.Request_Rec_Type ;

 l_return_status VARCHAR2(1000);

 l_msg_count NUMBER;

 l_msg_data VARCHAR2(1000);

 p_api_version_number NUMBER :=1.0;

 p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;

 p_return_values VARCHAR2(10) := FND_API.G_FALSE;

 p_action_commit VARCHAR2(10) := FND_API.G_FALSE;

 x_return_status VARCHAR2(1);

 x_msg_count NUMBER;

 x_msg_data VARCHAR2(100);

 p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_old_header_rec OE_ORDER_PUB.Header_Rec_Type :=  OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;

 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;

 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;

 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;

 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type; 

 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;

 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;

 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;

 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;

 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;

 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;

 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;

 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;

 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;

 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;

 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 X_DEBUG_FILE VARCHAR2(100);

 l_line_tbl_index NUMBER;

 l_msg_index_out NUMBER(10);

BEGIN

 dbms_output.enable(1000000);

 fnd_global.apps_initialize(<user_id>,<resp_id>,<app_id>); -- pass in user_id, responsibility_id, and application_id

 oe_msg_pub.initialize;

 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data

 dbms_output.put_line('START OF NEW DEBUG');

--This is to UPDATE order line

 l_line_tbl_index :=1; -- First Existing Line Update

-- Changed attributes

 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

 l_header_rec.header_id := <header_id>; -- header_id of the order

 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE; --** For the Existing line Operation is Update

 l_line_tbl(l_line_tbl_index).split_by := <user_id>; -- Pass user_id who is splitting the line

 l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index).header_id := <header_id>; -- header_id of the order

 l_line_tbl(l_line_tbl_index).line_id := <line_id>; -- line_id of the order line which needs to split

 l_line_tbl(l_line_tbl_index).ordered_quantity := <order_qty>; -- new ordered quantity of the line which you want to split (Original qty = 100 and now want to split as 70 and 30)

 l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- Enter the reason code for this Split

 l_line_tbl_index :=2; --For New Split Line

 l_line_tbl(l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index ).operation := OE_GLOBALS.G_OPR_CREATE; --** For the Splited line Operation is Create

 l_line_tbl(l_line_tbl_index ).split_by := 'USER'; -- Enter user(Bug 25793299)

 l_line_tbl(l_line_tbl_index ).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index ).split_from_line_id := <line_id>; -- line_id of  original line from which the line is splitted

 l_line_tbl(l_line_tbl_index ).inventory_item_id := <item_id>; -- inventory item id

 l_line_tbl(l_line_tbl_index ).ordered_quantity := 30; -- Enter the remaining qty after Split in ordered quantity (Enter 30 as you entered as 70 at top)

-- CALL TO PROCESS ORDER

 OE_ORDER_PUB.process_order (

  p_api_version_number => 1.0

  , p_init_msg_list => fnd_api.g_false

  , p_return_values => fnd_api.g_false

  , p_action_commit => fnd_api.g_false

  , x_return_status => l_return_status

  , x_msg_count => l_msg_count

  , x_msg_data => l_msg_data

  , p_header_rec => l_header_rec

  , p_line_tbl => l_line_tbl

  , p_action_request_tbl => l_action_request_tbl

-- OUT PARAMETERS

  , x_header_rec => p_header_rec

  , x_header_val_rec => x_header_val_rec

  , x_Header_Adj_tbl => x_Header_Adj_tbl

  , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl

  , x_Header_price_Att_tbl => x_Header_price_Att_tbl

  , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl

  , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl

  , x_Header_Scredit_tbl => x_Header_Scredit_tbl

  , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl

  , x_line_tbl => p_line_tbl

  , x_line_val_tbl => x_line_val_tbl

  , x_Line_Adj_tbl => x_Line_Adj_tbl

  , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl

  , x_Line_price_Att_tbl => x_Line_price_Att_tbl

  , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl

  , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl

  , x_Line_Scredit_tbl => x_Line_Scredit_tbl

  , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl

  , x_Lot_Serial_tbl => x_Lot_Serial_tbl

  , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl

  , x_action_request_tbl => l_action_request_tbl

 );

 dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages

 FOR i IN 1 .. l_msg_count

 LOOP

  Oe_Msg_Pub.get( p_msg_index => i

  , p_encoded => Fnd_Api.G_FALSE

  , p_data => l_msg_data

  , p_msg_index_out => l_msg_index_out);

  DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);

  DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);

 END LOOP;

-- Check the return status

 IF l_return_status = FND_API.G_RET_STS_SUCCESS

 THEN

  dbms_output.put_line('SO Line Qty Update Sucessful');

 ELSE

  dbms_output.put_line('AO Line Qty update Failed');

 END IF;

END;

/

Commit;

Oracle EBS: AR Un-Applied and On-Account Receipt Details Query

 SELECT

    *

FROM

    (

        SELECT

            business_unit,

            cash_receipt_id,

            customer_name,

            customer_number,

            party_site_number,

            receipt_number,

            receipt_date,

            currency_code,

            status,

            application_type,

            SUM(onacc_amt) onacc_amt,

            SUM(unapp_amt) unapp_amt,

            nvl((SUM(onacc_amt) + SUM(unapp_amt)), 0) zero_check

        FROM

            (

                SELECT

                    hou.name business_unit,

                    acra.cash_receipt_id,

                    rc.customer_name,

                    rc.customer_number,

                    rc.party_site_number,

                    acra.receipt_number,

                    acra.receipt_date,

                    acra.currency_code,

--    ( araa.amount_applied ) amount,

                    CASE

                        WHEN araa.status = 'ACC' THEN

                            araa.amount_applied

                        ELSE

                            0

                    END onacc_amt,

                    CASE

                        WHEN araa.status = 'UNAPP' THEN

                            araa.amount_applied

                        ELSE

                            0

                    END unapp_amt,

                    araa.status,

                    araa.application_type

--    ,    araa.gl_date

                FROM

                    apps.ar_cash_receipts_all             acra,

                    apps.ar_receivable_applications_all   araa,

                    apps.hr_operating_units               hou,

                    zacuser.bu_ra_customers_v             rc

                WHERE

                    acra.cash_receipt_id = araa.cash_receipt_id

                    AND araa.status IN (

                        'ACC',

                        'UNAPP'

                    )

                    AND hou.organization_id = acra.org_id

                    AND acra.pay_from_customer = rc.customer_id

                    AND rc.bill_to_site_use_id = acra.customer_site_use_id

                    AND acra.org_id IN (

                        1022,

                        1099,

                        1100

                    )

            )

        GROUP BY

            business_unit,

            cash_receipt_id,

            customer_name,

            customer_number,

            party_site_number,

            receipt_number,

            receipt_date,

            currency_code,

            status,

            application_type

    )

WHERE

-- (ONACC_AMT+UNAPP_AMT) <> 0

    zero_check <> 0

ORDER BY

    business_unit,

    customer_name,

    receipt_number;

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