Thursday, July 25, 2019

GL: Flexfield Qualifiers



The definition I inferred from the word flexfield qualifier is “which segment qualifies for which attribute“.
Each Segment can have one of the following Qualifying attributes attached to it. Some of them are mandatory others are optional.
  • Cost Center: Usually assigned to Department Segment. This qualifying segment is used by Oracle Assets.
  • Natural Account: This qualifier when attached to a segment enables the five nature of accounts Asset, Liability, Expense,Revenue, Owner’s Equity. It also assigns the option of Reconciliation account and Third Party Control.
  • Balancing: This qualifier is usually assigned to the segment on which the Balance Sheet is required. System balances the Debit and Credit of the ledger on this segment.
  • Intercompany: This qualifier is used to identify the transaction between two entities under one group. More on this in Intercompany.
  • Management: This qualifier is used as an additional control on Data Access Set.
  • Secondary Tracking: To be discussed
Flexfield Qualifiers determines the nature of the segment. If you define a COA structure as
Company-Department-Account

How will the system knows which segment is natural account? Which segment should be used to balance the accounting entries? which segment will be used as Cost Centre in Fixed Assets?
It is the qualifier through which the system assign particular attribute to the segment. If I attach Natural Account qualifier to Company Segment, the system will not validate anything against this assignment. It will simply enable the five natures of accounts and treat the Company segment as Natural Account.
So for particular segment, a particular qualifier is assigned. This is the one time activity and freezes as we freeze the COA Structure.
That’s All :)

Wednesday, July 17, 2019

O2C Cycle Oracle Apps R12

O2C CYCLE steps

