Friday, 15 February 2019

Fusion HCM - Query for Element Entry Details

SELECT *
  FROM per_all_people_f peo,
       per_all_assignments_f asg,
       per_person_names_f per_name,
       per_periods_of_service ser,
       per_person_types_tl per_typ,
       per_legal_employers ple,
       pay_rel_groups_dn payrel,
       pay_assigned_payrolls_dn papd,
       pay_all_payrolls_f pay,
       pay_element_entry_values_f peevf,
       pay_input_values_f pivf,
       pay_element_entries_f peef,
       pay_element_types_f petf,
       pay_entry_usages peu
 WHERE asg.person_id = peo.person_id
   AND ser.person_id = peo.person_id
   AND per_name.person_id = peo.person_id
   AND asg.assignment_id = payrel.assignment_id
   AND asg.period_of_service_id = ser.period_of_service_id
   AND asg.person_type_id = per_typ.person_type_id
   AND asg.legal_entity_id = ple.organization_id
   AND papd.payroll_term_id = payrel.parent_rel_group_id
   AND papd.payroll_id = pay.payroll_id
   AND peevf.element_entry_id = peef.element_entry_id
   AND pivf.element_type_id = petf.element_type_id
   --AND pivf.base_name = 'Amount'
   --AND petf.base_element_name IN ('Basic')
   AND peu.element_entry_id = peef.element_entry_id
   AND peevf.input_value_id = pivf.input_value_id
   AND peu.payroll_relationship_id = payrel.payroll_relationship_id
   AND payrel.group_type = 'A'
   AND asg.primary_flag = 'Y'
   AND asg.assignment_type IN ('E', 'C', 'N', 'P')
   AND asg.assignment_status_type = 'ACTIVE'
   AND per_name.name_type = 'GLOBAL'
   AND ple.status = 'A'
   AND per_typ.LANGUAGE = USERENV ('LANG')
   AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
                           AND per_name.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN payrel.start_date AND payrel.end_date
   AND TRUNC (SYSDATE) BETWEEN papd.start_date
                           AND NVL (papd.lspd,
                                    TO_DATE ('31/12/4712', 'DD/MM/YYYY')
                                   )
   AND TRUNC (SYSDATE) BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+)
   AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
                           AND pivf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN peevf.effective_start_date
                           AND peevf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
                           AND petf.effective_end_date

Fusion HCM - Query for Personal Payment Method Details

SELECT per_pay.*
  FROM per_all_people_f peo,
       per_all_assignments_f asg,
       per_person_names_f per_name,
       per_periods_of_service ser,
       pay_pay_relationships_dn pay_rel,
       pay_personal_payment_methods_f per_pay,
       pay_org_pay_methods_f org_pay,
       pay_bank_accounts bnk_act,
       per_legislative_data_groups ldg,
       per_person_types_tl per_typ,
       per_legal_employers ple
 WHERE asg.person_id = peo.person_id
   AND ser.person_id = peo.person_id
   AND per_name.person_id = peo.person_id
   AND peo.person_id = pay_rel.person_id
   AND asg.period_of_service_id = ser.period_of_service_id
   AND asg.person_type_id = per_typ.person_type_id
   AND asg.legal_entity_id = ple.organization_id
   AND pay_rel.payroll_relationship_id = per_pay.payroll_relationship_id
   AND per_pay.org_payment_method_id = org_pay.org_payment_method_id
   AND org_pay.legislative_data_group_id = ldg.legislative_data_group_id
   AND per_pay.bank_account_id = bnk_act.bank_account_id(+)
   AND asg.primary_flag = 'Y'
   AND asg.assignment_type IN ('E', 'C', 'N', 'P')
   AND asg.assignment_status_type = 'ACTIVE'
   AND per_name.name_type = 'GLOBAL'
   AND ple.status = 'A'
   AND per_typ.LANGUAGE = USERENV ('LANG')
   AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
                           AND per_name.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN org_pay.effective_start_date
                           AND org_pay.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN per_pay.effective_start_date
                           AND per_pay.effective_end_date

Fusion HCM - Query for OTL Time Entries Details

