Receivables to GL Link in 11i

SELECT je_header_id, line_number, trx_number, myrank, item_number,
       customer_number, customer_name, SOURCE, CATEGORY, batch_name,
       transaction_date, posted_date, period_name, currency_code,
       account_number, description,
       CASE
          WHEN myrank = 1
             THEN debits
          ELSE NULL
       END debits, CASE
          WHEN myrank = 1
             THEN credits
          ELSE NULL
       END credits
  FROM (SELECT   rcta.trx_number, rcta.customer_trx_id, gh.je_header_id,
                 ac.customer_number, ac.customer_name, gh.je_source SOURCE,
                 gh.je_category CATEGORY, gh.NAME batch_name,
                 TO_CHAR (gh.default_effective_date,
                          'DD-MON-YYYY'
                         ) transaction_date,
                 TO_CHAR (gh.posted_date, 'DD-MON-YYYY') posted_date,
                 gh.period_name period_name, gh.currency_code currency_code,
                 gl.je_line_num line_number,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                 || '.'
                 || gcc.segment6 account_number,
                 gh.description description, gl.accounted_dr debits,
                 gl.accounted_cr credits, rctla.inventory_item_id,
                 (SELECT segment1
                    FROM mtl_system_items_b msib
                   WHERE msib.inventory_item_id =
                                          rctla.inventory_item_id
                     AND msib.organization_id = rctla.warehouse_id)
                                                                  item_number,
                 DENSE_RANK () OVER (PARTITION BY gh.je_header_id, gl.je_line_num ORDER BY rctlg.cust_trx_line_gl_dist_id)
                                                                    AS myrank
            FROM gl_je_headers gh,
                 gl_je_lines gl,
                 gl_code_combinations gcc,
                 fnd_user fu,
                 gl_sets_of_books gsob,
                 gl_import_references gir,
                 ra_customer_trx_all rcta,
                 ra_cust_trx_line_gl_dist_all rctlg,
                 ra_customer_trx_lines_all rctla,
                 ar_customers ac
           WHERE gh.je_header_id = gl.je_header_id
             AND gl.code_combination_id = gcc.code_combination_id
             AND gh.last_updated_by = fu.user_id
             AND gh.period_name = :p_period
             AND gh.status = 'P'
             AND gh.set_of_books_id = gsob.set_of_books_id
             AND gsob.set_of_books_id = 1
             AND gh.je_source = 'Receivables'
             AND gir.je_header_id = gh.je_header_id
             AND gir.je_line_num = gl.je_line_num
             AND gir.je_batch_id = gh.je_batch_id
             AND TO_CHAR (gir.reference_2) = TO_CHAR (rcta.customer_trx_id)
             AND TO_CHAR (gir.reference_3) =
                                      TO_CHAR (rctlg.cust_trx_line_gl_dist_id)
             AND rcta.customer_trx_id = rctlg.customer_trx_id
             AND rctla.customer_trx_line_id = rctlg.customer_trx_line_id
             AND rctlg.customer_trx_id = rctla.customer_trx_id
             AND rctla.customer_trx_id = rcta.customer_trx_id
             AND rcta.bill_to_customer_id = ac.customer_id(+)
        ORDER BY gh.je_header_id, gl.je_line_num)

Script for getting Sales Tax Transactions

SELECT DISTINCT aia.invoice_date transaction_date,
                aia.invoice_num transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = UPPER (zl.event_class_code)
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                aia.gl_date accounting_date,
                psv.vendor_name third_party, pss.vendor_site_code site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ap_invoices_all aia,
                ap_invoice_lines_all ail,
                poz_suppliers_v psv,
                poz_supplier_sites_v pss,
                zx_lines zl,
                xle_entity_profiles hou,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE aia.invoice_id = ail.invoice_id
            AND aia.vendor_id = psv.vendor_id
            AND aia.vendor_site_id = pss.vendor_site_id
            AND ail.invoice_id = zl.trx_id(+)
            AND ail.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = aia.legal_entity_id           
            AND zl.cancel_flag <> 'Y'
            AND aia.cancelled_date IS NULL
