Oracle Forms Personalization ALTERABLE (APPLICATION COVER) Property

The ALTERABLE (APPLICATION COVER) property is used to allow or disallow INSERT and UPDATE activities on a specific instance of an item regardless of the activity (new record or update record) being performed on the row.

Example:
Let’s use the Sales Order form to understand this property better.
Suppose we need to make Payment Terms field of Sales Order form to be non-editable to restrict users from updating the Payment Terms.

In order to achieve the above requirement, you may use the ALTERABLE (APPLICATION COVER) property as below.

– Open the Forms Personalization window by clicking on the Help -> Diagnostics -> Custom Code -> Personalize menu option.
o Seq – 10
o Description – Payment Terms Read Only
o Level – Function
o Enabled – Yes

– Condition:
o Trigger Event – WHEN-NEW-ITEM-INSTANCE
o Trigger Object – ORDER.TERMS
o Condition – 
o Processing Mode – Not in Enter-Query Mode

– Actions:

o Seq – 10

o Type – Property

o Description – Payment Terms Read-only

o Language – All

o Enabled – Yes

o Object Type – Item

o Target Object – ORDER.TERMS

o Property Name – ALTERABLE (APPLICATIONS COVER)

o Value – False

Save the record.

DML for opening a 'Finally Closed' PO in Oracle Apps

UPDATE po_headers_all 
SET    closed_code = 'OPEN' --FINALLY CLOSED
WHERE  segment1 = '<PO_NUMBER>';

UPDATE po_lines_all 
SET    closed_flag=NULL, 
       closed_code = 'OPEN', --FINALLY CLOSED 
       closed_date = NULL,   --01-AUG-19 
       closed_reason = NULL, --NULL 
       closed_by = NULL     --1583 
where  po_line_id = <PO_LINE_ID>;

UPDATE po_line_locations_all 
SET    closed_code = 'OPEN',             --FINALLY CLOSED 
       closed_date = NULL,               --01-AUG-19 
       closed_by = NULL,                 --1583 
       closed_for_receiving_date = NULL, --01-AUG-19 
       closed_for_invoice_date = NULL    --01-AUG-19 
WHERE  po_line_id = <PO_LINE_ID>;

BOM API ( Bom_Bo_Pub.process_bom ) : How Bills of Material (BOMs) can be created using APIs

Create a staging table :

CREATE TABLE xx_bom_stg 
  ( 
     org_code     VARCHAR2(30), 
     asly_code    VARCHAR2(240), 
     comp_code    VARCHAR2(240), 
     qty          NUMBER, 
     subinventory VARCHAR2(100), 
     uom          VARCHAR2(30), 
     status       VARCHAR2(30), 
     status_msg   VARCHAR2(2000) 
  ); 

Insert the data into this staging table and then execute below procedure :
CREATE OR REPLACE PROCEDURE xx_bom_api 
AS 
  -- API input variables  
  l_bom_header_rec         bom_bo_pub.bom_head_rec_type := 
  bom_bo_pub.g_miss_bom_header_rec; 
  l_bom_revision_tbl       bom_bo_pub.bom_revision_tbl_type := 
  bom_bo_pub.g_miss_bom_revision_tbl; 
  l_bom_component_tbl      bom_bo_pub.bom_comps_tbl_type := 
  bom_bo_pub.g_miss_bom_component_tbl; 
  l_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type := 
  bom_bo_pub.g_miss_bom_ref_designator_tbl; 
  l_bom_sub_component_tbl  bom_bo_pub.bom_sub_component_tbl_type := 
  bom_bo_pub.g_miss_bom_sub_component_tbl; 
  -- API output variables  
  x_bom_header_rec         bom_bo_pub.bom_head_rec_type := 
  bom_bo_pub.g_miss_bom_header_rec; 
  x_bom_revision_tbl       bom_bo_pub.bom_revision_tbl_type := 
  bom_bo_pub.g_miss_bom_revision_tbl; 
  x_bom_component_tbl      bom_bo_pub.bom_comps_tbl_type := 
  bom_bo_pub.g_miss_bom_component_tbl; 
  x_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type := 
  bom_bo_pub.g_miss_bom_ref_designator_tbl; 
  x_bom_sub_component_tbl  bom_bo_pub.bom_sub_component_tbl_type := 
  bom_bo_pub.g_miss_bom_sub_component_tbl; 
  x_message_list           error_handler.error_tbl_type; 
  l_error_table            error_handler.error_tbl_type; 
  l_output_dir             VARCHAR2(500) := '/usr/tmp/test'; 
  l_debug_filename         VARCHAR2(60) := 'test_debug.dbg'; 
  l_return_status          VARCHAR2(1) := NULL; 
  l_msg_count              NUMBER := 0; 
  l_cnt                    NUMBER := 1; 
  -- WHO columns  
  l_user_id                NUMBER := -1; 
  l_resp_id                NUMBER := -1; 
  l_application_id         NUMBER := -1; 
  l_row_cnt                NUMBER := 1; 
  l_user_name              VARCHAR2(30) := 'TECHNICAL2'; 
  l_resp_name              VARCHAR2(30) := 'BILLS OF MATERIAL'; 
  l_item_seq               NUMBER; 
