Oracle EBS: Setup for Auto Approve Purchase Requisition and Purchase Order

Subject: We need to do setup for auto approve PR and PO.

Step1: We need to check which Job is assigned to the PR/PO Preparare.

Navigation: Global HRMS Manager > People > Enter and Maintain > Search your User Name > Click on Assignment Button.

Note: IF the PR/PO Preparare not have registered in Global HRMS as an employee than He/She can not create PR/PR

Step2: Check the Oracle USer has assigned the Employee User or Not.

Navigation: System Administrator > Security > User > Define > Search with User Name.

We can check the Global HRMS person assigned to Oracle User. If not assigned please assign it.

Step3: Need to assign User Job for Approval Assignment.

Navigation: Purchasing Super User > Setup > Approval > Approval Assignment > Search with Job.

Add the document type for 'Approve Purchase Requsition' & 'Approve Standard Purchase Order'

Now prepare PR and PO it will automatically be approved after creation. 

Oracle PLSQL : How to print error/success transaction detail in log and output file of the concurrent program?

 Subject: How to print error/success transaction detail in log and output file of the concurrent program.

Resolution:

We can use the below syntax in plsql code to generate log and output file with transaction details. User can append any number of transaction fields in the same syntax line using pipe seperator.

--FND Log--

FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: '||l_error);

--FND Output--

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,xxtlm_rec.inventory_item_id||'  Item Updated Successfully');

Oracle Fusion: How to get Fixed Asset Major and Minor Category Description

 --Major Category --

SELECT fc.segment1,(SELECT 

    ffvt.description

FROM fnd_flex_value_sets ffvs ,

    fnd_flex_values ffv ,

    fnd_flex_values_tl ffvt

WHERE

    ffvs.flex_value_set_id     = ffv.flex_value_set_id

    and ffv.flex_value_id      = ffvt.flex_value_id

    AND ffvt.language          = USERENV('LANG')

AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS'  --Enter Major Category Value Set Name

AND ffv.flex_value=fc.segment1) DESC

FROM fa_categories_vl

--Minor Category--

SELECT fc.segment1,(SELECT 

    ffvt.description

FROM fnd_flex_value_sets ffvs ,

    fnd_flex_values ffv ,

    fnd_flex_values_tl ffvt

WHERE

    ffvs.flex_value_set_id     = ffv.flex_value_set_id

    and ffv.flex_value_id      = ffvt.flex_value_id

    AND ffvt.language          = USERENV('LANG')

AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS' --Enter Minor Category Value Set Name

AND ffv.flex_value=fc.segment2) DESC

FROM fa_categories_vl

Oracle EBS: SQL Query to Get Profile Options

 SELECT DISTINCT

    pot.profile_option_name            "PROFILE_CODE",

    pot.user_profile_option_name       "PROFILE_NAME",

    decode(a.level_id, 10001, 'Site', 10002, 'Application',

           10003,

           'Responsibility',

           10004,

           'User',

           10005,

           'Server',

           10006,

           'Organization',

           a.level_id) "LEVEL_IDENTIFIER",

    decode(a.level_id, 10002, e.application_name, 10003, c.responsibility_name,

           10004,

           d.user_name,

           10005,

           f.host

           || '.'

           || f.domain,

           10006,

           g.name,

           '-')        "LEVEL_NAME",

    decode(a.profile_option_value, '1', '1 (may be "Yes")', '2',

           '2 (may be "No")',

           a.profile_option_value) "PF_VALUE"

FROM

    fnd_application_tl         e,

    fnd_user                   d,

    fnd_responsibility_tl      c,

    fnd_profile_option_values  a,

    fnd_profile_options        b,

    fnd_profile_options_tl     pot,

    fnd_nodes                  f,

    hr_all_organization_units  g

WHERE

        1 = 1

--AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')

    AND pot.profile_option_name = b.profile_option_name

    AND b.application_id = a.application_id (+)

    AND b.profile_option_id = a.profile_option_id (+)

    AND a.level_value = c.responsibility_id (+)

    AND a.level_value = d.user_id (+)

    AND a.level_value = e.application_id (+)

    AND a.level_value = f.node_id (+)

    AND a.level_value = g.organization_id (+)

    AND pot.language = 'US'

--AND POT.User_profile_option_name ='AHL: Application Usage Mode'

ORDER BY

    profile_name,

    level_identifier,

    level_name,

    pf_value;

