Salary Sum Query

Sum of all earnings in payroll:

/* Formatted on 2011/10/27 13:36 (Formatter Plus v4.8.8) */

SELECT pee.assignment_id, pee.element_entry_id, pee.element_link_id,
pel.element_type_id, pet.element_name, pettl.reporting_name,
DECODE (pet.element_name,
'Basic_New', 'Basic Salary',
'HRA', 'House Rent Allowance',
pet.element_name
) display_name,
(SELECT MAX (screen_entry_value)
FROM pay_element_entry_values_f
WHERE element_entry_id = pee.element_entry_id) screen_entry_value
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_element_classifications pec
WHERE pee.assignment_id = :assignment_id
AND TRUNC (SYSDATE) BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND TRUNC (SYSDATE) BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.processing_type = 'R'
AND pet.element_type_id = pettl.element_type_id
AND TRUNC (SYSDATE) BETWEEN peev.effective_start_date
AND peev.effective_end_date*/
AND pet.classification_id = pec.classification_id
AND pec.classification_name = 'Earnings'
ORDER BY DECODE (pet.element_name, 'Basic_New', 1, 'HRA', 2, 3)

FOrmsPersonalization:To restrict a receipt method LOV for a responsibility

In Order Management Responsibility go to quick sales order menu

• create a sales order
• click on actions
• Choose payments
• form main menu go to help-diagnostics-custom code-personalize

1.Add a Sequence with description,

 

 

Under Condition ;
trigger event ; when_new_item_instance
Trigger_object ; blockname.filed-name(In which u need the personalization)
Condition:optional,can give any default values e.g:
Block_name.field_name=’Value’

 

e.g: WHEN-NEW-ITEM-INSTANCE;
OE_PAYMENTS.RECEIPT_METHOD:OE_PAYMENTS.PAYMENT_TYPE_CODE='CASH'
Under Level Choose the responsibility that you want to assign.

 

 

 

Now go to actions tab

 

2.The first sequence is built-in and the second sequence is property for a LOV
e.g:Seq 10
type;Builtin
In Builtin Type Choose;Create record group for query
Argument;Enter your query
NOTE:
1. If u r changing the existing record group the you need to check the exiting record group query and develop your query by choosing same columns and with your own condition appropriately.
2. U need to refer the the LOV that u r Personalizing.
Group Name;Give a custom name
E.g. query
**-For type Cash**
SELECT rm.NAME,rm.receipt_method_id
/*Have choosen the same columns as per the default LOV Record group query and changed the where clause as per my requirement*/
FROM
ar_receipt_methods rm,
oe_payments b,
ar_receipt_method_accounts_all rma,
fnd_lookup_values flv,
ce_bank_acct_uses_all cba
where
rm.receipt_method_id=b.receipt_method_id
AND rm.receipt_method_id = rma.receipt_method_id
AND flv.lookup_type like '%PAYMENT_METHODS%' --Existing LOV Name--
AND rma.remit_bank_acct_use_id = cba.bank_acct_use_id and
rma.org_id = cba.org_id
AND flv.enabled_flag = 'Y'
AND Flv.LOOKUP_CODE not IN('CCR')--Condition for Where Clause--
AND rm.receipt_method_id IN('2012')
GROUP BY rm.NAME,rm.receipt_method_id

 

3.Create property for the built-in type

 

My desp; Restrict Receipt Method for cash
Object_type;Cash

 

Target_Object;Payment_Methods
Property_name;Group Name
Value;Give the custom name that u have given for the Builtin Group Name

 

And ITS DONE !!!!

 

 

 

 

 

FND_CANONICAL

In HRMS we do use date with timestamp for EIT ans SIT segments.

To change the format od the date we can use fnd_date.canonical_to_date default function:

e.g:

before using fnd_date function

SELECT pei_information2 Visit_date from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

After using fnd_date function:

SELECT fnd_date.canonical_to_date(pei_information2) from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 And you can use to_chat over uit to make it in words as

SELECT TO_CHAR(fnd_date.canonical_to_date(pei_information2),'Month ddTH, YYYY') from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 

Find log using Request id

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = 515172

Adding LOV list to forms

1.Create a record group with the query required for lov list.
2.Create a LOV with the created Record group and give the subclass information as LOV
3.Map the field from the form in LOV mapping property field.