Oracle Fusion: Po Status is Closed for Receiving. PO not able to closed due to PO and Invoice amount is not matching.

 

Issue: PO Created, Received, Delivered, Partial Invoiced - PO Status is Closed For Receiving but not Closed. How to Close the PO.

Invoice Amount is not matching with PO amount. That’s why PO not closed.

Resolution:
Step1: Navigation: Procurement > My Receipt > Task > Manage Receipt > Search with PO Number.

Step2: Click on receipt and Select Correct Button.

Step3: Put the same Invoice Amount in Correct Quantity Field & Click on Submit

Step4: Open the PO > Click on Action > Click on Edit
Step5: Add the Header Description for Change Order and Edit the line amount with same Invoice Amount.
Step6: Click on Submit.
Step7: If change Order required Approval Than User need to complete the Approval Process After That PO Get closed Automatically.

Oracle Fusion - SQL Query to check error message while Change Purchase Order Failed while send for and approval

--SQL to Check PO Detail--

SELECT *

FROM FUSION.PO_HEADERS_ALL

WHERE SEGMENT1 = '900001114637' --ENTER PO NUMBER

--SQL to Check PO Version Details--

SELECT  CO_NUM,

      VERSION_ID

FROM    FUSION.PO_VERSIONS

WHERE   PO_HEADER_ID IN

      (SELECT PO_HEADER_ID FROM FUSION.PO_HEADERS_ALL WHERE SEGMENT1 = '900001114637' --ENTER PO NUMBER

  )

ORDER BY CREATION_DATE DESC

--SQL to Check PO Approval Error--

SELECT  TEXT_LINE

FROM    FUSION.PO_ONLINE_REPORT_TEXT

WHERE   VERSION_ID = 300000225949043 --ENTER THE 1ST VERSION ID RECEIVED FROM ABOVE QUERY

ORDER BY CREATION_DATE DESC

Oracle Fusion: How to create Approval Delegation Rules / Vacation Rule / Approval Delegation Rule

 

Step1: Click on Bell Icon

 

Click on Show all

Click on Worklist

 

Step2: Lets Create Delegation rule.

  Right Hand Side - Top Corner > Select Preference

-           Click on Other Rule

Se    Search the username for whom we need to created delegation rule.

-           Click on “+”

-           Enter Name

-           Select Checkbox – Execute rule between these dates.

-           Select Start and End Date

-           Select Task : Task Matching this conditions

-           Click on “+”

-           Search Approver and Select the Approver name which you want to delegate

-           Select the Radio Button Delegate To – Select the person whom we need to send approver during that period.

 

 

Person1- xyz@gmail.com  (who is on leave or vaction)

to

Person2 - abc@gmail.com (who will approve in absence of Person1)

 

Now going forward all the approval redirect to person2 till the end date which we selected.

Oracle Fusion: How to Create New Requisition Approval Rules

 

Step1:

Navigate to: 

Setup and Maintenance > Setup Select: Procurement > Search with Manage%Approval

 

Step2: Select Header Stage > Edit Rule

 

Step3: Click on “+” Icon

 

Step4: Enter the Rule Name, Set the Priority (High/Medium/Low) & Description

Step5: Enter Condition and Action.

 

Step6:

Click on Save.

Click on Deploy.

Lets check Newly created Approval Rule is working or not.

Step7: Create Requisition.

 

Step8: Click on Manage Approval.

We can able to see the new approval coming in the approval list.

Oracle Fusion: SQL Query to Get Reference Data Sets and their Assigned with Business Units

 SELECT fsa.reference_group_name,

         fs.set_id,

         fs.set_code,

         fs.set_name,

         fs.description,

         bu.bu_name

    FROM fnd_setid_assignments   fsa,

         fun_all_business_units_v bu,

         fnd_setid_sets_vl       fs

   WHERE     1 = 1

         AND fsa.determinant_type = 'BU'

         AND fsa.determinant_value = bu.bu_id

         AND fsa.set_id = fs.set_id

         AND fsa.reference_group_name LIKE 'PER%'

ORDER BY reference_group_name,

         set_code,

         set_name,

         bu_name

Oracle Fusion: ADFDI sheet is blank after opening

Subject: ADFDI spreadsheet is completely blank after opening

Resolution:

1.  If you are not seeing the desktop integrator then follow the action plan in following document:
   
1. How To Instal ADFdi | How to Edit Role and Privileges for ADFDi - Download Desktop Integrator
2. Make sure you have latest version of file integrator. You can download from installer from application Navigator >> Download Desktop Integration Installer.

