Friday, April 25, 2014

Update Employee Organization At PA Expenditure Item Level

SELECT hou.organization_id,hou.name
           INTO l_new_organization_id, l_incurred_org
           FROM per_all_assignments_f papf,hr_all_organization_units hou
          WHERE papf.organization_id=hou.organization_id
AND trunc(sysdate) between papf.effective_start_date and trunc(nvl(papf.effective_end_date,sysdate))
and papf.person_id=:p_person_id
 AND hou.business_group_id = 571;

UPDATE    pa_expenditure_items_all
                  SET cc_prvdr_organization_id = l_new_organization_id,
                      last_update_date = SYSDATE,
                      last_updated_by = :p_user_id
                WHERE expenditure_id = :p_expenditure_id AND expenditure_item_id = :p_expenditure_item_id AND org_id = :p_org_id
            RETURNING cc_prvdr_organization_id
                 INTO l_cc_prvdr_organization_id;

UPDATE pa_expenditures_all
set incurred_by_organization_id=l_new_organization_id,
last_update_date = SYSDATE,
                      last_updated_by = :p_user_id
 WHERE expenditure_id = :p_expenditure_id AND org_id = :p_org_id
RETURNING incurred_by_organization_id
                 INTO l_incurred_by_organization_id;

Expenditure Items Not Cost Distributed For An Employee

     SELECT PAPF.PERSON_ID,HOU.NAME INCURRED_ORG,
       PE.INCURRED_BY_ORGANIZATION_ID,
       PEI.CC_PRVDR_ORGANIZATION_ID ,
       NVL(CDL.TRANSFER_STATUS_CODE,' ') TRANSFER_STATUS_CODE ,
       PE.EXPENDITURE_ID,
       PE.EXPENDITURE_STATUS_CODE,
       PE.EXPENDITURE_CLASS_CODE,
       PE.EXPENDITURE_GROUP,
       PE.INCURRED_BY_PERSON_ID,
       PAPF.FULL_NAME,
       PEI.EXPENDITURE_ITEM_ID,
       PEI.EXPENDITURE_TYPE,
       PEI.EXPENDITURE_ITEM_DATE,
       PEI.COST_DISTRIBUTED_FLAG,
       NVL(CDL.TRANSFER_REJECTION_REASON,' ') TRANSFER_REJECTION_REASON,
       PEI.JOB_ID,
       PEI.SYSTEM_LINKAGE_FUNCTION,
       PEI.ORIG_TRANSACTION_REFERENCE,
       PEI.TRANSACTION_SOURCE,
       PEI.ORG_ID,
       CDL.LINE_NUM,
       CDL.LINE_TYPE
FROM   PA_COST_DISTRIBUTION_LINES_ALL CDL,
       PA_EXPENDITURES_ALL PE,
       PER_ALL_PEOPLE_F PAPF,
       PA_EXPENDITURE_ITEMS_ALL PEI,
       HR_ALL_ORGANIZATION_UNITS HOU
WHERE  1 = 1
AND    PE.INCURRED_BY_PERSON_ID = PAPF.PERSON_ID
AND    PE.EXPENDITURE_ID = PEI.EXPENDITURE_ID
AND    CDL.EXPENDITURE_ITEM_ID(+) = PEI.EXPENDITURE_ITEM_ID
AND    PEI.ORG_ID = PE.ORG_ID
AND    PEI.ORG_ID = :p_org_id
AND    PAPF.FULL_NAME = :p_employee_name
AND    HOU.ORGANIZATION_ID = PE.INCURRED_BY_ORGANIZATION_ID
and    pei.COST_DISTRIBUTED_FLAG <> 'Y';

How to change the status of Expenditure Batch

select EXPENDITURE_GROUP,EXPENDITURE_GROUP_STATUS_CODE,SYSTEM_LINKAGE_FUNCTION,ORG_ID
   from pa_expenditure_groups_all
   where expenditure_group in (<Expenditure Batch Name>)
   and org_id=:p_org_id;

       
update pa_expenditure_groups_all
          set EXPENDITURE_GROUP_STATUS_CODE = 'SUBMITTED',
          last_update_date = SYSDATE,
          last_updated_by = :p_user_id
          where EXPENDITURE_GROUP = :P_EXPENDITURE_GROUP
         and ORG_ID = :P_ORG_ID
         returning EXPENDITURE_GROUP_STATUS_CODE
         into lc_status ;

OTL Create Timekeeper Groups, Link Timekeeper and Add Employees to Timekeeper Group

