Oracle Item creation API & Child Org Assignment

 Declare

    -- 1. Use the standard Oracle API collection types

       l_item_tbl      EGO_ITEM_PUB.Item_Tbl_Type;

       x_item_tbl      EGO_ITEM_PUB.Item_Tbl_Type;

       l_error_tbl     Error_Handler.Error_Tbl_Type;

       --l_item_rec      EGO_ITEM_PUB.Item_Rec_Type;

       -- Standard API tracking variables

       x_return_status VARCHAR2(1);

       x_msg_count     NUMBER;

  TYPE l_input_stg is TABLE OF XXKT_ALT_ITEMINFO_STG%rowtype;

     l_input_stg_row     l_input_stg:=l_input_stg();

       

       l_error_msg         VARCHAR2(4000);

       l_idx               NUMBER := 0; -- Counter for loop initialization

       l_inventory_item_id NUMBER := 0;

       l_organization_id   NUMBER := 0;

        -- Metrics Tracker Variables

       l_total_processed   NUMBER := 0;

       l_total_completed   NUMBER := 0;

       l_total_failed      NUMBER := 0;

       l_xdff_rows_inserted NUMBER ;

       l_xdff_rows_updated NUMBER ;

        -- Error Processing Local Variables

        l_msg_index_out     NUMBER;

        l_entity_index      NUMBER;

        l_message_text      VARCHAR2(4000);