Steps
  1. Enter sales order
  2. Book the sales order
  3. Pick release ( Release sales order)
  4. Move order
  5. Ship confirm
  6. Create Invoice
  7. Create Receipt
  8. Transfer to General ledger
  9. Journal import
  10. journal posting

  1. CREATE SALES ORDER 
    1. Header
      1. With customer info
    2. Line with item info
    3. Check availability qty of item
    4. Check the bank end tables
      1. OE_ORDER_HEADERS_ALL
        1. Flow_status_code = Entered
      2. OE_ORDER_LINES_ALL
        1. Flow_status_code = Entered 
  2. BOOK SALES ORDER 
    1. Sales order status : booked
    2. Check the back end tables
      1. OE_ORDER_HEADERS_ALL
        1. Flow_status_code = Booked
      2. OE_ORDER_LINES_ALL
        1. (Flow_status_code = Awaiting shipping)
      3. WSH_DELIVERY_DETAILS
        1. RELEASED_STATUS= 'R' (ready to release)
      4. WSH_DELIVERY_ASSIGNMENTS
        1. new row inserted
    3. Backgroud programs
      1. DEMAND INTERFACE PROGRAM
  3. PICK RELEASE ( Release sales order) [ Items in SO must be released from inventory ]
    1. Nav :( Shipping > Release sales order > Release sales order)
    2. search sales order
      1. auto create delivery ,Auto pick confirm, Auto pack delivery, Auto allocate = 'Y'
      2. Click 'Execute now' button
      3. Pick release process completes successfully and calls programs
        1. Auto pack report
        2. shipping exception report
        3. Pick slip report
    3. Back end tables 
      1. WSH_NEW_DELIVERIES ( NEW ROW CREATED)
      2. WSH_DELIVERY_DETAILS
        1. ( if Auto pick confirm = y )
          1. RELEASED_STATUS= 'S' (Release to warehouse)
        2. else do following from step 4 
  4. PICK CONFIRM (Move order )[ Confirning that the released item is picked for shipping ]
    1. (Nav: Inventory super user > Move order > Transact move order)
    2. search with 4th number in 'pick slip report' (batch request number)
      1. click 'view update allocation ' > click Transact button > Transact button will be deactivated
      2. close Transact move order form
    3. Go to order organizer form> search SO number > additional info > deliveries tab > pick status > Staged / Pick confirm
    4. view delivery details button > Delivery line details button >
      1. Line status : Staged / Pick confirm
      2. Next step  field value : ship confirm
  5. SHIP CONFIRM [ Confirming that the item is Shipped at customers location ]
    1. Nav : OM > Shipping > tansactions
    2. query order number
    3. Click on Delivery tab shows > click Ship confirm button 
      1. Enable following  : Yes 
        1. Ship entered qty :  
        2. Create delivery for staged quantities : yes
        3. Set delivery in transit : yes
        4. Create bill of lading : yes
        5. Close trip: yes
      2. Get a message saying : Delivery was successfully confirmed 
    4. Ship confirm will call other programs like, in Run> View request
      1. Interface trip stop
      2. Vehicle load sheet details
      3. Commercial invoice
      4. Packing slip report
      5. Bill of lading
    5. Backend tables
      1. WSH_DELIVERY_DETAILS (RELEASE STATUS = C (means Ship confirm))
      2. OE_ORDER_LINES_ALL (Flow status code = shipped)
      3. MTL_RESERVATIONS( INVENTORY_ITEM_ID = 'o2citem' => table empty > data freezed)\
      4. MTL_ONHAND_QUANTITIES_DETAIL(for item 'o2c' reduces by qty of SO)
      5. MTL_MATERIAL_TRANSACTIONS( for inventory item id > will show 4 rows 
        1. look at transaction qty column  
          1. 1 row for adding items
          2. 1 row > release from Inventory 
          3. 1 for moving to staging area
          4. 1 row > staging to transportation
  6. SO INTERFACE 
    1. Order organizer > SO > additional info >> deliveries > view delivery details >
      1. Line status > interfaced 
      2. next step  field : Not applicable
    2. RA_INTERFACE_LINES_ALL :  (INTERFACE_LINE_ATTRIBUTE1 = SO NUMBER)
      1. Now shipping status will be interfaced 

  7. CREATE INVOICE 
    1. Run Workflow background process
      1. item type : OM standard
      2. process deferred : yes
      3. Process time out : Yes
    2. This calls 2 more programs
      1. ADS ( Auto invoice master)
      2. Auto invoice import
    3. Go to AR > Transactions form
      1. Query the invoice using SO in 'Reference' field 
      2. Note the invoice number
      3. create accounting
    4. Back end tables
      1. Transaction tables
        1. RA_CUSTOMER_TRX_ALL ( TRX NUMBER = INV NUM) - Note CUSTOMER_TRX_ID 
        2. RA_CUSTOMER_TRX_LINES_ALL ( CUSTOMER_TRX_ID = xx)
        3. RA_CUSTOMER_TRX_LINE_GL_DIST_ALL ( CUSTOMER_TRX_ID = xx) -
          1. Look for TAX amount
          2. Find EVENT_ID
      2. Create accounting - SLA
        1. SLA
          1. XLA_EVENTS (where EVENT_ID =xx)
          2. XLA_AE_HEADERS (where EVENT_ID = XX)
          3. XLA_AE_LINES (where AE_HEADER_ID = XX)

      3. Interface
        1. GL_INTERFACE ( Reference26 = EVENT_ID)
      4. GL_TABLES
        1. Create accounting
          1. View request > journal import > view output > note : batch name 
        2. GL resp
        3.  Tables
          1. GL_JE_BATCHES( NAME = BATCH NAME)
          2. GL_JE_HEADERS (JE_BATCH_ID)
          3. GL_JE_LINES (JE_HEADER_ID)
  8. CREATE RECEIPT
    1. Create Reciepts for all the invoices created in previous step
    2. AR > Receipts form ( TRX NUMBER)
  9. TRANSFER TO GL
    1. To transfer the Receivables accounting information to general ledger,
    2. run General Ledger Transfer Program.
      Navigation: Receivables> View Requests
    3. ( We use this for invoices from AUTO INVOICE PROGRAM )
  10. JOURNAL IMPORT
    1. To transfer the data from General Ledger Interface table to General Ledger,
    2. run the Journal Import program from Oracle General Ledger.
    3. Navigation: General Ledger > Journal> Import> Run
  11. JOURNAL POSTING
    1. We have to Post journal batches that we have imported previously to update the account balances in General Ledger.
    2. Navigation: General Ledger> Journals > EnterQuery for the unposted journals for a specific period as shown below.

Tuesday, July 16, 2019

Real Time Scenarios in SQL Queries

Scenario 1 :  What is Query to find Second highest salary for employee?

This is most asked Real Time Scenarios in SQL in many industries. There are lot of real time situation where user needs to deal with this kind of situation.User will try multiple queries to find out the same result.
Query 1 :
Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;
Query 2:
select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=2;
Query 3:
select * from(Select S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR from Source) S Where S.DR=2;

Scenario 2 : Fetching Nth Record from the table.

There are some situations where user needs to find out the Nth records from the table. I will divide this scenario in to 3 parts for better understanding of people.
Query 1 :  Query to find First Record from the table.
 Select * from Employee where Rownum =1;
Query 2: Query to find last record from the table.
Select * from Employee where Rowid= select max(Rowid) from Employee;
Query 3 : Query to find Nth Record from the table.
select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum <= N_ROWS ) where rnum >= N_ROWS;

