, , , , ,

Running Fast Formula From Backend – Oracle HRMS

Prerequisites:


Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results

Package used to call the fast formula from the backed : ff_exec

Simple Code Snippet below: 

      l_formula_id        NUMBER;
      l_element_inputs    ff_exec.inputs_t;
      l_element_outputs   ff_exec.outputs_t;
      l_in_count          NUMBER;
      l_out_count         NUMBER;
      l_pay_value         NUMBER;

   BEGIN

      BEGIN
         SELECT formula_id
           INTO l_formula_id
           FROM ff_formulas_f
          WHERE formula_name = ‘XX_PAYROLL_FORMULA’
          AND p_effective_date BETWEEN effective_start_date  
             AND effective_end_date;
      EXCEPTION 
      WHEN OTHERS 
      THEN
      DBMS_OUTPUT.put_line (‘NO formula exists’);
      END;

      IF l_formula_id IS NOT NULL

      THEN

— Insert FND_SESSIONS row ( Optional )

  INSERT INTO fnd_sessions
             ( session_id, 
               effective_date
             )
        VALUES 
            ( USERENV (‘sessionid’), 
              p_effective_date
             );

—
— Initialize the formula.
—

ff_exec.init_formula (l_formula_id,
                      p_effective_date,
                      l_element_inputs,
                      l_element_outputs
                      );

—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
    LOOP

    —
    — Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
    —    
       IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
        THEN
           l_element_inputs (l_in_count).VALUE :=
                fnd_date.date_to_canonical (p_start_date);
        END IF;

END LOOP;

—
–Run The formula
—

ff_exec.run_formula (l_element_inputs, l_element_outputs);

—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST

    LOOP
      —
      — Get all the Out Put Values Here L_PAY_VALUE is the out put value
      —
      IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
        THEN
           l_pay_value := l_element_outputs (l_out_count).VALUE;
      END IF;

    END LOOP;

RETURN (l_pay_value);

END;

/by
, , , , , ,

FND Messages In Oracle HRMS

FND MESSAGES:
Here i am Explaining  how to create Fnd Messages  via  E-Business suite  and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message

To create a message in the E-Business suite message library you will need the “Application Developer” responsibility.
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_CUSTOMER_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my first message
Click the save icon.

Retrieving a message using PL/SQL:

In order to retrieve the message from the database we need to use a standard API’s in the FND_MESSAGE package.
An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Optional)

Below is the PL/SQL Block to retrive the message

