creating a complex package body with type,bulk collect,execute immediate,functions example

create or replace
PACKAGE BODY xx_requisition_report
AS
-- Author      : Gopal
-- Date        :
-- Description : Get data for Requisition Status Report
-- Change History
-- Date         Author             Description
-----------------------------------------------------------------------------------------------------------------
-- 29-Jan-2009  Gopal         Created
-- 02-Feb-2009  Gopal         Report modifed to display only iProcruement requistion,
--                                  added condition inteface_source_code is null
-----------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------
-- Function  Name:  getapprover
-- Purpose       :  This function will get approver
-------------------------------------------------------------------
FUNCTION getapprover(p_seq_number in Number,
                     p_req_num in varchar2,
                     p_org_id     IN NUMBER
                    )return  varchar2
IS
--creating a record type which will hold many variables of diffrent types
  TYPE apprec IS RECORD( full_name per_people_x.full_name%TYPE,
                         person_id per_people_x.person_id%TYPE,
                      sequence_num po_action_history.sequence_num%TYPE,
                         approval_status por_approval_status_lines_v.approval_status%TYPE);
--to hold the above created record type table creating a type variable
  TYPE app_tbl IS TABLE OF apprec;
--creating a variable l_app_tbl to hold above app_tbl and initializing as zero with object symbol
  l_app_tbl app_tbl := app_tbl();
-- creating variable to hold a query and that query contains concatenation symbol because at dynamic
--run time sql it needs to give like that
  l_run_query   VARCHAR2(4000);
  v_full_name  VARCHAR2(100);
  v_seq_number NUMBER;
  v_app_cg_count   NUMBER:=0;
  v_app_person_id      NUMBER;
BEGIN
       IF l_app_tbl.COUNT > 0
       THEN
         l_app_tbl.DELETE;
        END IF;
         l_run_query := 'SELECT DISTINCT por_view_reqs_pkg.get_approver_name(PAH.EMPLOYEE_ID),pah.employee_id,
                   pah.sequence_num,nvl(pah.action_code, '||''''||'PENDING'||''''||')
FROM po_action_history pah,
                              po_lookup_codes plc_status,
                              po_requisition_headers_all prha,
                              per_assignments_x pax,
                              po_position_controls_all ppca,
                              po_control_groups_all pcga
                        WHERE plc_status.lookup_code = nvl(pah.action_code, '||''''||'PENDING'||''''||')
                        AND   prha.requisition_header_id = pah.object_id
AND plc_status.lookup_type in ( '||''''||'APPROVER ACTIONS'||''''||','||''''||'CONTROL ACTIONS'||''''||')
AND nvl(pah.action_code,'||''''||'PENDING'||''''||') in('||''''||'PENDING'||''''||','||''''||'APPROVE'||''''||','||''''||'null'||''''||','||''''||'NO ACTION'||''''||','||''''||'DELEGATE'||''''||')
AND pah.object_type_code = '||''''||'REQUISITION'||''''||'
AND pah.object_sub_type_code = '||''''||'PURCHASE'||''''||'
        AND pax.position_id = ppca.position_id
     AND ppca.control_group_id = pcga.control_group_id
     AND pax.person_id = pah.employee_id
AND prha.interface_source_code is null
                       -- AND pcga.control_group_name not in('||''''||'FA Approval Gp'||''''||','||''''||'FA Approval Gp - IE'||''''||','||''''||'FA Approval Gp -UK'||''''||') --Commented by Deepti on 13 June for #208771
                          AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = '||''''||'QL_CONTROL_GROUP_NAME'||''''||' )--Added by Deepti
AND prha.segment1 = '||''''||p_req_num||''''||'
        AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti

UNION ALL
SELECT DISTINCT por_view_reqs_pkg.get_approver_name(apll.approver_id),apll.approver_id,
                               apll.sequence_num,'||''''||'null'||''''||'
FROM po_approval_list_headers aplh,
                             po_approval_list_lines apll,
                             po_requisition_headers_all prha,
                             per_assignments_x pax,
                             po_position_controls_all ppca,
                             po_control_groups_all pcga
WHERE apll.approval_list_header_id = aplh.approval_list_header_id
AND   prha.requisition_header_id = aplh.document_id
AND   apll.sequence_num > aplh.current_sequence_num
                        AND   pax.position_id = ppca.position_id
                        AND   ppca.control_group_id = pcga.control_group_id
                       -- AND pcga.control_group_name not in('||''''||'FA Approval Gp'||''''||','||''''||'FA Approval Gp - IE'||''''||','||''''||'FA Approval Gp -UK'||''''||')  --Commented by Deepti on 13 June for #208771
                       AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = '||''''||'QL_CONTROL_GROUP_NAME'||''''||' )--Added by Deepti
                        AND pax.person_id = apll.approver_id