SELECT   papf.person_number emp_id,
         TO_DATE (TO_CHAR (sh21.start_time, 'DD/MM/YYYY'),
                  'DD/MM/YYYY'
                 ) start_time,
         TO_DATE (TO_CHAR (sh21.stop_time, 'DD/MM/YYYY'),
                  'DD/MM/YYYY'
                 ) stop_time,
         sh26.attribute_category elements, sh27.status_value,
         ROUND (SUM (sh21.measure), 2) measure
    FROM per_all_people_f papf,
         per_all_assignments_m asg,
         per_legal_employers ple,
         hwm_tm_rec sh21,
         hwm_tm_rec_grp_usages sh22,
         hwm_tm_rec_grp sh23,
         hwm_grp_type sh24,
         hwm_tm_rep_atrb_usages sh25,
         hwm_tm_rep_atrbs sh26,
         hwm_tm_statuses sh27,
         hwm_tm_status_def_b sh28
   WHERE papf.person_id = asg.person_id(+)
     --AND papf.person_number = '24043'
     AND asg.legal_entity_id = ple.organization_id
     AND asg.primary_flag = 'Y'
     AND asg.assignment_type IN ('E', 'C', 'N', 'P')
     AND asg.assignment_status_type = 'ACTIVE'
     AND ple.status = 'A'
     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                             AND papf.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date
                             AND asg.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date
                             AND ple.effective_end_date
     AND sh21.latest_version = 'Y'
     AND sh21.resource_type = 'PERSON'
     AND sh21.tm_rec_id = sh22.tm_rec_id
     AND sh21.tm_rec_version = sh22.tm_rec_version
     AND sh21.layer_code = 'TIME_RPTD'
     AND sh22.layer_code = 'TIME_RPTD'
     AND sh22.tm_rec_grp_id = sh23.tm_rec_grp_id
     AND sh22.tm_rec_grp_version = sh23.tm_rec_grp_version
     AND sh23.latest_version = 'Y'
     AND sh21.resource_id = sh23.resource_id
     AND sh23.grp_type_id = sh24.grp_type_id
     AND sh21.tm_rec_id = sh25.usages_source_id
     AND sh21.tm_rec_version = sh25.usages_source_version
     AND sh25.usages_type = 'TIME_RECORD'
     AND sh24.NAME = 'Processed TimecardDay'
     AND sh25.tm_rep_atrb_id = sh26.tm_rep_atrb_id
     AND sh26.attribute_category IN (SELECT base_element_name
                                       FROM pay_element_types_f)
     AND sh21.resource_id = papf.person_id
     AND sh21.tm_rec_type IN ('RANGE', 'MEASURE')
     AND sh27.tm_status_def_id = sh28.tm_status_def_id
     AND sh27.tm_bldg_blk_id = sh21.tm_rec_id
     AND sh27.tm_bldg_blk_version = sh21.tm_rec_version
     AND TRUNC (sh27.date_to) = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