DECLARE
 my_message VARCHAR2(100);

 BEGIN

  –Initialize Apps Session
  fnd_global.apps_initialize( user_id      => 1234
                             ,resp_id      => 1235
                             ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility‘;
  */
 
  –Clear the existing session
  FND_MESSAGE.CLEAR;

  –Tell e business suite which message you want (custom application short name/message name) 
  FND_MESSAGE.SET_NAME(‘XXERP’,’XX_CUSTOMER_MSG’);

  –Retrieve the message
  my_message := FND_MESSAGE.GET;  

  –Output the message
  DBMS_OUTPUT.PUT_LINE(my_message);
 END;

Output for the Above Block Is : This is my first message

Using Tokens in the message:

The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time.
Open the E-Business Suite message create a New Message
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_UNAME_TOKEN_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my second message and the Token User name is &USERNAME
Click the save icon.
Note: In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. &USERNAME
Retrieving message With Token Substitution
Here USERNAME is called as TOKEN, we will Add the value dynamically

Example Block  is below:

DECLARE
 my_message VARCHAR2(100);
BEGIN
 –Initialize Apps Session
 fnd_global.apps_initialize( user_id      => 1234
                            ,resp_id      => 1235
                            ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility’;
  */
 
 –Clear the existing session
 FND_MESSAGE.CLEAR;

 –Tell e business suite which message you want (Application short name/message name)
 FND_MESSAGE.SET_NAME(‘XXERP‘,’XX_UNAME_TOKEN_MSG’);

  –Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN(‘USERNAME’,FND_GLOBAL.USER_NAME);

 –Retrieve the message
 my_message := FND_MESSAGE.GET;

 –Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;

Out put for above block is : 

This is my second message and the Token User name is IAMKRISHNA

Downloading and Uploading Messages using the Generic Loader

To download our example message we would use the following command at the Unix prompt on the mid-tier:
 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’PER’ MESSAGE_NAME=”XX_UNAME_TOKEN_MSG”

To Upload our example message we would use the following command at the Unix prompt on the mid-tier:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt

/by
, , , , , , , , , , ,

GL Accounting Cycle and Integration

General Ledger Accounting Cycle:
1. Open Period
2. Create/Reverse journal entries
3. Post Journals
4. Review
5. Revaluate/Translate
6. Consolidate
7. Review/correct balances
8. Run reports
9. Close the periods

Integration of General Ledger with other modules:

Oracle General Ledger integrates with other modules. Following is the list of modules along with the details that flow to the General Ledger.
1. Payables sends Invoices, payments, adjustments, realized gain/loss on foreign currency and invoice price variance to GL.

2. Receivables sends invoices, payments, adjustments, debit memos, credit memos, cash, charge backs and realized gain and loss on foreign currency to GL.

3. Assets sends capital and construction in process asset additions, cost adjustments, transfers, retirements, depreciation and reclassifications.

4. Purchasing sends accruals or receipts not invoiced, purchase orders, final closes and cancellations.

5. HRMS sends employee details.

6. Payroll sends salary, deductions and tax information.

7. Inventory sends cycle counts, physical inventory adjustments, receiving transactions, delivery transactions, delivery transactions, intercompany transfers, sales order issue, internal requisitions, sub-inventory transfers and Cost of Goods Sold.

/by
, , , , , , , , , ,

Important Profiles During Implementation

 Important Profiles
1. Receiving transaction Processor concurrent not visible
TP: INV Transaction processing mode                 —         On-line

RCV: Processing Mode                                   —         On-Line  set to User & Resp.

2. Auto creates tools copy document next document form not pop-up.

PO: Display the Autocreated Document               —         No to Yes

PO: Display the Autocreated Quotation                —         No to Yes

PO: Allow Buyer Override in Autocreate Find        —         Yes or No

PO: Allow Category Override in Autocreate Find   —         Yes or No

PO: Allow Referencing CPA Under Amendment    —         Yes or No

PO: Allow Requisition Approval Forward Action     —         Yes or No

HR: Supervisor Hierarchy Usage —Use Assignment-based  Supervisor Hierarchies
                                                     Use Person-based Supervisor Hierarchies

HR: Display Position Hierarchy                           —         Yes or No

PO: Allow Requisition Approval Forward Action     —         Yes or No       

3. Requisition Import required Internal & Purchase Requisition

PO: Legal Requisition Type                                             —         Both

PO: Restrict Requisition line modify to quantity split         —         No

RCV: Allow routing override                                             —         Yes

OM: Display New Order after Copy                                  —         Yes

OM: Item Change Honors Frozen Price                            —         Yes

OM: Return Item Mismatch Action                                   —         Allow

4. Sales Order Number not automatic sequencing

Sequential Numbering                                                    —         Always Used

AR:Maximum lines per AutoInvoice                                  —        20

AR: AutoInvoice Gather Statistics                                    —         No

AR: Use Invoice Accounting For Credit Memos                 —         Yes

eBTax: Allow Override of Customer Exemptions                —         Yes

WIP:Job Name Updatable                                               —         Yes to No

Drop ship

OM: Population Of Buyer Code For Dropship Lines           —         Order Creator     (not mandatory)

Requisition import & Create release both should run at a time

PO: Release During ReqImport                                        —         Yes

Localization Profiles

Responsibility    —          Purchasing

JG: Application                          —         Purchasing

JG: Product                               —         Asia/Pacific Localizations

JG: Territory                              —         India

JG: Company Operating Unit       —         Operating Unit Name

Responsibility    —          Inventory

JG: Application                          —         Inventory

JG: Product                               —         Asia/Pacific Localizations

JG: Territory                              —         India

JG: Company Operating Unit       —         Operating Unit Name

Responsibility    —          Order Management

JG: Application                          —         Order Management

JG: Product                               —         Asia/Pacific Localizations

JG: Territory                              —         India

JG: Company Operating Unit       —         Operating Unit Name

Any changes from SO Header level it won’t affect to line level need to set the profile – R12 Futures:-

OM: Sales Order Form:
Cascade Header Changes to Line               —    Automatic

QP: Item Validation Organization               —    IMO

OM: Item Validation Organization             —    IMO setting up both should Same
                                     (then only Item should be listing in price list and modifier)

FND: Record History Enabled

FND: Diagnostics                     —    Yes (Customer web page view record history

Personalize Self-Service Defn–>

                                            No to Yes (Customer web page view record history)

HZ: Enable Duplicate Prevention at Party Creation —   Disabled

                                                                               Organization only

                                                                               Organization and Person

                                                                                Person only

HZ: Enable DQM Party Search                          —    Yes

ASO : Price List Override                                   —    Yes

PO: Allow Buyer Override in Autocreate Find       —     Yes

Transaction type added Customer PO mandatory —    Check box enabled
                                                                              Organization Type
In a Human Resources responsibility, the navigation is Other Definitions > Application Utilities Lookup.

    Works
    Non-Works
    Dump
    Branch
    Warehouse
    Goodown

1. Define FOB: AR à Purchasing lookup à Query FOB      —         Add the Value
                                                                             
2. Define Freight carrier: Order Management à Setup à Shipping à Freight Carrier, Cost Type à Freight Carrier      —         Add the value

3. Define Freight Terms: Order Management à Setup à Quick Codes à Order Management à Query Freight Terms   —   Add the Value

AR: AutoInvoice Gather Statistics               —         No to Yes

( AutoInvoice Master Program concurrent error)

AR:Maximum lines per AutoInvoice worker    —         10000 (null to 10000)

Credit Memo Profile

AR: Use Invoice Accounting For Credit Memos         —        Null to Yes

AR: Transaction Batch Source                                 —         Null to
                                                                                    
INV: Advanced Pricing for Inter-Org Transfers        —         Null to Yes

INV: Advanced Pricing for Inter-Org Transfers        —         No

Price list security:-
Price list and Modifier need to set to Operating Unit level or Global
QP: Security Control                                                —         OFF / ON

QP: Security Default Maintain Privilege                    —  Global
                                                                                      None
                                                                                      Operating Unit
                                                                                      Responsibility
                                                                                      UserQP: Security Default ViewOnly Privilege                   —   Global
                                                                                      None
                                                                                      Operating Unit
                                                                                      Responsibility
                                                                                      User

CST: Transfer Pricing Option                                     —         No

 Internal Sales Order Price Defaulted from Inventory Item cost à Sales Order à Line items à pricing tab à Calculate Price flag set Freeze price to Calculate price.

OM: E-Mail Required on new Customers  — No to Yes (When create new customer or add Contact)

Parallel Pick Future R12
Set the profile site level to
1. WSH: Number of Pick Release Child Processes’  –  50

Consigned Inventory functionality set this Profiles
INV: Allow Expense to Asset Transfer = Yes.

Check following 2 condition for your environment.
1) OE: Autobackorder is only effective if the profile, OE: Reservation = Yes
2) only affects items in a kit, model, or ship set during Pick Release.

/by
, , , , ,

Oracle 11i/R12 Important Queries in Concurrent , Inventory, Purchaing , HR, Payrol , OPM

To Find Duplicate Item Category Code


SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition


SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management


SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varry)