Oracle ERP : Issue Resolution : ORA-29280: invalid directory object

Subject: When we are bursting or creating a .csv file using PLSQL procedure and try to send it on FTP directory or try to open the file from the FTP directory path we are getting the below error message.

Error:

ORA-29280: invalid directory object

Solution:

Oracle suggests using the directory path which is defined under utl_file_dir. the application allows accessing the file from the FTP server.

Use the below query to get UTL FILE Directory path

select * from  V$PARAMETER where NAME like '%utl_file_dir%'

Set path: /s01/oracle/DEV/db/19.3.0/temp/DEV  (at a time run concurrent request)

After setting the suggested path run the concurrent request. The file was generated successfully.

Oracle Fusion: How to Create Inventory Sub-Inventory Transfer Transaction

Subject: Business required to move on-hand stock from one sub inventory to another sub-inventory for that we need to create a sub-inventory transfer.

--Item Details--

Item Code: 04081006.1

Organization: DHY

Sub-Inventory1: C1-R2-150-A-G05

Sub-Inventory2: C2-R2-170-A-G09

Let me check the Item stock before creating the transaction against both sub-inventory.

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management

Click on the right-side panel Task Button > Show Task "Inventory" > Manage Item Quantities.

Sub-Inventory1: G05
Locator: C1-R2-150-A-G05

QTY: 11

Sub-Inventory2: G09

Locator: C2-R2-170-A-G09 

QTY: 8

Let's create the sub-inventory transfer from G05 to G09

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management > Select Task > Show Task "Inventory" > Create Subinventory Transfer.

Enter all the required details.

Date: It must be within the open inventory accounting period.

Type: Subinventory Transfer

Item: 04081006.1

--Source--

Sub-Inventory: G05
Locator: C1-R2-150-A-G05

--Destination--

Sub-Inventory: G09

Locator: C2-R2-170-A-G09 

Qty: 1

Reason: Stock Pick

After entering all the details click on Submit button. 

Now let's check on hand in both sub-inventory locators.

Click on the right-side panel Task Button > Show Task "Inventory" > Manage Item Quantities.

Sub-Inventory1: G05
Locator: C1-R2-150-A-G05

QTY: 10

Sub-Inventory2: G09

Locator: C2-R2-170-A-G09 

QTY: 9

Sun-inventory transfer transaction completed successfully. 

Oracle Fusion: How to create Miscellaneous Transaction to add item on-hand stock

Subject: We need to create onhand for items using Oracle Fusion Miscellaneous Transaction Screen.

Let me check the Item stock before creating the transaction.

--Item Details--

Item Code: 341-01-0018

Organization: JV2

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management

Click on the right-side panel Task Button > Show Task "Inventory" > ManageItem Quantities.

Search with Item Code and Organization.

We can see that Item having Onhand Quantity 100.

Let's Increase the onhand Quantity using Miscellaneous Transaction.

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management > Select Task > Show Task "Inventory" > Create Miscellaneous Transaction

Entered highlighted all Details. Must take care date within Open Inventory Accounting Period

Type: Miscellaneous Receipts

Account: Inventory Material Account

Use Current Item Cost: Yes

After entering all these details, click on edit details.

We must be required to enter the serial numbers for serialized Items. 

After entering the serial number click on Ok.

Now Click on Submit.

Miscellaneous Transaction Created successfully.

Let's Check the Onhnad for the same Item again.

Navigation: Oracle Home Page > Supply Chain Execution > Inventory Management

Click on Task Button > Show Task "Inventory" > ManageItem Quantities.

Search with Item Code and Organization.

We can see that Item having an Onhand Quantity of 250.

Onhand created successfully.

Oracle EBS - How to upload bulk Material Transaction Reason

Subject: How to upload bulk Material Transaction Reason in Oracle EBS.

Pre-requisite: We need to store Reason Name and Description fields Values in a temporary table for bulk data.

EBS Navigation: Iventory > Setup > Transactions > Reasons

--Base Table:-- MTL_TRANSACTION_REASONS

--PLSQL Scripts--

DECLARE

    v_rowid   NUMBER;

    v_rowid1  VARCHAR2(1000);