GROUP BY papf.person_number,
         sh26.attribute_category,
         sh27.status_value,
         TO_DATE (TO_CHAR (sh21.start_time, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
         TO_DATE (TO_CHAR (sh21.stop_time, 'DD/MM/YYYY'), 'DD/MM/YYYY')

Fusion HCM - Query for Absence Accrual Balance

SELECT peo.person_number emp_id, aapft.NAME absence_plan_name,
       acc_ent.end_bal balance
  FROM per_all_people_f peo,
       per_all_assignments_f asg,
       per_person_names_f per_name,
       per_periods_of_service ser,
       per_person_types_tl per_typ,
       per_legal_employers ple,
       anc_absence_plans_f_tl aapft,
       anc_absence_plans_f aapf,
       anc_per_accrual_entries acc_ent
 WHERE asg.person_id = peo.person_id
   AND ser.person_id = peo.person_id
   AND per_name.person_id = peo.person_id
   AND acc_ent.person_id = peo.person_id
   AND asg.period_of_service_id = ser.period_of_service_id
   AND acc_ent.prd_of_svc_id = asg.period_of_service_id
   AND asg.person_type_id = per_typ.person_type_id
   AND asg.legal_entity_id = ple.organization_id
   AND aapft.absence_plan_id = aapf.absence_plan_id
   AND acc_ent.plan_id = aapf.absence_plan_id
   AND acc_ent.accrual_period =
          (SELECT MAX (acc_ent1.accrual_period)
             FROM anc_per_accrual_entries acc_ent1
            WHERE acc_ent1.accrual_period <=
                                     TO_DATE (SUBSTR (TRUNC (SYSDATE), 1, 10))
              AND acc_ent1.person_id = acc_ent.person_id
              AND acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id
              AND acc_ent1.plan_id = acc_ent.plan_id)
   AND asg.primary_flag = 'Y'
   AND asg.assignment_type IN ('E', 'C', 'N', 'P')
   AND asg.assignment_status_type = 'ACTIVE'
   AND per_name.name_type = 'GLOBAL'
   AND ple.status = 'A'
   AND per_typ.LANGUAGE = USERENV ('LANG')
   AND aapf.plan_status = 'A'
   AND aapft.LANGUAGE = 'US'
   --AND acc_ent.end_bal <> 0
   AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
                           AND per_name.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN aapf.effective_start_date
                           AND aapf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN aapft.effective_start_date
                           AND aapft.effective_end_date

Supplier Refund Process in Oracle Fusion Applications

Supplier Refund Process in Oracle Fusion Applications
Please find the below setups for Supplier Refund Process
1. Create Prepayment Invoice 100000
2. Process Payment for Prepayment Invoice 100000
3. Create Standard Invoice 80000
4. Apply Prepayment on supplier Invoice 80000
5. Create the dummy Invoice 20000
6. Apply Prepayment on dummy Invoice 20000
7. Credit Memo 20000
8. Record Payment for Credit Memo 20000

Step 1: Login as Application User
 Step 2: you will be in home page as below
Step 3: we need to navigate to Functional Setup Manager, click on User Name and you will be able to see Setup and Maintenance option it will navigate you to functional setup manger.
 Step 4: below is the functional setup manager window, we could able to see all the offerings as provisioned and enabled
Step 5: we could see Financials Offering as provisioned and enabled,  we need to  click on Implementation projects button in the below screenshot.




Step 6: Search for our Project in the search window

Step 7: Create Prepayment Invoice 100000
Step 8: Enter the required information for creating prepayment invoice and validate


Step 9: Make the payment
Step 10: Enter the required information for payment



Step 11: Create Standard invoice 80000 and validate the invoice


Step 12: Apply the Prepayment invoice
Invoice Validation

Step 13: Create Dummy Invoice for 20000.00 and Validate it 




Step 14: Create Credit Memo Invoice for 20000 and Validate

Step 14 : To Record the 2000 form supplier go to payment work bench select the type has Refund and select the respective invoice 


select the respective invoice then Save and Close 


Wednesday, 13 February 2019

Requisition Creation and Approve process in Fusion Applications

Requisition Creation and Approve
Step 1: Login as Application User

Step 2: you will be in home page as below


Step 3: we need to navigate to Functional Setup Manager, click on User Name and you will be able to see Setup and Maintenance option it will navigate you to functional setup manger.
Step 4: below is the functional setup manager window, we could able to see all the offerings as provisioned and enabled
Step 5: we could see Financials Offering as provisioned and enabled,  we need to  click on Implementation projects button in the below screenshot.


Step 6: Search for our Project in the search window
Step 7:  
Requisition Creation
Navigator Procurement offering click on Purchase Requisitions
Step 8: Click on Enter Requisition Line
 Step 9 : Enter all the required information for creating Requisition


 Step 10 :Verify Requisition is Approved.
Step 11:  
To Make this requisition has PO Click on Purchasing Orders work area
Step 12: Click on Process Requisitions
Step 13: Create PO Automatically like EBS R12 Selesct the Requisition and click on Add to Document Builder
Step 14: Click on Create button Purchase order will create
Step 15: Submit the PO for Approval

Step 16:  
Verify the Purchase Order
Navigation: Task List click on Manage Order



Tuesday, 5 February 2019

Function to format the invoice number in AP

 Function to format the  invoice number in AP:

Please find the below steps for  Function to format the  invoice number in AP

CREATE OR REPLACE FUNCTION XXXX_VALIDATE_INVOICE(
    p_invoice_number VARCHAR2)
  RETURN VARCHAR2
AS
  l_result VARCHAR2(10) := 'INVALID';
BEGIN
  IF ((LENGTH(p_invoice_number) <> 13) OR (SUBSTR(p_invoice_number,5,1) <> '-') OR (NVL (LENGTH (TRIM (TRANSLATE (SUBSTR(p_invoice_number,1,4), '0123456789', ' '))),0) > 0) OR (NVL (LENGTH (TRIM (TRANSLATE (SUBSTR(p_invoice_number,6), '0123456789', ' '))),0) > 0)) THEN
    RETURN l_result;
  ELSE
    RETURN 'VALID';
  END IF;
EXCEPTION
WHEN OTHERS THEN
  RETURN l_result;

END IRON_VALIDATE_INVOICE;