Venky DevMastery

 Procedure With Character Parameter enabled =========================== Create or Replace Procedure FLM_Names_Test1(P_name varchar2) IS n varchar2(50); f varchar2(50); l varchar2(50); m varchar2(50); BEGIN n:='&name'; f:=substr(n,1,instr(n,' ')-1); l:=substr(n,instr(n,' ',1,2)+1); m:=trim(ltrim(rtrim(n,l),f)); dbms_output.put_line('Entered Name is: '||n); dbms_output.put_line('================'); dbms_output.put_line('First Name is:- '||f); dbms_output.put_line('Last Name is:- '||l); dbms_output.put_line('Middle_Name is:- '||m); dbms_output.put_line('--------End of Procedure--------'); --EXCEPTION END; /

 Procedure to add two numbers with Parameter enabled ==================================================== CREATE  OR REPLACE PROCEDURE ADD_TEST1( p_a IN number, p_b IN number) IS a number(3); b number(3); c number(5); BEGIN a:=&a; b:=&b; c:=a+b; DBMS_OUTPUT.PUT_LINE('Total is: '||c);  --EXCEPTION END; / Procedure ADD_TEST1 compiled Exec ADD_TEST1(10,20); Total is: 30 PL/SQL procedure successfully completed.

 WITH Clause  ====================================== What? With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs) This features is introduced in Oracle 9i.Using this feature: We assign query result set to a temporary tables. We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries. When? Usage of (2,3 level-Nested Queries) can  be minimized or avoided when using With Clause. If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data. How to implement CTEs ====================== SELECT deptno,        Sum(sal) total_sal FROM   emp GROUP  BY deptno; --fetch sum of sal for each dept. SELECT Avg(total_sal) FROM  (SELECT deptno,               Sum(sal) total_sal     ...

 In Oracle Fusion SCM, you manage freight charges, duty charges, and rebate charges through various modules,  including   Pricing,   Order Management,   and potentially Transportation Management,   allowing for allocation, calculation, and tracking of these costs.  Here's a more detailed breakdown: 1. Freight Charges: Definition: Freight charges are costs associated with transporting goods, encompassing various elements like  transportation,  fuel,  and handling.  Management: Pricing: Define shipping cost types and pricing rules to calculate freight charges.  Order Management: Capture and manage freight charges at the sales order level.  Transportation Management: Integrate with TMS to automate freight cost updates.  Manual Entry: You can manually enter or override freight charges on sales orders.  Allocation: Allocate freight charges to specific item lines or across all lines on an invoice....

 Performance Tuning Tips: --> Do not select all the columns when you are fetching data from FusionDB because it will do full table scan(FTS) Instead use only required columns or Index columns. ---> Always limit the rows when fetching data. --> Use Proper Joins(Primary Key and Foreign Key relationship). --> Use Exists instead of IN. --> Use Hints.

 Emp_Details_Report Table1: per_all_people_f select  person_id, person_number, to_char(trunc(start_date),'yyyy-mm-dd') from per_all_people_f --(person_number,start_date) where person_number=:P_person_number --Bind Parameter. --and nvl(:P_person_number,P_person_number)--Optional Parameter AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE ---------------------- Table2: Per_person_names_f --person_name select FULL_NAME  from Per_person_names_f where --PERSON_ID=300000047606113 and NAME_TYPE='GLOBAL' -------------------------------------------------- Table3:per_people_legislative_f select sex as gender, marital_status from per_people_legislative_f where person_id=<enter_id> --------------------------------------- Table4:per_persons select date_of_birth from per_persons where person_id=<enter_id> Now, we will join the tables to get the Report ================================= Bind Parameter is mandatory: ---------------------------- select  papf....

How to Export  Images/Attachments/Documents from Oracle Fusion to Third-Party Apps: ===================================================  1st Methord: Navigate Tools--->click Export Management Oracle ERP application Fill details: name, export object, and file name. Select the Export attachment checkbox to include attachments in the export. After the activity completes, you can access the exported data, including the extracted attachments and then send to third party application. 2nd Method: Use REST API to get all the all attachments  https://docs.oracle.com/en/cloud/saas/human-resources/24d/farws/op-payslips-payslipsuniqid-child-documents-documentsuniqid-child-attachmentspreview-attachmentspreviewuniqid-child-attachment-get.html

Single Row_SubQuery -------------------- Single-Row Sub-Queries Returns one value SELECT <columns> FROM <table_name> WHERE <col_name> <OP> (SELECT STMNT); > , >=, <, <=, =. <> A single-row subquery in Oracle SQL is a subquery (a query nested inside another query) that returns only one row. The main/outer query uses the inner query result as its conditions If single row sub-query returns more than one row then you will get an error ORA-01422: exact fetch returns more than requested number of rows) It can be used in Select, Where, Having clauses Single Row_SubQuery -------------------- --1. Find who is getting highest salary in the employee table? select max(sal) from emp; select *from emp where sal=(select max(sal) from emp); select max(salary) from employees; select *from employees where salary=(select max(salary) from employees); --2.Find second maximum salary emp? select *from emp where sal=( select max(sal) from emp where sal!=(select max(sa...

Human Capital Management HR Modules: Core Human Resources Benefits Workforce Modeling Strategic Workforce Planning Work Life Advanced HCM Controls Experience Design Studio Activity Centers Talent Management Modules Recruiting Onboarding Learning Career Development Opportunity Marketplace Performance Management Compensation Succession Planning Dynamic Skills Workforce Management Modules Absence Management Time and Labor Workforce Health and Safety Workforce Scheduling Workforce Labor Optimization Oracle Payroll Oracle Payroll Interface Oracle Payroll Connect Oracle Payroll Core Oracle ME  Oracle ME Platform Communicate Journeys Grow Celebrate Touchpoints Connections HR Help Desk Digital Assistant

 Finance Management: Accounting hub Reporting and analytics Payables and Assets (AP &FA) Revenue management Receivables (AR) Collections Expense management Joint venture management U.S. Federal Financials Project Management: Plan, schedule, and forecast Resource management Cost management and control Billing and revenue management Grant management Project asset management Procurement: Supplier Management Supplier Portal Sourcing Procurement Contracts Self Service Procurement Purchasing Payables Procurement Analytics Risk Management and compliance: ERP role and security design Separation-of-duties automation Continuous access monitoring User access certification Configurations controls Transactions controls Audit and SOX/ICFR workflows Enterprise risk management (ERM) Business continuity planning Enterprise Performance Management: Planning Profitability and cost management Account reconciliation Financial consolidation and close Tax reporting Narrative reporting Enterprise data ...

 SCM --- Supply Chain Planning: Demand Management Supply Planning Sales and Operations Planning Supply Chain Collaboration Inventory Management: Materials Management Cost Management Financial and Supply Chain Orchestration Manufactering: Discrete Manufacturing Process Manufacturing Mixed-Mode Manufacturing Production Scheduling Project-Driven Manufacturing Contract Manufacturing Maintenance: Asset management Smart Operations Maintenance planning and execution Maintenance cost management Parts inventory management Project-specific maintenance Service logistics and depot repair for asset-based service Order Management Omnichannel Order Management Rapid order fulfillment Pricing management Product and service configuration Global order promising Channel Revenue Management Logistics: Transportation Management Global Trade Management Warehouse Management Product Life-Cycle Management: Innovation Management Product Development Quality Management Configurator Modeling Product Master Data ...