AND prha.interface_source_code is null
AND aplh.latest_revision = '||''''||'Y'||''''||'
AND aplh.document_type = '||''''||'REQUISITION'||''''||'
AND aplh.document_subtype = '||''''||'PURCHASE'||''''||'
AND prha.segment1 = '||''''||p_req_num||''''||'
AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
ORDER BY sequence_num';
          EXECUTE IMMEDIATE l_run_query
          BULK COLLECT INTO l_app_tbl;
-------------------------------------------------------
--Get the approvers
--------------------------------------------------------
          v_seq_number := p_seq_number;
          FOR idx IN l_app_tbl.FIRST..l_app_tbl.COUNT
          LOOP
            v_full_name         := NULL;
          FOR IDX in 1..v_seq_number
          LOOP
               IF idx= v_seq_number THEN
                   IF l_app_tbl(idx).approval_status in ('DELEGATE') THEN
                      v_full_name := l_app_tbl(idx).full_name || ' DELEGATED';
                   ELSE
                      v_full_name := l_app_tbl(idx).full_name;
                   END IF;
                 RETURN v_full_name;
               END IF;
         END LOOP;
     END LOOP;

  EXCEPTION
   WHEN NO_DATA_FOUND THEN
     v_full_name := null;
     RETURN v_full_name;
   WHEN OTHERS
   THEN
    RETURN null;
END getapprover;
---------------------------------------------------------------
--Get Approval Date Function
---------------------------------------------------------------
FUNCTION getapprover_date(p_seq_number in Number,
                                             p_req_num in varchar2,
                          p_org_id     IN NUMBER )return  VARCHAR2
IS
  TYPE apprec_dt IS RECORD( approval_date VARCHAR2(20),
                            sequence_num  po_action_history.sequence_num%TYPE);
  TYPE app_tbl_dt IS TABLE OF apprec_dt;
  l_app_tbl_dt     app_tbl_dt := app_tbl_dt();
  l_run_query_dt      VARCHAR2(4000);
  v_approval_date  VARCHAR2(20):=Null;
  v_seq_number NUMBER;
BEGIN
       IF l_app_tbl_dt.COUNT > 0
       THEN
         l_app_tbl_dt.DELETE;
        END IF;
         l_run_query_dt := 'SELECT DISTINCT to_char(pah.action_date,fnd_profile.value_wnps('||''''||'ICX_DATE_FORMAT_MASK'||''''||')),
                               pah.sequence_num
   FROM po_action_history pah,
                                 po_lookup_codes plc_status,
                                 po_requisition_headers_all prha,
                                 per_assignments_x pax,
                                 po_position_controls_all ppca,
                                 po_control_groups_all pcga
                            WHERE plc_status.lookup_code = nvl(pah.action_code, '||''''||'PENDING'||''''||')
                            AND   prha.requisition_header_id = pah.object_id
   AND   plc_status.lookup_type in ( '||''''||'APPROVER ACTIONS'||''''||','||''''||'CONTROL ACTIONS'||''''||')
   AND   nvl(pah.action_code,'||''''||'PENDING'||''''||') in('||''''||'PENDING'||''''||','||''''||'APPROVE'||''''||','||''''||'null'||''''||','||''''||'NO ACTION'||''''||','||''''||'DELEGATE'||''''||')
   AND   pah.object_type_code = '||''''||'REQUISITION'||''''||'
   AND   pah.object_sub_type_code = '||''''||'PURCHASE'||''''||'
                            AND   pax.position_id = ppca.position_id
   AND   prha.interface_source_code is null
                            AND   ppca.control_group_id = pcga.control_group_id
                            AND   pax.person_id = pah.employee_id
                           -- AND   pcga.control_group_name not in('||''''||'FA Approval Gp'||''''||','||''''||'FA Approval Gp - IE'||''''||','||''''||'FA Approval Gp -UK'||''''||') --Commented by Deepti on 13 June for #208771
                           AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = '||''''||'QL_CONTROL_GROUP_NAME'||''''||' ) --Added by Deepti
   AND   prha.segment1 = '||''''||p_req_num||''''||'
   AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