UNION
SELECT DISTINCT rct.trx_date transaction_date,
                rct.trx_number transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = zl.event_class_code
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                gl_date accounting_date,
                hca.account_name third_party,
                NVL (hps.party_site_name, hps.party_site_number) site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code ) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ra_customer_trx_all rct,
                ra_customer_trx_lines_all rctl,
                zx_lines zl,
                xle_entity_profiles hou,
                hz_cust_site_uses_all hcsua,
                hz_cust_acct_sites_all hcasa,
                hz_cust_accounts hca,
                hz_party_sites hps,
                (SELECT DISTINCT customer_trx_line_id, gl_date,
                                 customer_trx_id
                            FROM ra_cust_trx_line_gl_dist_all) dist_tab,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE rct.customer_trx_id = rctl.customer_trx_id
            AND rct.customer_trx_id = zl.trx_id(+)
            AND rctl.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = rct.legal_entity_id
            AND zl.entity_code = 'TRANSACTIONS'
            AND zl.cancel_flag <> 'Y'
            AND rctl.line_type = 'LINE'
            AND hcsua.site_use_id = rct.bill_to_site_use_id
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
            AND hcasa.cust_account_id = hca.cust_account_id
            AND hps.party_site_id = hcasa.party_site_id
            AND dist_tab.customer_trx_id = rct.customer_trx_id
            AND dist_tab.customer_trx_line_id = rctl.customer_trx_line_id
            

Script for Extracting InterCompany batch details

This summary is not available. Please click here to view the post.

Script to get Project Listing