Begin

     OPEN c_staging_data ;

      LOOP

        FETCH c_staging_data 

        BULK COLLECT INTO l_input_stg_row LIMIT 100;

        l_total_processed := l_total_processed + l_input_stg_row.COUNT;

        

        IF l_input_stg_row.COUNT > 0 THEN

          -- Clear API array structure for the current batch chunk

            l_item_tbl.DELETE;

            l_idx := 0;      

        FOR i IN 1..l_input_stg_row.count

         LOOP               --FOR r IN c_staging_data LOOP

            l_idx := l_idx + 1;

          --variable assignments

           --l_item_tbl(l_idx) := INV_ITEM_GRP.G_MISS_ITEM_REC; --recheck

                l_item_tbl(l_idx).Transaction_Type           := l_input_stg_row(i).TRANSACTION_TYPE;

                l_item_tbl(l_idx).Segment1                   := l_input_stg_row(i).ITEM_NUMBER;

                l_item_tbl(l_idx).Organization_Code          := l_input_stg_row(i).ORGANIZATION_CODE;

                l_item_tbl(l_idx).Organization_Id            := l_input_stg_row(i).ORGANIZATION_ID;               

            

                -- Mappings (Intercept missing columns and fall back to G_MISS Text/Numeric fields using NVL to protect Master inheritance

                l_item_tbl(l_idx).Description                := NVL(l_input_stg_row(i).DESCRIPTION, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Long_Description           := NVL(l_input_stg_row(i).LONG_DESCRIPTION, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Primary_Uom_Code           := NVL(l_input_stg_row(i).PRIMARY_UOM_CODE, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Inventory_Item_Status_Code := NVL(l_input_stg_row(i).INVENTORY_ITEM_STATUS_CODE, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).planner_code               := NVL(l_input_stg_row(i).PLANNER_CODE, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Default_Shipping_Org       := NVL(l_input_stg_row(i).DEFAULT_SHIPPING_ORG_ID, EGO_ITEM_PUB.G_MISS_NUM);

                l_item_tbl(l_idx).ACCOUNTING_RULE_ID         := NVL(l_input_stg_row(i).ACCOUNTING_RULE_ID, EGO_ITEM_PUB.G_MISS_NUM);                

                                                

                -- DFF Mappings

                l_item_tbl(l_idx).Attribute1                 := NVL(l_input_stg_row(i).ATTRIBUTE1, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Attribute13                := NVL(l_input_stg_row(i).ATTRIBUTE13, EGO_ITEM_PUB.G_MISS_CHAR);

                l_item_tbl(l_idx).Attribute14                := NVL(l_input_stg_row(i).ATTRIBUTE14, EGO_ITEM_PUB.G_MISS_CHAR);

                

            -- Handle Master-Controlled ITEM_TYPE 

            -- If creating a child record, it MUST inherit the Master's type forcing new value at the child level when it is Master-Controlled will cause failure.

                IF l_input_stg_row(i).TRANSACTION_TYPE = 'CREATE' AND l_input_stg_row(i).ORGANIZATION_CODE <> 'M1' THEN                            

                    -- Let it inherit from the Master record by passing G_MISS for the child orgs                    

                    l_item_tbl(l_idx).ITEM_TYPE                  := EGO_ITEM_PUB.G_MISS_CHAR;               

                ELSE    --For  Im1 create, update and child update take input value

                    l_item_tbl(l_idx).ITEM_TYPE                  := NVL(l_input_stg_row(i).ITEM_TYPE_CODE, EGO_ITEM_PUB.G_MISS_CHAR);                    

                END IF;  

                    --causing error for child item API

                    l_item_tbl(l_idx).receipt_days_exception_code:= EGO_ITEM_PUB.G_MISS_CHAR;

                    l_item_tbl(l_idx).auto_lot_alpha_prefix      := EGO_ITEM_PUB.G_MISS_CHAR;

                    l_item_tbl(l_idx).receiving_routing_id       := EGO_ITEM_PUB.G_MISS_NUM;

                    l_item_tbl(l_idx).invoice_close_tolerance    := EGO_ITEM_PUB.G_MISS_NUM;

                    l_item_tbl(l_idx).receive_close_tolerance    := EGO_ITEM_PUB.G_MISS_NUM;                                 

                                                                                                                         

            END LOOP;

       -----------------------------------------------------------------------------

       -- LOOP 2: Execute API Call - for batch of 100

       -----------------------------------------------------------------------------

              Error_Handler.Initialize; 

            

              EGO_ITEM_PUB.PROCESS_ITEMS (

                 p_api_version   => 1.0,

                 p_init_msg_list => FND_API.G_TRUE,

                 p_commit        => FND_API.G_FALSE, -- Keeping false for transactional validation

                 p_item_tbl      => l_item_tbl,

                 x_item_tbl      => x_item_tbl,

                 x_return_status => x_return_status,

                 x_msg_count     => x_msg_count

              );

-- Scenario A: Complete Batch Pass

            IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN

         -- Process successful elements and execute xDFF mappings

                FOR j IN 1..x_item_tbl.COUNT LOOP

                    l_inventory_item_id := x_item_tbl(j).inventory_item_id;

                    l_organization_id   := x_item_tbl(j).organization_id;

             -- Dynamically push memory model to 'C' status

                    l_input_stg_row(j).PROCESS_STATUS := 'C';

                    l_input_stg_row(j).ERROR_MSG      := 'Success';

                    l_total_completed                 := l_total_completed + 1;

                END LOOP;

         -- Scenario B: Batch Processing Error Enforced

            ELSE

                -- Default everything in this chunk chunk to failure first

                FOR j IN 1..l_input_stg_row.COUNT LOOP

                    l_input_stg_row(j).PROCESS_STATUS := 'E';

                    l_input_stg_row(j).ERROR_MSG      := 'Oracle Core API Processing Failure';

                    l_total_failed                    := l_total_failed + 1;

                END LOOP;

                -- Fetch detailed error messages mapped by index out of Oracle's global log stack

                Error_Handler.Get_Message_List(x_message_list => l_error_tbl);

                

                IF l_error_tbl.COUNT > 0 THEN

                    FOR k IN 1..l_error_tbl.COUNT LOOP

                        l_entity_index := l_error_tbl(k).entity_index;

                        l_message_text := l_error_tbl(k).message_text;

                        

                        -- Match specific error context text safely to correct layout row index line

                        IF l_entity_index BETWEEN 1 AND l_input_stg_row.COUNT THEN

                            l_input_stg_row(l_entity_index).ERROR_MSG := SUBSTR('[API Error]: ' || l_message_text, 1, 4000);

                            

                            FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line ' || l_input_stg_row(l_entity_index).LINE_NUMBER || 

                                              ' API Error: ' || l_message_text);

                        END IF;

                    END LOOP;

                END IF;

            END IF;

            -----------------------------------------------------------------

            -- Post-Execution Bulk Update Sync 

            -----------------------------------------------------------------

            FORALL m IN 1..l_input_stg_row.COUNT

                UPDATE XXKT_ALT_ITEMINFO_STG

                   SET PROCESS_STATUS    = l_input_stg_row(m).PROCESS_STATUS,

                       ERROR_MSG         = l_input_stg_row(m).ERROR_MSG,

                       LAST_UPDATE_DATE  = SYSDATE

                       --REQUEST_ID        = l_input_stg_row(m).REQUEST_ID--recheck

                 WHERE BATCH_ID          = P_BATCH_ID

                   AND ALT_ITEMINF_ID    = l_input_stg_row(m).ALT_ITEMINF_ID;

            COMMIT; -- Secure transaction isolation boundary for current chunk runtime execution

        END IF;

        --l_total_processed := l_total_processed + l_input_stg_row.COUNT; duplicate

        EXIT WHEN c_staging_data%NOTFOUND OR l_input_stg_row.COUNT = 0;

    END LOOP;

    CLOSE c_staging_data;

    -- Print records metrics summary to concurrent log in preferred style

    FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Core Item API Process Summary ---');

     

       -- 4. Print Process Metric Summary Counters to Concurrent Log Output

    --fnd_file.put_line(fnd_file.log, '==================================================');

    --fnd_file.put_line(fnd_file.log, '--- CALL_ITEM_API METRICS SUMMARY ---');

    fnd_file.put_line(fnd_file.log, 'Total Validated Rows Read:    ' || l_total_processed);

    fnd_file.put_line(fnd_file.log, 'API Successful Items:         ' || l_total_completed);

    fnd_file.put_line(fnd_file.log, 'API Transaction Failed Rows:  ' || l_total_failed);

    fnd_file.put_line(fnd_file.log, '==================================================');

 EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK;

        IF c_staging_data%ISOPEN THEN

            CLOSE c_staging_data;

        END IF;

        fnd_file.put_line(fnd_file.log, 'Fatal Failure Exception in CALL_ITEM_API: ' || SQLERRM);

        FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_STACK); 

        FND_FILE.PUT_LINE(FND_FILE.LOG,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

        RAISE;   

    END CALL_ITEM_API;

Note: 

In order to avoid Master Child API error as shown below, do not enter any item attributes that are master controlled while creation or update of child orgs as inputs to the API

API error: 

TABLE NAME : MTL_SYSTEM_ITEMS_INTERFACE

COLUMN NAME : MASTER_CHILD_1G

MESSAGE NAME : INV_IOI_MASTER_CHILD_1G

ERROR MESSAGE : Master - Child Conflict in one of these Attributes: Action(RECEIPT_DAYS_EXCEPTION_CODE), Receipt Routing(RECEIVING_ROUTING_ID), Invoice Close Tolerance Percentage(INVOICE_CLOSE_TOLERANCE), Receipt Close Tolerance Percentage(RECEIVE_CLOSE_TOLERANCE), Starting Lot Prefix(AUTO_LOT_ALPHA_PREFIX), Description(DESCRIPTION), Long Description(LONG_DESCRIPTION).

Line 10 API Error: Master - Child Conflict in one of these Attributes: Action(RECEIPT_DAYS_EXCEPTION_CODE), Receipt Routing(RECEIVING_ROUTING_ID), Invoice Close Tolerance Percentage(INVOICE_CLOSE_TOLERANCE), Receipt Close Tolerance Percentage(RECEIVE_CLOSE_TOLERANCE), Starting Lot Prefix(AUTO_LOT_ALPHA_PREFIX), Description(DESCRIPTION), Long Description(LONG_DESCRIPTION).

Child Org Assignment API

EGO_ITEM_PUB.PROCESS_ITEMS can be used create/update of Master and child orgs, while below API is only used to assign/ create child org item when a master item already exists.

 EGO_ITEM_PUB.assign_item_to_org (

        p_api_version        => 1.0,

        p_init_msg_list      => FND_API.G_TRUE,

        p_commit             => FND_API.G_TRUE,

        p_inventory_item_id  => 123456,  -- Item ID

        p_organization_id    => 81,      -- Child Organization ID to assign to

        p_primary_uom_code   => 'EA',

        x_return_status      => x_return_status,

        x_msg_count          => x_msg_count,

        x_msg_data           => x_msg_data

    );