UNION ALL
 SELECT DISTINCT to_char(apll.response_date,fnd_profile.value_wnps('||''''||'ICX_DATE_FORMAT_MASK'||''''||')),
                                 apll.sequence_num
 FROM   po_approval_list_headers aplh,
                                 po_approval_list_lines apll,
                                 po_requisition_headers_all prha,
                                 per_assignments_x pax,
                                 po_position_controls_all ppca,
                                 po_control_groups_all pcga
 WHERE  apll.approval_list_header_id = aplh.approval_list_header_id
 AND    prha.requisition_header_id = aplh.document_id
 AND    apll.sequence_num > aplh.current_sequence_num
                          AND    pax.position_id = ppca.position_id
                          AND    ppca.control_group_id = pcga.control_group_id
                          --AND    pcga.control_group_name not in('||''''||'FA Approval Gp'||''''||','||''''||'FA Approval Gp - IE'||''''||','||''''||'FA Approval Gp -UK'||''''||')--Commented by Deepti
                          AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = '||''''||'QL_CONTROL_GROUP_NAME'||''''||' )--Added by Deepti
                          AND    pax.person_id = apll.approver_id
 AND    prha.interface_source_code is null
 AND    aplh.latest_revision = '||''''||'Y'||''''||'
 AND    aplh.document_type = '||''''||'REQUISITION'||''''||'
 AND    aplh.document_subtype = '||''''||'PURCHASE'||''''||'
 AND    prha.segment1 = '||''''||p_req_num||''''||'
 AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
ORDER BY  sequence_num';
          EXECUTE IMMEDIATE l_run_query_dt
          BULK COLLECT INTO l_app_tbl_dt;
-------------------------------------------------------
--Get the approval Date
--------------------------------------------------------
          v_seq_number := p_seq_number;
      FOR idx IN l_app_tbl_dt.FIRST..l_app_tbl_dt.COUNT
      LOOP
        v_approval_date := null;
        FOR IDX in 1..v_seq_number
        LOOP
            IF idx= v_seq_number THEN
              v_approval_date := NVL(l_app_tbl_dt(idx).approval_date,null);
              RETURN v_approval_date;
           END IF;
        END LOOP;
      END LOOP;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
     v_approval_date := null;
     RETURN v_approval_date;
   WHEN OTHERS
   THEN
    RETURN null;
END getapprover_date;

------------------------------------------------------------------
-- Function  Name:  getapprover_FA
-- Purpose       :  This function will get FA approver
-------------------------------------------------------------------
FUNCTION getapprover_FA(p_req_num in VARCHAR2,p_org_id     IN NUMBER)return  varchar2
IS
  TYPE fa_apprec IS RECORD( full_name per_people_x.full_name%TYPE,
                         person_id    per_people_x.person_id%TYPE,
                    sequence_num po_action_history.sequence_num%TYPE,
                         approval_status por_approval_status_lines_v.approval_status%TYPE,
object_id PO_ACTION_HISTORY.object_id%TYPE);
  TYPE fa_app_tbl IS TABLE OF fa_apprec;
  l_fa_app_tbl  fa_app_tbl := fa_app_tbl();
  l_fa_run_query   VARCHAR2(4000);
  v_person_id      NUMBER;
  v_cg_name        NUMBER;
  v_fa_full_name   VARCHAR2(100):=null;
  v_fa_seq_number  NUMBER;
  v_object_id PO_ACTION_HISTORY.object_id%TYPE;
BEGIN
       IF l_fa_app_tbl.COUNT > 0
       THEN
         l_fa_app_tbl.DELETE;
        END IF;
         l_fa_run_query := 'SELECT por_view_reqs_pkg.get_approver_name(PAH.EMPLOYEE_ID),pah.employee_id,
                      pah.sequence_num,nvl(pah.action_code, '||''''||'PENDING'||''''||'),pah.object_id
               FROM po_action_history pah,
                                 po_lookup_codes plc_status,
                                 po_requisition_headers_all prha
                            WHERE plc_status.lookup_code = nvl(pah.action_code, '||''''||'PENDING'||''''||')
                            AND   prha.requisition_header_id = pah.object_id
   AND   plc_status.lookup_type in ( '||''''||'APPROVER ACTIONS'||''''||','||''''||'CONTROL ACTIONS'||''''||')
   AND   nvl(pah.action_code,'||''''||'PENDING'||''''||') in('||''''||'PENDING'||''''||','||''''||'APPROVE'||''''||','||''''||'null'||''''||','||''''||'NO ACTION'||''''||','||''''||'DELEGATE'||''''||')
   AND   pah.object_type_code = '||''''||'REQUISITION'||''''||'
   AND   pah.object_sub_type_code = '||''''||'PURCHASE'||''''||'
   AND prha.interface_source_code is null
   AND   prha.segment1 = '||''''||p_req_num||''''||'
 AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