SELECT   pt.project_id, ppa.NAME project_name,
         ppa.description project_description, ppa.segment1 project_number,
         pt.task_number,
         ppa.project_status_code project_status,
         (SELECT DISTINCT (SELECT person_number
                             FROM per_people_x ppx
                            WHERE ppx.person_id =
                                                ppn.person_id)
                     FROM pjf_project_parties proj_dir,
                          pjf_proj_role_types_tl prt,
                          per_all_assignments_f paf,
                          per_person_names_f ppn
                    WHERE proj_dir.project_id = ppa.project_id
                      AND proj_dir.project_role_id = prt.project_role_id
                      AND prt.project_role_name = 'Project Manager'
                      AND proj_dir.resource_source_id = paf.person_id
                      AND paf.assignment_status_type = 'ACTIVE'
                      AND paf.person_id = ppn.person_id
                      AND ppn.name_type = 'GLOBAL'
                      AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                              AND NVL (paf.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active
                                              AND NVL
                                                    (proj_dir.end_date_active,
                                                     SYSDATE + 1
                                                    )
                      AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
                                              AND NVL (ppn.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND paf.primary_flag = 'Y'
                      AND ROWNUM < 2) project_manager
    FROM pjf_tasks_v pt ,
         pjf_projects_all_vl ppa
   WHERE 1 = 1 AND ppa.project_id = pt.project_id(+)         

Fusion HCM - Payroll Setup Sequence

Below are the Setup Sequence for Fusion Payroll. It differs as per the Client requirement.

1. Manage Payroll Calculation Card

2. Manage Payment Methods

3. Manage Salary Basis

4. Manage Consolidation Groups

5. Manage Payroll Definitions

6. Manage Payroll Balances

7. Manage Object Groups

8. Manage Payroll Event Groups

9. Manage Elements

Fusion HCM - Absence Management Setup Sequence

Below are the Setup Sequence for Fusion Absence Management. It differs as per the Client requirement.

1. Manage Work Shifts

2. Manage Work Workday Patterns

3. Manage Work Schedules

4. Manage Work Schedule Assignment Administration

5. Manage Geography Trees

6. Manage Calendar Events

7. Manage Eligibility Profiles

8. Manage Derived Factors

9. Manage Absence Reasons

10. Manage Absence Certifications

11. Manage Repeating Time Periods

12. Manage Absence Plans

13. Manage AbsenceTypes

Approval Management in Fusion

Approval Management in Fusion: Points to Ponder:

Oracle Business Process Management (BPM):

The BPM Worklist Application is a web-based application that lets users access tasks assigned to them and perform actions based on their roles in the approval process. Administrators can set up approval groups and task-level configuration via an administration portal or through the Oracle BPM Worklist.

As in EBS suite, we have different types of transactions (“Purchase Requisition Approval”) we have tasks defined for each Transaction in Fusion for example for I-Expenses the task is “Manage Expense Approvals” and for Procurement the task is “Manage Requisition Approvals”.

How to configure or edit Manage Requisition Approval process:

Navigate to: Setup and Maintenance > Select: Procurement > Setup button > Functional Areas: procurement> Show: All Tasks > Manage Requisition Approvals > Oracle BPM Worklist as shown below

Click on the Manage Requisition Approvals which will take you to Mange Requisition Approval Set up page as shown below:

You can create new rules by clicking Edit Rules button as shown in above screen shot or by selecting create option from Actions Drop Down.

An approval rule is composed of the following:

1.       Rule Name

2.       Condition

3.       Action

Rule Name:

The Rule Name is used to identify the approval rule.

• In cases where there are large numbers of rules, users can filter search results in the Query by Example fields.

Condition:

The Condition indicates when the approval rule will be applied.

A rule can contain multiple conditions, and you can select the “and” or “or” operators to indicate if all conditions in the approval rule must be true or if only one condition must be true for the approval rule to apply.

For example, if the requisition amount is less than 10,000 and requisitioning BU is US Business Unit. A condition can be defined using attributes seeded in a dimension or user-defined attributes. Please look into the section on how to create User Defined Attributes.

You can create the condition by using predefined Approval Task attributes or by using User Defined Attributes.

You can serch for any standard attributes and can create the condition as shown below.

Action:

An action defines what needs to be done if the conditions of a rule is met.

It identifies if approvals or FYI notifications are required and the type of users needed to approve or receive notification for a document.

The supported action types are: -

·         Approval required

·         Approval actions required from the recipients of the approval tasks - Automatic

·         Automatically approve or reject the approval task - Information only

·         FYI notifications sent to recipients

In the below screenshot you can see we created Action based on Condition

The Supported Routing Methods are:

·         Approval Group

·         Job-Level

·         Position Hierarchy

·         Single Approver

·         Supervisory Hierarchy

User-Defined Attributes

Organizations have different requirements for document approvals. Some of these requirements may include the need to perform currency conversions for amount based attributes or derive approvers based on a roll-up amounts across lines within a document with common attributes. These user-defined attributes are managed and used within a specific approval task.

There are two types of user-defined attributes they are:

·         Currency Based

·         Summation.

Currency Based on User-Defined Attributes:

You can define currency based attributes such that amounts indifferent currencies are converted to a specific currency for the rule evaluation.

For example, Acme Corp. creates an attribute “USD Requisition Amount” and uses the attribute for rule conditions, such as if USD Requisition Amount is less than 500, approvals from the preparer’s manager is required and so forth. If a requisition created in Mexico is submitted, where the functional currency is pesos, the requisition amount will first be converted to USD before rules are evaluated.

Summation User-Defined Attributes:

Customers who need to perform approval routings based on data aggregated across one or more attributes when a document contains more then one line, schedule or distribution can create summation user-defined attributes.

For example, Acme Corp’s approval policy requires the number of IT approvers to be based on the total amount of IT requests within a requisition. If the total IT amount is less than 1200 USD, then the IT manager needs to approve. If the IT amount exceeds 1200 USD, then the IT manager, director, and VP need to approve the document.

Deploying Approval Rules:

Any changes made to the rules or any rules that have been newly added have to be deployed for them to be effective.

·         Deploying approval rules will require users to Click on the Deploy button on the Edit Rules and on the Manage Approval Rules page.

·         If users do not intend to deploy the changes that were made to the rules, they can choose to click on the

·         Discard button to revert the rules to the last deployed state.

Save: Save or Save and Close will save the updated rules to the database without deploying them. Users can edit and save rules across multiple participants before deploying them.

Deploy: Deploy updates the rules engine and makes all the rule changes across all participants effective. It is displayed on both the Manage Approval Rules page and the Edit Rules page. The Deploy button is disabled by default and is enabled when there are rules that have been setting up Document Approvals updated and saved.

Discard: Discard is displayed on both the Manage Approval Rules page and the Edit Rules page. It is disabled by default and is enabled when there are rules that have been updated and saved. Discard removes any rule changes that were saved and the rules will go back to the last deployed state.

Once You Click on Deploy The below warning Message will Appear.

Click on the Confirmation Dialog Message.

Once the changes are deployed, the rule will be effective once by enabling (Enable) button adjacent to “Edit” button where you created or edited the rule as shown below.