Now Install the Software.

Open the Excel > File > Option > Add-ins > Select Manage Com Add-ins > Click on Go

Select Oracle ADF Desktop Integration Add-in for Excel . Click Ok.

Now Close the Excel and Reopen.

Now ADFDi Addins added in the excel. User Can Open the spreadsheet without any issue.

Oracle Fusion: Foreign Currency Purchase Requisitions Creation Issue due to the Daily Rate Conversion

Subject: Enter PR Line Currency Conversion Rate Issue.

Resolution:

Navigation: Setup and Maintenance > Finance Application > Search Manage Daily Rates.

Click on Daily Rates Tab.

Search From EUR to GBP Currency conversion details for the Sep22 period. We not found Spot Conversion Rate for the 15SEP2022. We need to create Conversion Rate for this date to resolve the issue.

 

Click on Spread Sheet Button.

If SpreadSheet not asking to connect with Oracle Application than Excel ADFDi Addins are missing.

User Need to Enable those Addins. Refer below link for enabling ADFDi Addin.

Oracle Fusion: ADFDI sheet is blank after opening

We need to connect spreadsheet with oracle instance. Click on Yes

Enter User Name and Password of Oracle Application and Login.

Enter the Spread Sheet Data For Conversion Rate. Click on Submit button from the Top Menu.

Click on Ok. It will Run Background Request.

It will process the data

 

It will show record successfully Inserted.

We can verify the data from Manage Daily Rate Screen. Now we can able to see the daily rates for 15SEP2022

 

Now we user try to create foreign currency requisition system will not through an error.

 

Oracle Fusion: SQL Query to get the User Assigned Roles Access

 SELECT

    pu.user_id,

    pu.username,

    ppnf.full_name,

    prdt.role_id,

    prdt.role_name,

    prd.role_common_name,

    prdt.description,

    to_char(pur.start_date, 'DD-MON-YYYY') role_start_date,

    to_char(pur.end_date, 'DD-MON-YYYY')   role_end_date,

    prd.abstract_role,

    prd.job_role,

    prd.data_role,

    prd.duty_role,

    prd.active_flag

FROM

    per_user_roles     pur,

    per_users          pu,

    per_roles_dn_tl    prdt,

    per_roles_dn       prd,

    per_person_names_f ppnf

WHERE

        1 = 1

    AND pu.user_id = pur.user_id

    AND prdt.role_id = pur.role_id

    AND prdt.language = userenv('lang')

    AND prdt.role_id = prd.role_id

    AND nvl(pu.suspended, 'N') = 'N'

    AND ppnf.person_id = pu.person_id

    AND ppnf.name_type = 'GLOBAL'

    AND pu.active_flag = 'Y'

    AND nvl(pu.start_date, sysdate) <= sysdate

    AND nvl(pu.end_date, sysdate) >= sysdate

    AND pu.username = 'cr@gmail.com' -- Enter the Username 

ORDER BY

    pu.username,

    prdt.role_name

Oracle Fusion: BPM - SQL Query to Get Approval Group Details

 SELECT 

FWG.APPROVALGROUPID ,

PPNF.DISPLAY_NAME EMP_NAME,

PAPF.PERSON_NUMBER EMP_NUM,

FWGM.MEMBER USER_NAME, 

FWGM.TYPE, 

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

FROM 

FA_FUSION_SOAINFRA.WFAPPROVALGROUPS FWG,

FA_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS FWGM,

PER_USERS PU,

PER_PERSON_NAMES_F PPNF,

PER_ALL_PEOPLE_F PAPF

WHERE 1=1

AND FWG.APPROVALGROUPID = FWGM.APPROVALGROUPID

AND FWGM.MEMBER=PU.USERNAME

AND PU.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE='GLOBAL'

AND PAPF.PERSON_ID=PPNF.PERSON_ID

AND PAPF.PERSON_NUMBER = NVL(:P_EMP_NUM,PAPF.PERSON_NUMBER)

AND FWGM.MEMBER=NVL(:P_USERNAME,FWGM.MEMBER)

GROUP BY

PPNF.DISPLAY_NAME ,

PAPF.PERSON_NUMBER ,

FWGM.MEMBER , 

FWGM.TYPE, 

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

ORDER BY PPNF.DISPLAY_NAME ,FWG.APPROVALGROUPNAME