-- Create the new Timekeeper Group
            x_object_version_number := 1;

            SELECT hxc_tk_groups_s.NEXTVAL
              INTO l_tk_group_id
              FROM DUAL;

            INSERT INTO hxc_tk_groups
                        (tk_group_id, tk_group_name, tk_resource_id, object_version_number, business_group_id, creation_date, created_by, last_updated_by,
                         last_update_date, last_update_login
                        )
                 VALUES (l_tk_group_id, i.new_timekeeper_group_name, v_tk_resource_id, x_object_version_number, 571, SYSDATE, l_user_id, l_user_id,
                         SYSDATE, l_user_id
                        );

            SELECT hxc_tk_group_queries_s.NEXTVAL
              INTO l_tk_group_query_id
              FROM DUAL;

            INSERT INTO hxc_tk_group_queries
                        (tk_group_query_id, tk_group_id, group_query_name, include_exclude, system_user, object_version_number, creation_date, created_by,
                         last_updated_by, last_update_date, last_update_login
                        )
                 VALUES (l_tk_group_query_id, l_tk_group_id, 'System: Included Resources', 'I', 'S', x_object_version_number, SYSDATE, l_user_id,
                         l_user_id, SYSDATE, l_user_id
                        );

 
--How to Change the Timekeeper of the existing Group
               l_status := 'U';

               UPDATE hxc_tk_groups
                  SET tk_resource_id = v_tk_resource_id,
                      object_version_number = object_version_number + 1,
                      last_updated_by = l_user_id,
                      last_update_date = SYSDATE
                WHERE tk_group_name = i.new_timekeeper_group_name;



--Add employees to timekeeper group
        INSERT INTO hxc_tk_group_query_criteria
                     (tk_group_query_criteria_id, tk_group_query_id, criteria_type, criteria_id, object_version_number, creation_date, created_by,
                      last_updated_by, last_update_date, last_update_login
                     )
              VALUES (hxc_tk_group_query_criteria_s.NEXTVAL, v_new_tk_group_query_id, 'PERSON', v_person_id, 1, SYSDATE, l_user_id,
                      l_user_id, SYSDATE, l_user_id
                     );

R12: Set Org Context

EXECUTE IMMEDIATE 'alter session set NLS_LANGUAGE=''AMERICAN''';

mo_global.set_policy_context ('S', :p_org_id);
mo_global.init (<Application Short Name>);

OTL Timekeepers Not Linked to OTL Responsibility

SELECT tg.tk_group_name timekeepr_group_name, papf2.employee_number,papf2.full_name
        FROM hxc_tk_groups tg, per_all_people_f papf2, per_all_assignments_f paaf, hr_all_organization_units haou
       WHERE tg.tk_resource_id = papf2.person_id
         AND paaf.person_id = papf2.person_id
         AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date
         AND papf2.current_employee_flag = 'Y'
         AND haou.organization_id = paaf.organization_id
         AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
         AND paaf.organization_id IN (SELECT     org.organization_id
                                            FROM hr_all_organization_units org, per_org_structure_elements pose
                                           WHERE 1 = 1 AND org.organization_id = pose.organization_id_child
                                      START WITH pose.organization_id_parent = :p_org_id                                                                  --entity AT
                                      CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
         AND NOT EXISTS (
                SELECT 1
                  FROM fnd_user fu, fnd_user_resp_groups furg, fnd_responsibility_tl fr
                 WHERE 1 = 1
                   AND fr.responsibility_name = <'OTL Timekeeper Entry'>
                   AND fu.employee_id = papf2.person_id
                   AND furg.user_id = fu.user_id
                   AND furg.responsibility_id = fr.responsibility_id
                   AND furg.responsibility_application_id = fr.application_id
                   AND fr.LANGUAGE = 'US')
ORDER BY 1,2,3;

OTL Timekeeper Group, Timekeeper and Employees

select timekeepr_Group_Name,timekeeper_name ,Timekeeper_corporation_number timekeeper_tgi, Employee_Name ,employee_corporation_Number employee_tgi,a.name org_name
 from (select employee_number,npw_number,full_name,paaf.organization_id,haou.name
from per_all_people_f papf,per_all_assignments_f paaf,hr_all_organization_units haou
where paaf.person_id=papf.person_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and current_employee_flag='Y'
and haou.organization_id=paaf.organization_id
and  paaf.organization_id in
(SELECT
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND org.organization_id = pose.organization_id_child
START WITH
pose.organization_id_parent =:p_org_id
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
))a,
(select tg.TK_GROUP_NAME timekeepr_Group_Name,papf2.full_name timekeeper_name ,papf2.employee_Number Timekeeper_corporation_number,
papf1.employee_Number employee_corporation_Number,papf1.full_name Employee_Name
from hxc_tk_groups tg,hxc_tk_group_queries tkgq,hxc_tk_group_query_criteria tc,per_all_people_f papf1,per_all_people_f papf2,
hxt_rotation_plans hrp
where tg.tk_group_id=tkgq.tk_group_id
and tc.TK_GROUP_QUERY_ID=tkgq.TK_GROUP_QUERY_ID
and tc.CRITERIA_ID=papf1.person_id
and tg.TK_RESOURCE_ID=papf2.person_id
and hrp.name(+)=papf1.employee_number
and sysdate between papf1.effective_start_date and papf1.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date)b
where nvl(a.employee_number,a.npw_number)=b.employee_corporation_Number
order by 1,3,4