-

 Users attached to Responsibilities and having Approval limits Null 

select hou.name "Org name",

       u.user_name "User Name",

       u.description  "User Description",

       u.start_date "User Start Date",

       u.end_date "User End Date",

       pe.full_name "Employee Name",

   pe.person_id "Employee id",

       pe.effective_start_date "Employee Start Date",

       pe.effective_end_date "Employee End Date",

       (SELECT name

          FROM apps.per_all_positions pap

         WHERE pap.position_id = pas.position_id) "Employee Position",

         amount_limit "PR Approval Amount"

  from apps.fnd_user u,

       apps.fnd_user_resp_groups_direct furg,

       apps.fnd_responsibility_tl frt,

       apps.fnd_responsibility  fr,

       apps.fnd_application_tl  fat,

       apps.fnd_application     fa,

       apps.per_all_people_f pe,

       apps.per_all_assignments_f pas,

       apps.hr_operating_units hou,

        (SELECT paf.person_id,

  paf.assignment_type,

      pcr.amount_limit,

              paf.position_id,

              f.org_id

     FROM apps.per_all_assignments_f paf,

      apps.per_jobs pj,

              apps.po_position_controls_all f,

      apps.po_control_groups_all g,

      apps.po_control_rules pcr

    WHERE TRUNC(SYSDATE) BETWEEN TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)

      AND paf.primary_flag     = 'Y'

      AND NVL(paf.job_id, -99) = pj.job_id

          AND pj.job_id    = f.job_id

          AND paf.position_id    = f.position_id

          AND f.control_group_id   = g.control_group_id

          AND f.control_function_id = 8 --'Approve Purchase Requisitions'

  AND nvl(trunc(f.end_date),trunc(SYSDATE)) >= trunc(SYSDATE)

      AND g.enabled_flag       = 'Y'

      AND pcr.control_group_id = g.control_group_id

          AND nvl(pcr.inactive_date, sysdate + 1) > sysdate

  AND pcr.object_code      = 'DOCUMENT_TOTAL') PRD

 WHERE SYSDATE BETWEEN TRUNC(u.start_date) AND TRUNC(NVL(u.end_date , SYSDATE+1))

   AND furg.user_id = u.user_id

   AND furg.responsibility_id = frt.responsibility_id

   AND fr.responsibility_id   = frt.responsibility_id

   AND fa.application_id      = fat.application_id

   AND fr.application_id      = fat.application_id

   AND sysdate between furg.start_date and nvl(furg.end_date,sysdate)

   AND frt.language           = USERENV ('LANG')

   AND frt.responsibility_name in  ('Res p Name')

   AND pe.effective_start_date  BETWEEN TRUNC(pe.effective_start_date) AND NVL(TRUNC(pe.effective_end_date),SYSDATE)

   AND NVL(pe.effective_end_date,SYSDATE) >= TRUNC(SYSDATE)

   AND u.employee_id = pe.person_id 

   AND pas.person_id = pe.person_id

   AND TRUNC(SYSDATE) BETWEEN TRUNC(pas.effective_start_date) AND TRUNC(pas.effective_end_date) 

   AND pe.person_id = prd.person_id(+)

   AND NVL(prd.assignment_type,'-99') IN ('C',DECODE(DECODE(pe.current_employee_flag,'Y','Y',DECODE(pe.current_npw_flag,'Y','Y','N')),'Y','E', 'B'),'E', '-99')

   AND hou.organization_id = prd.org_id(+)

   AND hou.organization_id in (123)

   AND amount_limit is null

    order by hou.name,user_name

Comments

Post a Comment

Popular posts from this blog

Deleting the Sourcing Rule Assignments API

-
Deleting the Sourcing Rule Assignments API DECLARE        l_return_status VARCHAR2(200);        l_msg_count  NUMBER;        l_msg_data VARCHAR2(2000);        l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;        l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;        l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;        l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;        o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;        o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;        o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;        o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type; ...
Read more

BarCode with XML Publisher

-
Image
Barcode Steps Code 128 Barcode with XML Publisher Step:1 XML Tags and template Create or use existing oracle reports concurrent program. Change the output format under concurrent program definition to XML, run the concurrent program and get the XML tags. Map the RTF based on the XML tags generated. Step:2 Getting barcode fonts For barcode I have used Code 128 from IDAutomation (you can download from their site) After installing the font on local machine assign one of the IDAutomation font to the field in RTF template (for example an order number and font can be eg: IDAutomationSC128M) Step:3 Barcode font reading by handheld or scanner To make the barcode to be read by scanner, the encoder java code provided by vendor has to be compiled and convert it into a java class file. A.       Below is the class code save the code in a notepad and rename it as BarcodeUtil.java B.       Move the class file with EBSO fil...
Read more
-
 Active project Managers Query SELECT DISTINCT hou.name org_name,        ppa.name project_name,        ppa.segment1 project_number,        ppa.project_type,    pe.full_name Project_Manager,    amount_limit Purchase_Requisition_approval,    pe.email_address,        ppa.start_date,        to_char(ppa.completion_date,'DD-MON-YYYY')completion_date ,        ppa.closed_date,        ppa.enabled_flag   FROM apps.pa_project_parties ppp,    apps.pa_projects_all ppa,    apps.pa_project_role_types pprt,    apps.per_all_people_f pe,    apps.pa_project_assignments pa,    apps.fnd_user u,        apps.hr_operating_units hou,   (SELECT paf.person_id,   paf.assignment_type,       pcr.amount_limit   ...
Read more