SELECT ood.organization_name,
segment1|| ‘-‘|| segment2|| ‘-‘|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varry)


SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup

select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.

select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to Find Responsibilities assigned to particular user.


SELECT b.responsibility_name NAME
FROM apps.fnd_user_resp_groups a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND u.user_id = (select user_id from FND_USER where user_name=:User_name)
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL

Query To find the Request Run By Concurrentselect b.user_concurrent_queue_name, c.request_id
from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.concurrent_queue_id = b.concurrent_queue_id
and a.concurrent_process_id = c.controlling_manager;

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliverselect sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query(select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item,

Query to Find out On Hand Quantity of specific Item Oracle inventory
(select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id) onhand,
— Qty Issued by X No clue what you want here
–Qty On Order,Expected deivery date(select sum(ordered_quantity),scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info,
–Toatl Received Qty
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0) tot_rec_qty,
–Total received Qty in 9 months
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_rec_qty_9mths,
–Total issued quantity in 9 months(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) avg_month_consumption; Find detail of specific Applications Running in System System Administrator

SELECT application_id,APPLICATION_NAME
FROM fnd_application_tl
WHERE application_name = ‘Purchasing’
AND rownum = 1;

Find the Request Groups and Concurrent ProgamsSELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
–and c.USER_CONCURRENT_PROGRAM_NAME like ‘%Purchase Order%’
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id;

Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code,  Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-‘|| msib.segment2|| ‘-‘|| msib.segment3|| ‘-‘|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)

/by