Oracle Fusion: BPM - SQL Query to get Purchase Requisition Approval Rule

SELECT POR_AMX_RULES_TBL.rule_id,

       POR_AMX_RULES_TBL.rule_number,

       POR_AMX_RULES_TBL.rule_name,

       POR_AMX_RULES_TBL.DISPLAY_RULE_NAME,

       POR_AMX_RULES_TBL.description,

       POR_AMX_RULES_TBL.CONDITIONS_STRING,

       POR_AMX_RULES_TBL.PRIORITY,

       POR_AMX_RULES_TBL.active_flag,

       POR_AMX_TASKS_TBL.LOOKUP_CODE pat_LOOKUP_CODE,

       POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_KEY,

       POR_AMX_ACTIONS_TBL.ACTION_CODE,

       POR_AMX_ACTIONS_TBL.APPROVAL_GROUP_NAME,

   POR_AMX_ACTIONS_TBL.WORKER_ID,

   ppnf.DISPLAY_NAME EMP_NAME

FROM   POR_AMX_RULES POR_AMX_RULES_TBL,

       POR_AMX_PARTICIPANTS POR_AMX_PARTICIPANTS_TBL,

       POR_AMX_DIMENSIONS   POR_AMX_DIMENSIONS_TBL,

       POR_AMX_TASKS POR_AMX_TASKS_TBL,

       (select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj 

        from POR_AMX_RULES 

        group by RULE_NAME) rul_max,

       POR_AMX_STAGES   PAS,

       POR_AMX_ACTIONS POR_AMX_ACTIONS_TBL

   ,per_person_names_f ppnf

WHERE  POR_AMX_RULES_TBL.active_flag = 'Y'

   and POR_AMX_RULES_TBL.PARTICIPANT_ID = POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_ID

   and POR_AMX_RULES_TBL.task_id = POR_AMX_PARTICIPANTS_TBL.task_id

   and pas.STAGE_ID = POR_AMX_PARTICIPANTS_TBL.STAGE_ID

   and pas.task_id = POR_AMX_PARTICIPANTS_TBL.task_id

   and pas.stage_id = POR_AMX_PARTICIPANTS_TBL.stage_id

   and POR_AMX_TASKS_TBL.task_id = POR_AMX_DIMENSIONS_TBL.task_id

   and rul_max.max_obj = POR_AMX_RULES_TBL.OBJECT_VERSION_NUMBER

   and rul_max.max_rul_name = POR_AMX_RULES_TBL.rule_name

   and POR_AMX_ACTIONS_TBL.rule_id = POR_AMX_RULES_TBL.rule_id

   and POR_AMX_ACTIONS_TBL.task_id = POR_AMX_RULES_TBL.task_id

   and POR_AMX_TASKS_TBL.task_key = 'ReqApproval'

   and POR_AMX_DIMENSIONS_TBL.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL'

   AND POR_AMX_RULES_TBL.DISPLAY_RULE_NAME='UK Finance Manager Approval - Andrew Craggs'

   AND POR_AMX_ACTIONS_TBL.WORKER_ID=ppnf.person_id(+) -- Approval Rule not assigned Worker also need to fetch

GROUP BY

   POR_AMX_RULES_TBL.rule_id,

       POR_AMX_RULES_TBL.rule_number,

       POR_AMX_RULES_TBL.rule_name,

       POR_AMX_RULES_TBL.DISPLAY_RULE_NAME,

       POR_AMX_RULES_TBL.description,

       POR_AMX_RULES_TBL.CONDITIONS_STRING,

       POR_AMX_RULES_TBL.PRIORITY,

       POR_AMX_RULES_TBL.active_flag,

       POR_AMX_TASKS_TBL.LOOKUP_CODE ,

       POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_KEY,

       POR_AMX_ACTIONS_TBL.ACTION_CODE,

       POR_AMX_ACTIONS_TBL.APPROVAL_GROUP_NAME,

   POR_AMX_ACTIONS_TBL.WORKER_ID,

   ppnf.DISPLAY_NAME 

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: Interface Tables and Base Tables

 **********************

--General Ledger--

**********************

--Interface Table--

gl_interface

gl_budget_interface

--Base Table--

gl_je_batches

gl_je_headers

gl_je_lines

gl_je_sources

gl_je_categories

gl_sets_of_books

gl_daily_rates

gl_balances

gl_periods

gl_period_sets

gl_code_conbinations

 

**********************

--Receivables--

**********************

--Interface Table--