BEGIN

    fnd_global.apps_initialize(user_id => 25665, resp_id => 52895, resp_appl_id => 0);

    fnd_msg_pub.initialize;

    FOR i IN (

        SELECT

            *

        FROM

            temp_lookup_tbl

        WHERE

            lookup_type = 'MTL_TRANSACTION_REASON' 

--                and MEANING in ('PS-Metallicplate of switch-127','PS-Metallicplate of switch-159')

--                and rownum=1

    ) LOOP

        INSERT INTO mtl_transaction_reasons (

            reason_id,

            last_update_date,

            last_updated_by,

            creation_date,

            created_by,

            last_update_login,

            reason_name,

            description,

            attribute2,

            reason_type,

            reason_type_display

        ) VALUES (

            mtl_transaction_reasons_s.NEXTVAL,

            sysdate,

            - 1,

            sysdate,

            - 1,

            - 1,

            i.meaning,

            i.description,

            i.org,

            8,

            'QA Update Status'

        );

    END LOOP;

    COMMIT;

END;

Oracle EBS: How to create Miscellaneous Transaction to add item on-hand stock

 Subject: We need to create onhand for items using Miscellaneous Transaction Screen.

Let me check the Item stock before creating the transaction.

Let's create Miscellaneous Transaction to add on hand for this item.

Navigate to Inventory Responsibility > Transactions > Miscellaneous Transactions

Enter the details.

Enter the all details.

Let's check on hand.

Onhand created successfully.

Oracle Fusion: Universal Content Management (UCM)

Oracle Fusion Universal Content Management (UCM) Overview:

Oracle Fusion: Universal Content Management (UCM)

Realtime Demo:

Oracle Fusion: Universal Content Management (UCM): Realtime Demo

Oracle Fusion Application Composer: How to find base tables for Custom Objects

Subject: How to get a fusion base table for custom objects.

(To Learn More About Application Composer Visit Oracle Fusion: Application Composer)

There is 2 way to find custom object base tables.

1) Using Backend SQL Query Approach:

/*--Need to pass custom object api name--*/ 

/*--Get Table ID--*/

select TABLE_ID

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%customobject_api_name%';

/*--Need to pass custom Table Id from the above query--*/ 

/*--Get Table Name--*/

select TABLE_NAME 

from fusion.adf_extensible_table where TABLE_ID = P_TABLE_ID;

/*--Combining above 2 query--*/ 

/*--Need to pass custom object api name only--*/ 

/*--Get Table Name--*/

select TABLE_NAME 

from fusion.adf_extensible_table 

where TABLE_ID IN (select TABLE_ID

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%PO_REF%');

2) Using Metadata Manager Approach:

Navigation: 

Application Composer > Metadata Manager > Click on Generate > After Successfully Run > Click on Export.

Open the excel sheet. It is having all the custom object summaries.

Click on any custom object it will open the detailed mapping for that custom object. Let's click on the Bank_c custom object.

It's having all the custom object fields with base table mapping.

Hereby, we covered both approach to find custom objects and their fields mapping.

Oracle EBS Function: Get Inventory Item On-Hand Quantity

CREATE OR REPLACE FUNCTION xx_onhand_qty (

    p_inv_item_id  IN  VARCHAR2,

    p_org_id       IN  NUMBER,

    p_sub_inv      IN  VARCHAR2,

    p_loc_id       IN  NUMBER

) RETURN NUMBER IS

    x_return_status        VARCHAR2(50);

    x_msg_count            VARCHAR2(50);

    x_msg_data             VARCHAR2(50);

    v_item_id              NUMBER;

    v_organization_id      NUMBER;

    v_qoh                  NUMBER;

    v_rqoh                 NUMBER;

    v_atr                  NUMBER;

    v_att                  NUMBER;

    v_qr                   NUMBER;

    v_qs                   NUMBER;

    v_lot_control_code     BOOLEAN;

    v_serial_control_code  BOOLEAN;

    l_qty                  NUMBER;

    l_qty_avai_reserve     NUMBER := 0;

    l_qty_avai_transact    NUMBER := 0;

    l_reserved_qty         NUMBER := 0;