Scenario 3 : Find and delete duplicate rows

There are real world situations where user needs to find and delete duplicate rows from the table. These are most used SQL queries in real world to find the duplicate rows and delete it. When there is a situation where user needs to add unique constraint to column,user needs to delete duplicate rows.
Query 1 :  Query to find duplicate rows.
 select a.* from Employee a where rowid != 
         (select max(rowid) from Employee b where  a.Employee_num =b.Employee_num;
Query 2: Query to delete duplicate rows
Delete from Employee a where rowid !=  (select max(rowid) from Employee b where  a.Employee_num =b.Employee_num;

Scenario 4 : Find a table specific information

There are times where user needs to find out the table specific information.There are so many system tables which will find a table specific information.
Query 1: How to Find table name and its owner?
Make sure that the database user have logged in with SYS user.
Select table_name,Owner from All_tables order by table_name,owner;
Query2:How to find Selected Tables from a User?
SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘STU%’;
Scenario 5 : Find the constraint information
There are so many scenarios in real world that user needs to find out the constraint information.There are so  many constraints used to make the database normalized.The following are some important queries which will gives us the information about the oracle constraints.
Query 1 : How to find all details about Constraints?
SELECT * From User_Constraints;
SELECT * FROM User_Cons_Columns;
Query 2: How to find Constraint Name?
SELECT Table_Name, Constraint_Name FROM User_Constraints;
Query 3: How to find Constraint Name with Column_Name?
SELECT Column_Name, Table_Name, Constraint_Name FROM User_Cons_Columns;
Query 4: How to find Selected Tables which have Constraint?
SELECT Table_Name FROM User_Cons_Columns WHERE Table_Name LIKE ‘STU%’;
Query 5: How to find Constraint_Name, Constraint_Type, Table_Name?
SELECT Table_Name, Constraint_Type, Constraint_Name FROM User_Constraints;
SELECT Table_Name, Constraint_Type, Constraint_Name, Generated FROM User_Constraints;

Scenario 6: How to create a table which has same structure  or how to create duplicate table.

There are so many situations where user needs to create duplicate tables for testing purpose. There are some needs where user needs to create the structure of the table. The following are 2 most important queries which are used in 90% of Real Time Scenarios in SQL.
Query 1: Create the duplicate table with data
Create table Employee_1 as Select * from Employee;
Query 2: Create the table structure duplicate to another table.
Create table Employee_1 as Select * from Employee where 1=2;

Scenario 7 : Finding the procedures information

There are situations in Real Time Scenarios of SQL where user needs to find out the procedures information.
Query 1 :How to check Procedures?
 SELECT * FROM User_Source
WHERE Type=’PROCEDURE’
AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);
Query 2:How to find procedure columns information?
select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

Scenario 8: Scenario of Self Join

We need to check the table which are joined with itself.There are the situations where user needs to join the table with itself. I will try to give one query which explains the scenario of self join.
Query : The query to find out the manager of employee
Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

Scenario 9 : Email validation of SQL

There is need to add the email validation using SQL queries.These are also most common Real Time Scenarios in SQL.
Query : How to add the email validation using only one query?
User needs to use REGEXP_LIKE function for email validation.
 SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

How to Generate Permutations in SQL 



with mydata as (
    select 'A' mycol from dual
    union all
    select 'B' mycol from dual
    union all
    select 'C' mycol from dual
    union all
    select 'D' mycol from dual
    union all
    select 'E' mycol from dual
    union all
    select 'F' mycol from dual
    union all
    select 'G' mycol from dual
    union all
    select 'H' mycol from dual
)
select c1.mycol , c2.mycol
from mydata c1, mydata c2
where c1.mycol <> c2.mycol