UNION ALL
 SELECT por_view_reqs_pkg.get_approver_name(apll.approver_id),apll.approver_id,
                                 apll.sequence_num,'||''''||'null'||''''||',prha.requisition_header_id object_id
 FROM po_approval_list_headers aplh, po_approval_list_lines apll ,po_requisition_headers_all prha
 WHERE apll.approval_list_header_id = aplh.approval_list_header_id
 AND   prha.requisition_header_id = aplh.document_id
 AND   apll.sequence_num > aplh.current_sequence_num
 AND   aplh.latest_revision = '||''''||'Y'||''''||'
 AND   aplh.document_type = '||''''||'REQUISITION'||''''||'
 AND   prha.interface_source_code is null
 AND   aplh.document_subtype = '||''''||'PURCHASE'||''''||'
 AND   prha.segment1 = '||''''||p_req_num||''''||'
AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
ORDER BY sequence_num';
          EXECUTE IMMEDIATE l_fa_run_query
          BULK COLLECT INTO l_fa_app_tbl;
-------------------------------------------------------
--Get the approvers
--------------------------------------------------------

          FOR idx IN l_fa_app_tbl.FIRST..l_fa_app_tbl.COUNT
          LOOP
            v_fa_full_name := NULL;
            v_cg_name      :=0;
            v_person_id    := NULL;
            v_person_id := l_fa_app_tbl(idx).person_id;
   v_object_id := l_fa_app_tbl(idx).object_id;
            BEGIN
              SELECT count(pcga.control_group_name)
              INTO  v_cg_name
              FROM   per_assignments_x pax
                    ,po_position_controls_all ppca
                    ,po_control_groups_all pcga
              WHERE pax.position_id = ppca.position_id
              AND   ppca.control_group_id = pcga.control_group_id
             -- AND   pcga.control_group_name in('FA Approval Gp','FA Approval Gp - IE','FA Approval Gp -UK')
             AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'QL_CONTROL_GROUP_NAME')--Added by Deepti
           
              AND   person_id = v_person_id;
     END;
     ---start adding by srinivasulu z for WR#122959
     IF v_cg_name =0 THEN
 SELECT COUNT(employee_name)
 INTO v_cg_name
 FROM PO_ACTION_HISTORY_V
         WHERE object_id    = v_object_id
AND employee_id =v_person_id
         AND object_type_code = 'REQUISITION'
         AND ( 'PURCHASING'   = 'PUBLIC'
         OR ( 'PURCHASING'    = 'PURCHASING'))
AND action_code_dsp IN('Approve');
END IF;
--- end for WR#122959
          --
             IF v_cg_name > 0 THEN
          IF l_fa_app_tbl(idx).approval_status in ('DELEGATE') THEN
                        v_fa_full_name := l_fa_app_tbl(idx).full_name || ' DELEGATED';
                    ELSE
                        v_fa_full_name := l_fa_app_tbl(idx).full_name;
                    END IF;
                    RETURN v_fa_full_name;
                    EXIT;
             END IF;
          END LOOP;
          RETURN v_fa_full_name;
    EXCEPTION
   WHEN OTHERS THEN
    RETURN v_fa_full_name;
END getapprover_FA;

---------------------------------------------------------------
--Get FA Approval Date Function
---------------------------------------------------------------
FUNCTION getapprover_FA_date(p_req_num in varchar2 ,p_org_id     IN NUMBER
                            )return  VARCHAR2
IS
  TYPE apprec_fa_dt IS RECORD( approval_date VARCHAR2(20),
                               person_id     per_people_x.person_id%TYPE,
                               sequence_num  po_action_history.sequence_num%TYPE,
      object_id PO_ACTION_HISTORY.object_id%TYPE);
  TYPE app_tbl_fa_dt IS TABLE OF apprec_fa_dt;
  l_app_tbl_fa_dt     app_tbl_fa_dt := app_tbl_fa_dt();
  l_run_query_fa_dt      VARCHAR2(4000);
  v_approval_fa_date  VARCHAR2(20):=Null;
  v_person_id      NUMBER;
  v_cg_name        NUMBER;
  v_object_id PO_ACTION_HISTORY.object_id%TYPE;