BEGIN

    SELECT

        inventory_item_id,

        mp.organization_id

    INTO

        v_item_id,

        v_organization_id

    FROM

        mtl_system_items_b  msib,

        mtl_parameters      mp

    WHERE

            msib.inventory_item_id = p_inv_item_id

        AND msib.organization_id = mp.organization_id

        AND msib.organization_id = p_org_id; -- :organization_code;

    BEGIN

        SELECT

            nvl(SUM(reservation_quantity), 0)

        INTO l_reserved_qty

        FROM

            mtl_reservations

        WHERE

                inventory_item_id = p_inv_item_id

            AND organization_id = p_org_id

            AND subinventory_code = p_sub_inv

            AND locator_id = p_loc_id;

    EXCEPTION

        WHEN OTHERS THEN

            l_reserved_qty := 0;

            dbms_output.put_line('ERROR: ' || sqlerrm);

    END;

    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := false;

    v_serial_control_code := false;

    fnd_client_info.set_org_context(1);

    inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, p_init_msg_lst => 'F',

                                          x_return_status => x_return_status,

                                          x_msg_count => x_msg_count,

                                          x_msg_data => x_msg_data,

                                          p_organization_id => v_organization_id,

                                          p_inventory_item_id => v_item_id,

                                          p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,

                                          p_is_revision_control => false,

                                          p_is_lot_control => v_lot_control_code,

                                          p_is_serial_control => v_serial_control_code,

                                          p_revision => NULL,                          -- p_revision,

                                          p_lot_number => NULL,                        -- p_lot_number,

                                          p_lot_expiration_date => sysdate,

                                          p_subinventory_code => nvl(p_sub_inv, NULL),                 -- p_subinventory_code,

                                          p_locator_id => nvl(p_loc_id, NULL),                        -- p_locator_id,

                                          p_onhand_source => 3,

                                          x_qoh => v_qoh,                    -- Quantity on-hand

                                          x_rqoh => v_rqoh,         --reservable quantity on-hand

                                          x_qr => v_qr,

                                          x_qs => v_qs,

                                          x_att => v_att,               -- available to transact

                                          x_atr => v_atr                 -- available to reserve

                                          );

    l_qty := nvl(v_qoh, 0); --On Hand qty

    l_qty_avai_reserve := nvl(v_atr, 0); --Available to Reserve

    l_qty_avai_transact := nvl(v_att, 0); --Available to Transact

    RETURN nvl((l_qty_avai_transact - l_reserved_qty), 0);

    dbms_output.put_line('On-Hand Quantity: ' || v_qoh);

    dbms_output.put_line('Available to reserve: ' || v_atr);

    dbms_output.put_line('Quantity Reserved: ' || v_qr);

    dbms_output.put_line('Quantity Suggested: ' || v_qs);

    dbms_output.put_line('Available to Transact: ' || v_att);

EXCEPTION

    WHEN OTHERS THEN

        RETURN NULL;

        dbms_output.put_line('ERROR: ' || sqlerrm);

END xx_onhand_qty; 

/

--Testing Script: get onhand qty--

select XX_ONHAND_QTY(8334013,85,'ZONEA',147167) from dual;  --Item_id,Organization_id,Sub_inv,Locator_id

Oracle EBS - Procedure to create duplicate sales order based on existing sales order using Oracle API.

Requirement: We need to create duplicate Sales Order Based on Existing Sales Order using Oracle API.

CREATE OR REPLACE PROCEDURE xx_create_so_prc (

    p_order_num IN NUMBER

) AS

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

    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;

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

    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;

    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_debug_file                  VARCHAR2(100);

    l_line_tbl_index              NUMBER;

    l_msg_index_out               NUMBER(10);

    CURSOR c_hdr IS

    SELECT

        transactional_curr_code,

        sold_to_org_id,

        price_list_id,

        sold_from_org_id,

        salesrep_id,

        order_type_id

    FROM

        oe_order_headers_all

    WHERE

        order_number = p_order_num--1219754

        ;

    CURSOR c_line IS

    SELECT

        line_number,

        ordered_quantity,

        ship_from_org_id,

        inventory_item_id,

        schedule_ship_date

    FROM

        oe_order_lines_all

    WHERE

        header_id = (

            SELECT

                header_id

            FROM

                oe_order_headers_all

            WHERE

                order_number = p_order_num

        )--28755419

    ORDER BY

        line_number;