BEGIN 
    -- Get the user_id  
    SELECT user_id 
    INTO   l_user_id 
    FROM   fnd_user 
    WHERE  user_name = l_user_name; 

    -- Get the application_id and responsibility_id  
    SELECT application_id, 
           responsibility_id 
    INTO   l_application_id, l_resp_id 
    FROM   fnd_responsibility 
    WHERE  responsibility_key = l_resp_name; 

    -- intiialize applications information  
    fnd_global.Apps_initialize(l_user_id, l_resp_id, l_application_id); 
    -- Mfg / Mfg & Dist Mgr / INV  

    dbms_output.Put_line('Initialized applications context: ' 
                         || l_user_id 
                         || ' ' 
                         || l_resp_id 
                         ||' ' 
                         || l_application_id); 

    FOR ch IN (SELECT DISTINCT asly_code, 
                               comp_code, 
                               org_code 
               FROM   xx_bom_stg A, 
                      mtl_parameters B, 
                      mtl_system_items C, 
                      mtl_system_items d 
               WHERE  A.org_code = B.organization_code 
                      AND A.asly_code = C.segment1 
                      AND B.organization_id = C.organization_id 
                      AND a.comp_code = d.segment1 
                      AND d.organization_id = C.organization_id 
                      --AND A.ASLY_CODE='PACKTC1D0003' 
                      AND C.bom_enabled_flag = 'Y' 
                      --and d.BOM_ENABLED_FLAG='N' 
                      AND NOT EXISTS (SELECT 1 
                                      FROM   bom_bill_of_materials X 
                                      WHERE 
                              X.assembly_item_id = C.inventory_item_id 
                              AND X.organization_id = C.organization_id) 
              --  and not exists (select 1 from bom_inventory_components x where x.COMPONENT_ITEM_ID=d.inventory_item_id and x.PK2_VALUE=C.ORGANIZATION_ID)
              ) LOOP 
        l_bom_revision_tbl.DELETE; 

        l_bom_component_tbl.DELETE; 

        l_bom_ref_designator_tbl.DELETE; 

        l_bom_sub_component_tbl.DELETE; 

        x_bom_revision_tbl.DELETE; 

        x_bom_component_tbl.DELETE; 

        x_bom_ref_designator_tbl.DELETE; 

        x_bom_sub_component_tbl.DELETE; 

        -- initialize BOM header  
        l_bom_header_rec := bom_bo_pub.g_miss_bom_header_rec; 

        l_bom_revision_tbl := bom_bo_pub.g_miss_bom_revision_tbl; 

        l_bom_component_tbl := bom_bo_pub.g_miss_bom_component_tbl; 

        l_bom_ref_designator_tbl := bom_bo_pub.g_miss_bom_ref_designator_tbl; 

        l_bom_sub_component_tbl := bom_bo_pub.g_miss_bom_sub_component_tbl; 

        -- API output variables  
        x_bom_header_rec := bom_bo_pub.g_miss_bom_header_rec; 

        x_bom_revision_tbl := bom_bo_pub.g_miss_bom_revision_tbl; 

        x_bom_component_tbl := bom_bo_pub.g_miss_bom_component_tbl; 

        x_bom_ref_designator_tbl := bom_bo_pub.g_miss_bom_ref_designator_tbl; 

        x_bom_sub_component_tbl := bom_bo_pub.g_miss_bom_sub_component_tbl; 

        l_bom_header_rec.assembly_item_name := ch.asly_code; 

        l_bom_header_rec.organization_code := ch.org_code; 

        l_bom_header_rec.assembly_type := 1; 

        l_bom_header_rec.transaction_type := 'CREATE'; 

        l_bom_header_rec.return_status := NULL; 

        l_cnt := 0; 

        l_item_seq := 0; 

        FOR cl IN (SELECT A.comp_code, 
                          A.uom, 
                          A.org_code, 
                          A.subinventory, 
                          A.qty 
                   FROM   xx_bom_stg A, 
                          mtl_parameters B, 
                          mtl_system_items C 
                   WHERE  A.org_code = B.organization_code 
                          AND A.comp_code = C.segment1 
                          AND B.organization_id = C.organization_id 
                          AND A.asly_code = ch.asly_code 
                          AND A.org_code = ch.org_code) LOOP 
            l_cnt := l_cnt + 1; 

            l_item_seq := l_item_seq + 10; 

            -- initialize BOM components  
            -- component 1  
            L_bom_component_tbl (l_cnt).organization_code := ch.org_code; 

            L_bom_component_tbl (l_cnt).assembly_item_name := ch.asly_code; 

            L_bom_component_tbl (l_cnt).start_effective_date := SYSDATE; 
            -- to_date('16-JUL-2010 19:30:39','DD-MON-YY HH24:MI:SS'); -- should match timestamp for UPDATE  

            L_bom_component_tbl (l_cnt).component_item_name := cl.comp_code; 

            L_bom_component_tbl (l_cnt).alternate_bom_code := NULL; 

            L_bom_component_tbl (l_cnt).supply_subinventory := cl.subinventory; 

            L_bom_component_tbl (l_cnt).location_name := NULL; 
            -- '6.6.6..';  -- provide concatenated segments for locator  

            L_bom_component_tbl (l_cnt).comments := 'Created from BOM API'; 

            L_bom_component_tbl (l_cnt).item_sequence_number := l_item_seq; 

            L_bom_component_tbl (l_cnt).operation_sequence_number := l_item_seq; 

            L_bom_component_tbl (l_cnt).transaction_type := 'CREATE'; 

            L_bom_component_tbl (l_cnt).quantity_per_assembly := cl.qty; 

            L_bom_component_tbl (l_cnt).return_status := NULL; 
        --- initialize error stack for logging errors  
        END LOOP; 

        error_handler.initialize; 

        -- call API to create / update bill   
        --         DBMS_OUTPUT.PUT_LINE('=======================================================');  
        --         DBMS_OUTPUT.PUT_LINE('Calling Bom_Bo_Pub.process_bom API');     
        bom_bo_pub.Process_bom (p_bo_identifier => 'BOM', 
        p_api_version_number => 1.0, 
        p_init_msg_list => TRUE, p_bom_header_rec => l_bom_header_rec, 
        p_bom_revision_tbl => l_bom_revision_tbl, 
        p_bom_component_tbl => l_bom_component_tbl, 
        p_bom_ref_designator_tbl => l_bom_ref_designator_tbl, 
        p_bom_sub_component_tbl => l_bom_sub_component_tbl, 
        x_bom_header_rec => x_bom_header_rec, 
        x_bom_revision_tbl => x_bom_revision_tbl, 
        x_bom_component_tbl => x_bom_component_tbl, 
        x_bom_ref_designator_tbl => x_bom_ref_designator_tbl, 
        x_bom_sub_component_tbl => x_bom_sub_component_tbl, 
        x_return_status => l_return_status, x_msg_count => l_msg_count, 
        p_debug => 'N' 
        --,  
        --                       p_output_dir                  => l_output_dir,  
        --                     p_debug_filename              => l_debug_filename  
        ); 

        --      DBMS_OUTPUT.PUT_LINE('=======================================================');  
        dbms_output.Put_line('Return Status: ' 
                             ||l_return_status); 

        /*    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
       
                  dbms_output.put_line('x_msg_count:' || l_msg_count);
                   
                   Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
                   DBMS_OUTPUT.PUT_LINE('Error Message Count :'||l_error_table.COUNT);
                   FOR i IN 1..l_error_table.COUNT LOOP
                     DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).entity_index||':'||l_error_table(i).table_name);
                     DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).message_text);
                   END LOOP;          
                 
              END IF;
             
        */ 
        --      DBMS_OUTPUT.PUT_LINE('=======================================================');  
        COMMIT; 
    END LOOP; 
EXCEPTION 
  WHEN OTHERS THEN 
             dbms_output.Put_line('Exception Occured :'); 

             dbms_output.Put_line(SQLCODE 
                                  ||':' 
                                  ||SQLERRM); 

dbms_output.Put_line('======================================================='); 

RAISE; 
END;