ra_customers_interface_all  

ra_contact_phones_int_all

ra_customer_profiles_int_all

--Base Table--

hz_cust_sit_use_all

hz_cust_acct_sites_all

hz_parties hz_cust_accounts

hz_party_sites

hz_locations

hz_party_site_uses

hz_customer_profiles

hz_organization_profiles

hz_person_profiles

 

--Invoice--

--Interface Table--

ra_interface_lines_all

ra_interface_distributions_all

ra_interface_salescredits_all

--Base Table--

ra_customer_trx_all

ra_customer_trx_lines_all

ra_cust_trx_line_gl_dist_all

ra_cust_trx_types_all

ar_payment_schedules_all

ra_batch_sources_All

ar_vat_tax_all

ra_terms ar_periods

ar_period_types

 

**********************

--Order Management--

**********************

--Interface Table--

oe_headers_iface_all

oe_lines_iface_all

oe_actions_iface_all

--Base Table--

oe_order_headers_all

oe_order_lines_all

oe_order_sources

oe_transaction_types_tl

oe_transaction_types_all

 

**********************

--Inventory--

**********************

--Interface Table--

mtl_system_items_interface

mtl_item_categories_interface

mtl_item_revisions_interface

mtl_interface_errors

 

--Base Table--

mtl_system_items_b

mtl_system_items_tl

mtl_item_locations

mtl_item_locations

mtl_item_categories

mtl_item_revisions_b

mtl_parameters

hr_all_organizations_units

cst_item_costs

qp_list_headers

jft_rs_salesreps

 

**********************

--Purchasing--

**********************

--Interface Table--

PO REQUISITIONS

po_requistitions_interface_all

po_req_dist_interface_all

 

--Base Table--

PO REQUISITIONS

po_requisition_headers_all

po_requisition_lines_all

po_req_distributions_all

 

**********************

--PURCHASE ORDERS--

**********************

--Interface Table--

po_headers_interface

po_lines_interface

po_distributions_interface

 

--Base Table--

po_headers_all

po_lines_all

po_line_locations_all

po_distributions_all

 

**********************

--Payables--

**********************

--Interface Table--

ap_invoices_interface

ap_invoice_lines_interface

 

--Base Table--

ap_invoices_all

ap_invoice_distributions_all

ap_payments_schedules_all

 

**********************

--Fixed Assets--

**********************

--Interface Table--

fa_inv_interface

fa_production_interface

fa_mass_additions

 

--Base Table--

fa_additions_b

fa_additions_tl

fa_books

fa_deprn_summary

fa_deprn_details

fa_deprn_history

fa_retirements

Oracle EBS: How to Create Materialized View & How to Refresh Materialized View

Subject: We will review here how to create materialized view and how to refresh the materialized view on demand.

 

Step1: Create Employee and Employee Address table based on that we create Materialized view. Also, insert 1 -1 record each.

create table xx_emp (emp_num NUMBER, emp_name VARCHAR2(240));

INSERT INTO xx_emp VALUES(1001,'David');

 

create table xx_emp_add (emp_num NUMBER, address VARCHAR2(240));

INSERT INTO xx_emp_add VALUES(1001,'USA');

 

Step2: Create materialized view based on the above-created custom tables.

 

create materialized view xx_emp_v AS

select

a.emp_num,

a.emp_name,

b.address

from

xx_emp a,

xx_emp_add b

where a.emp_num=b.emp_num;

 

Step3: Try to run the materialized view.

select * from xx_emp_v;  --It will give one record only

                                    

 

Step4: Create a new Entry and check the materialized view output.

INSERT INTO xx_emp VALUES(1002,'John');

INSERT INTO xx_emp_add VALUES(1002,'France');

 

select * from xx_emp_v;  --It will give one record only

 

Step5: We need to refresh the materialized view using the below script and check the Materialized view output.

exec dbms_mview.refresh('xx_emp_v'); -- Materialized view refresh script.

select * from xx_emp_v;  --It will give two records only

 

Oracle Fusion - SQL Query to get Project Plan Task Associated Resource Utilization Details

Navigation: Projects > Manage Financial Project Settings > Manage Project Plan > Click on Resource.

--SQL Query:--

SELECT

    main.resource_name,

    SUM(nvl(main.planned_amt, 0)) planned_amt,

    SUM(nvl(main.actual_amount, 0)) actual_amount,

    SUM(nvl(main.actual_amount, 0)) - SUM(nvl(main.planned_amt, 0)) varience

