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....