BEGIN

    fnd_global.apps_initialize(user_id => 26406, resp_id => 21623, resp_appl_id => 660);

    mo_global.init('ONT');

    mo_global.set_policy_context('S', 102);

    oe_msg_pub.initialize;

    oe_debug_pub.initialize;

    x_debug_file := oe_debug_pub.set_debug_mode('FILE');

    oe_debug_pub.setdebuglevel(5);

    FOR i IN c_hdr LOOP

        l_header_rec := oe_order_pub.g_miss_header_rec;

        l_header_rec.operation := oe_globals.g_opr_create;

        l_header_rec.transactional_curr_code := i.transactional_curr_code;

        l_header_rec.pricing_date := sysdate;

        l_header_rec.sold_to_org_id := i.sold_to_org_id;

        l_header_rec.price_list_id := i.price_list_id;

        l_header_rec.ordered_date := sysdate;

        l_header_rec.sold_from_org_id := i.sold_from_org_id;

        l_header_rec.salesrep_id := i.salesrep_id;

        l_header_rec.order_type_id := i.order_type_id;

        FOR j IN c_line LOOP

            l_line_tbl_index := 1;

            l_line_tbl(j.line_number) := oe_order_pub.g_miss_line_rec;

            l_line_tbl(j.line_number).operation := oe_globals.g_opr_create;

            l_line_tbl(j.line_number).ordered_quantity := j.ordered_quantity;

            l_line_tbl(j.line_number).ship_from_org_id := j.ship_from_org_id;

            l_line_tbl(j.line_number).inventory_item_id := j.inventory_item_id;

            l_line_tbl(j.line_number).schedule_ship_date := j.schedule_ship_date;

        END LOOP;

    END LOOP;

    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 => x_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('Order Header_ID : ' || x_header_rec.header_id);

    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 : ' || l_msg_data);

        dbms_output.put_line('message index : ' || 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('Order Created Successfull');

    ELSE

        dbms_output.put_line('Orcer Creation Failed');

    END IF;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Error: ' || sqlerrm);

END xx_create_so_prc;

/

--Testing Scripts:--

--Need to pass existing order number based on what we need to create a new order--

exec XX_CREATE_SO_PRC(1219754);  

--Get the latest created Sales Order Number--

select *

from(

select HEADER_ID,ORDER_NUMBER from 

oe_order_headers_all

where trunc(creation_date) =trunc(sysdate)

order by creation_date desc)

where rownum=1

;

Oracle EBS - How to add responsibility to user using PLSQL Script

 --Add responsibility

DECLARE

    v_user_name    VARCHAR2(30):= '&USER_NAME';

    lc_resp_appl_short_name   VARCHAR2(100)    := 'SYSADMIN';

    lc_responsibility_key          VARCHAR2(100)    := 'SYSTEM_ADMINISTRATOR';

    lc_security_group_key        VARCHAR2(100)    := 'STANDARD';

    ld_resp_start_date                DATE                        := TO_DATE(sysdate);

    ld_resp_end_date                 DATE                        := NULL;

 

BEGIN

     fnd_user_pkg.addresp

     (   username           => v_user_name,

        resp_app             => lc_resp_appl_short_name,

        resp_key             => lc_responsibility_key,

        security_group  => lc_security_group_key,

        description         => NULL,

        start_date           => ld_resp_start_date,

        end_date            => ld_resp_end_date

    );

 

COMMIT;

 

EXCEPTION

            WHEN OTHERS THEN

                        ROLLBACK;

                        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Oracle EBS - How to reset oracle application password using PLSQL script

 --Reset Password

DECLARE

  v_user_name    VARCHAR2(30):= '&USER_NAME';

  v_new_password VARCHAR2(30):= 'welcome';

  v_status       BOOLEAN;

BEGIN

  update fnd_user set user_guid = null where user_name = v_user_name;

 

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,

                                              newpassword => v_new_password

                                            );

  IF v_status =TRUE THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

Oracle EBS - Order Management Sales Order Cancelled Line Return Reason SQL Query

Requirement: We need to fetch cancelled sale order line return reason query.

Navigation: Order Management Responsibility > Sales Order > Action > Additional Information > Quantity Hitrory.

SQL Query:

 (select LISTAGG( FLV.MEANING, ',') WITHIN GROUP (ORDER BY  FLV.MEANING)

        from 

        OE_REASONS OER,

        fnd_lookup_values_vl FLV

        where 

        FLV.LOOKUP_TYPE=OER.REASON_TYPE

        AND FLV.LOOKUP_CODE=OER.REASON_CODE

        AND OER.HEADER_ID=OOHA.HEADER_ID  --Need to pass order header Id

        AND OER.ENTITY_ID=OOLA.LINE_ID        -- Need to pass order line Id

) return_reason_code