FROM

    (

        SELECT

            ppav.project_unit,

            ppav.proj_number,

            ppav.project_id,

            ppav.proj_name,

            ppav.start_date,

            ppav.completion_date,

            ppav.project_type,

            ppav.proj_status,

            ppav.proj_desc,

            res.resource_name,

            SUM(nvl(res.planned_amt, 0)) planned_amt,

            nvl((

                SELECT

                    SUM(nvl(pcd1.acct_raw_cost, 0))

                FROM

                     pjc_cost_dist_lines_all   pcd1,

                     pjf_resource_classes_b    prc1, 

pjf_resource_classes_tl   prct1, 

pjf_txn_base_header       ptbh

                WHERE

pcd1.txn_accum_header_id = ptbh.txn_accum_header_id

                    AND ptbh.resource_class_id = prc1.resource_class_id

                    AND prc1.resource_class_id = prct1.resource_class_id

                    AND prct1.language = userenv('LANG')

                    AND prct1.source_lang = userenv('LANG')

                    AND pcd1.project_id = ppav.project_id --300001798619442

                    AND prct1.name = res.resource_name

            ), 0) actual_amount

        FROM

            -- pjf_tasks_v ptv,

            (

                SELECT

                    pcp.contract_id,

                    ppa.project_id,

                    ppa.project_unit_id,

                    ppa.name                   proj_name,

                    ppa.segment1               proj_number,

                    ppa.org_id,

                    ppst.project_status_name   proj_status,

                    ppa.start_date,

                    ppa.completion_date,

                    hov.name                   project_unit,

                    pptt.project_type,

                    ppa.description            proj_desc,

                    CASE

                        WHEN ppa.attribute_category <> 'AMO Eng Bidding' --No Need to check AMO Eng category

                         THEN

                            ppa.attribute2

                    END bidding_prj_num,

                    ppa.attribute1             source_of_bidding,

                    to_char(ppa.attribute1_date, 'mm/dd/yyyy') q_and_a_date,

                    to_char(ppa.attribute2_date, 'mm/dd/yyyy') submission_date,

                    ppa.attribute2             earning_opportunity,

                    ppa.attribute7             project_segment_gl,

                    hou.name                   project_bu

                FROM

                    pjf_projects_all_vl       ppa,

                    pjb_cntrct_proj_links     pcp,

                    pjf_project_statuses_tl   ppst,

                    hr_organization_v         hov,

                    pjf_project_types_tl      pptt,

                    hr_operating_units        hou

                WHERE

                    1 = 1

                    AND ppa.project_id = pcp.project_id (+)

                    AND ppa.project_status_code = ppst.project_status_code

                    AND ppst.language = userenv('LANG')

                    AND ppst.source_lang = userenv('LANG')

                    AND hov.organization_id = ppa.project_unit_id

                    AND hov.classification_code = 'PRJ_PROJECT_UNIT'  --Classification PRJ_PROJECT_UNIT

                    AND hov.status = 'A'

                    AND sysdate BETWEEN hov.effective_start_date AND hov.effective_end_date

                    AND ppa.project_type_id = pptt.project_type_id

                    AND pptt.source_lang = userenv('LANG') --Source Lang US

                    AND pptt.language = userenv('LANG') --Language US

                    AND ppa.org_id = hou.organization_id

                GROUP BY

                    pcp.contract_id,

                    ppa.project_id,

                    ppa.project_unit_id,

                    ppa.name,

                    ppa.segment1,

                    ppa.org_id,

                    ppst.project_status_name,

                    ppa.start_date,

                    ppa.completion_date,

                    hov.name,

                    pptt.project_type,

                    ppa.description,

                    CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' --No Need to check AMO Eng category

                             THEN

                                ppa.attribute2

                    END,

                    ppa.attribute1,

                    to_char(ppa.attribute1_date, 'mm/dd/yyyy'),

                    to_char(ppa.attribute2_date, 'mm/dd/yyyy'),

                    ppa.attribute2,

                    ppa.attribute7,

                    hou.name

            ) ppav,

            (

                SELECT

                    ppvv.project_id,

                    ppvv.plan_version_id,

                    ppvv.version_name,

                    ppvv.total_pc_brdnd_cost,

                    ppvv.total_pc_revenue

                FROM

                    pjo_plan_versions_vl ppvv

                WHERE

                    1 = 1

                    AND plan_status_code = 'B' --Baseline plan status data need to fetch

                    AND ppvv.plan_class_code = 'BUDGET'  --Budget Version Detail need to fetch

                    AND ppvv.budgetary_controls_flag = 'Y'

                    AND version_number = (

                        SELECT

                            MAX(version_number)

                        FROM

                            pjo_plan_versions_vl

                        WHERE

                            project_id = ppvv.project_id

                            AND plan_status_code = 'B' --Baseline plan status data need to fetch

                    )

            ) pv,

            (

                SELECT

                    ppe.project_id,

                    ppe.plan_version_id,

                    prct.name resource_name, --v 2.0

                    SUM(nvl(ppl.total_tc_raw_cost, 0)) planned_amt

                FROM

                    pjo_planning_elements     ppe,

                    pjf_rbs_elements          pre,

                    pjo_plan_lines            ppl,

                    pjf_resource_classes_b    prc,

                    pjf_resource_classes_tl   prct

                WHERE

                    ppe.rbs_element_id = pre.rbs_element_id

                    AND ppe.planning_element_id = ppl.planning_element_id

                    AND pre.resource_class_code = prc.resource_class_code

                    AND prc.resource_class_id = prct.resource_class_id

                    AND prct.language = userenv('LANG')

                    AND prct.source_lang = userenv('LANG')

                GROUP BY

                    ppe.project_id,

                    ppe.plan_version_id,

                    prct.name

            ) res

        WHERE

            1 = 1

            -- ppav.project_id = ptv.project_id

            AND ppav.project_id = pv.project_id (+)

            AND ppav.project_id = res.project_id

            AND pv.plan_version_id = res.plan_version_id

            -- AND ptv.task_id = res.task_id

            AND ( ppav.project_bu IN (

                :p_bu

            )

                  OR 'ALL' IN (

                'ALL' || :p_bu

            ) )

            AND ( ppav.proj_number IN (

                :p_project_num

            )

                  OR 'ALL' IN (

                'ALL' || :p_project_num

            ) )

            AND ( ppav.proj_name IN (

                :p_project_name

            )

                  OR 'ALL' IN (

                'ALL' || :p_project_name

            ) )

            AND ( ppav.project_unit_id IN (

                :p_project_unit

            )

                  OR 'ALL' IN (

                'ALL' || :p_project_unit

            ) )

--V 1.0 Parameter Removed

/*

AND inv.trx_date BETWEEN nvl(

                        :p_trx_from_date, inv.trx_date

                    ) AND nvl(

                        :p_trx_to_date, inv.trx_date

                    )

                    AND ( ptv.task_id IN ( :p_task )

                          OR 'ALL' IN ( 'ALL' || :p_task ) )

                    AND ( ppav.org_id IN ( :p_bu )

                          OR 'ALL' IN ( 'ALL' || :p_bu ) )

                    AND ( ppav.project_unit_id IN ( :p_project_unit )

                          OR 'ALL' IN ( 'ALL' || :p_project_unit ) )

                    AND ( ppav.project_id IN ( :p_project_name )

                          OR 'ALL' IN ( 'ALL' || :p_project_name ) )

                    AND ppav.start_date BETWEEN nvl(

                        :p_from_date, ppav.start_date

                    ) AND nvl(

                        :p_to_date, ppav.start_date

                    )

*/

            AND ppav.org_id IN (

                SELECT

                    org_id

                FROM

                    fun_user_role_data_asgnmnts   furda,

                    hr_operating_units            hou

                WHERE

                    user_guid = fnd_global.user_guid

                    AND active_flag != 'N'

                    AND hou.organization_id = furda.org_id

                UNION

                SELECT

                    hou.organization_id org_id

                FROM

                    per_users               pu,

                    per_all_people_f        ppf,

                    per_all_assignments_f   papf,

                    hr_operating_units      hou

                WHERE

                    ppf.person_id = pu.person_id

                    AND papf.person_id = ppf.person_id

                    AND hou.organization_id = papf.business_unit_id

                    AND pu.username = fnd_global.user_name

                    AND primary_assignment_flag = 'Y'

                    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

            )

        GROUP BY

            ppav.project_unit,

            ppav.proj_number,

            ppav.project_id,

            ppav.proj_name,

            ppav.start_date,

            ppav.completion_date,

            ppav.project_type,

            ppav.proj_status,

            ppav.proj_desc,

            res.resource_name

        ORDER BY

            ppav.proj_number

    ) main

GROUP BY

    main.resource_name

ORDER BY

    main.resource_name