BEGIN
       IF l_app_tbl_fa_dt.COUNT > 0
       THEN
         l_app_tbl_fa_dt.DELETE;
        END IF;
         l_run_query_fa_dt := 'SELECT to_char(pah.action_date,fnd_profile.value_wnps('||''''||'ICX_DATE_FORMAT_MASK'||''''||')),
                                 pah.employee_id,pah.sequence_num,pah.object_id
                      FROM po_action_history pah,
                                    po_lookup_codes plc_status,
                                    po_requisition_headers_all prha
                              WHERE plc_status.lookup_code = nvl(pah.action_code, '||''''||'PENDING'||''''||')
                              AND   prha.requisition_header_id = pah.object_id
     AND   plc_status.lookup_type in ( '||''''||'APPROVER ACTIONS'||''''||','||''''||'CONTROL ACTIONS'||''''||')
     AND   nvl(pah.action_code,'||''''||'PENDING'||''''||') in('||''''||'PENDING'||''''||','||''''||'APPROVE'||''''||','||''''||'null'||''''||','||''''||'NO ACTION'||''''||','||''''||'DELEGATE'||''''||')
     AND   pah.object_type_code = '||''''||'REQUISITION'||''''||'
     AND   pah.object_sub_type_code = '||''''||'PURCHASE'||''''||'
AND   prha.interface_source_code is null
                              AND   prha.segment1 = '||''''||p_req_num||''''||'
                              AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
   UNION ALL
     SELECT to_char(apll.response_date,fnd_profile.value_wnps('||''''||'ICX_DATE_FORMAT_MASK'||''''||')),
                                     apll.approver_id,apll.sequence_num,prha.requisition_header_id object_id
                              FROM   po_approval_list_headers aplh, po_approval_list_lines apll ,po_requisition_headers_all prha
     WHERE  apll.approval_list_header_id = aplh.approval_list_header_id
     AND    prha.requisition_header_id = aplh.document_id
                              AND    apll.sequence_num > aplh.current_sequence_num
     AND    aplh.latest_revision = '||''''||'Y'||''''||'
     AND    aplh.document_type = '||''''||'REQUISITION'||''''||'
                              AND    aplh.document_subtype = '||''''||'PURCHASE'||''''||'
     AND    prha.interface_source_code is null
     AND    prha.segment1 = '||''''||p_req_num||''''||'
     AND    prha.org_id   = '||''''||p_org_id||''''||' --Added by Deepti
                            ORDER BY sequence_num';
     EXECUTE IMMEDIATE l_run_query_fa_dt
     BULK COLLECT INTO l_app_tbl_fa_dt;
-------------------------------------------------------
--Get the approval Date
--------------------------------------------------------
      FOR idx IN l_app_tbl_fa_dt.FIRST..l_app_tbl_fa_dt.COUNT
      LOOP
        v_approval_fa_date := null;
        v_person_id    := NULL;
        v_person_id := l_app_tbl_fa_dt(idx).person_id;
v_object_id := l_app_tbl_fa_dt(idx).object_id;
         BEGIN
              SELECT count(pcga.control_group_name)
              INTO  v_cg_name
              FROM   per_assignments_x pax
                    ,po_position_controls_all ppca
                    ,po_control_groups_all pcga
              WHERE pax.position_id = ppca.position_id
              AND   ppca.control_group_id = pcga.control_group_id
              --AND   pcga.control_group_name in('FA Approval Gp','FA Approval Gp - IE','FA Approval Gp -UK')
              AND pcga.control_group_name not in(SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'QL_CONTROL_GROUP_NAME')
              AND   person_id = v_person_id;
          END;
 ---start adding by srinivasulu z for WR#122959
 IF v_cg_name =0 THEN
 SELECT COUNT(employee_name)
 INTO v_cg_name
 FROM PO_ACTION_HISTORY_V
         WHERE object_id    = v_object_id
AND employee_id =v_person_id
         AND object_type_code = 'REQUISITION'
         AND ( 'PURCHASING'   = 'PUBLIC'
         OR ( 'PURCHASING'    = 'PURCHASING'))
AND action_code_dsp IN('Approve');
END IF;
--end for WR#121111
          IF v_cg_name > 0 THEN
               v_approval_fa_date := NVL(l_app_tbl_fa_dt(idx).approval_date,null);
              RETURN v_approval_fa_date;
              EXIT;
           END IF;
      END LOOP;
      RETURN v_approval_fa_date;
  EXCEPTION
   WHEN OTHERS  THEN
    RETURN v_approval_fa_date;
END getapprover_fa_date;

END xx_requisition_report;