How to find the receipt details based on PO number ?

This query is used to find the receipt details based on PO number.


SELECT  prh.segment1 "Req Number"
       ,prh.requisition_header_id
       ,prh.org_id "Req orgid"
       ,prh.creation_date
       ,prl.requisition_line_id
       ,prl.line_num
       ,prl.item_description "req line description"
       ,prd.distribution_id
       ,prd.set_of_books_id
       ,pda.po_distribution_id
       ,pla.po_line_id
       ,pla.line_num "Po Linenum"
       ,pha.segment1 "Po Number"
       ,pha.po_header_id
       ,as1.segment1 "Vendor Number"
       ,as1.vendor_name
       ,rt.TRANSACTION_TYPE
       ,rsl.shipment_line_id
       ,rsl.line_num "Shipment Linenum"
       ,rsl.shipment_line_status_code
       ,rsl.item_id
       ,rsl.source_document_code
       ,rsl.from_organization_id
       ,rsl.to_organization_id
       ,rsl.to_subinventory
       ,rsl.quantity_shipped
       ,rsl.quantity_received
       ,rsl.deliver_to_person_id
       ,rsl.item_description "Shipment lines Description"
       ,rsl.unit_of_measure
       ,rsh.receipt_num
       ,rsh.shipment_num
 
FROM    po_requisition_headers_All prh
       ,po_requisition_lines_All prl
       ,po_req_distributions_All prd
       ,po_distributions_all pda
       ,po_lines_all pla
       ,po_headers_all pha
       ,ap_suppliers as1
       ,rcv_transactions rt
       ,rcv_shipment_lines rsl
       ,rcv_shipment_headers rsh
 
WHERE 1=1
-- AND   prh.segment1              =  '143'
AND   prh.org_id                = 791
AND   prh.requisition_header_id = prl.requisition_header_id
AND   prl.requisition_line_id   = prd.requisition_line_id
AND   prd.distribution_id       = pda.req_distribution_id(+)
AND   pla.po_line_id            = pda.po_line_id       
AND   pha.po_header_id          = pla.po_header_id       
AND   pha.org_id                = 791                   
AND   pha.type_lookup_code      NOT IN ('RFQ','QUOTATION')
AND   pha.segment1 = '102'
AND   pha.vendor_id             = as1.vendor_id           
AND   pha.po_header_id          = rt.po_header_id         
AND   pla.po_line_id            = rt.po_line_id           
AND   pda.po_distribution_id    = rt.po_distribution_id   
AND   rt.organization_id        = 791                     
-- AND   prl.requisition_line_id    = rt.requisition_line_id(+)
AND  rt.shipment_line_id        = rsl.shipment_line_id   
AND  pha.po_header_id           = rsl.po_header_id         
AND  pla.po_line_id             = rsl.po_line_id           
AND  pda.po_distribution_id     = rsl.po_distribution_id     
AND  prd.distribution_id        = rsl.req_distribution_id   
-- AND  prl.requisition_line_id     = rsl.requisition_line_id(+)
AND  rsh.shipment_header_id     = rsl.shipment_header_id;  

How to create revenue events by using PA_EVENT_PUB API in R12?

create or replace PACKAGE BODY XXX_6666_reversal_pkg
IS

--==============================================================================
--                          GLOBAL CONSTANTS
--==============================================================================

  CN_PROJECT_CLASS            CONSTANT      VARCHAR2(60)    := 'Revenue Recognition Method';
  CN_CLASS_CODE               CONSTANT      VARCHAR2(30)    := 'ASC606'; 
  CN_REVERSAL_EVENT_TYPE      CONSTANT      VARCHAR2(30)    := 'REVENUE 6666 CONTRA';
  CN_EVENT_QUALIFIER          CONSTANT      VARCHAR2(30)    :=  'T6666';
  CN_6666_CONT_EVENT_QUALIFIER CONSTANT      VARCHAR2(30)   :=  'T6666 Contra';
  CN_606_EVENT_QUALIFIER      CONSTANT      VARCHAR2(30)    :=  'T606';
 

PROCEDURE Debug( p_message  IN  VARCHAR2
               ) IS
lv_message       VARCHAR2(200);
 
BEGIN
 
      lv_message    := SUBSTR(p_message,1,240);
      fnd_file.put_line(fnd_file.log, lv_message);
 
END Debug;


 PROCEDURE XXX_reversal_proc (
                                     p_project_id     NUMBER ,
                                     p_project_num    VARCHAR2,
                                     p_program_mode   VARCHAR2,
                                     p_as_of_date     DATE,
                                     p_event_type     VARCHAR2,
                                     p_task           VARCHAR2
                                     ) IS

-- +====================================================================+
--     LOCAL VARIABLES
-- +====================================================================+
 
       ln_event_id                NUMBER;
       ln_line_num                NUMBER := 0;
       ln_msg_count               NUMBER := 0;
       ln_seq_val                 NUMBER := 0;
       ld_complete_date           DATE;
       lv_flexfield_name          VARCHAR2(100) := 'PA_EVENTS_DESC_FLEX';
       lv_msg_data                VARCHAR2(100);
       lv_bill_hold_flag          VARCHAR2(3)   := 'N';
       lv_product_code            VARCHAR2(100) := 'XXX';
       lv_flag            VARCHAR2(3):='S';
       lv_6666_rev_task    VARCHAR2(40);
   --    lc_proj_rate_type     VARCHAR2(30);  -- Commented on 20-DEC-17
   --    ln_proj_ex_rate       NUMBER;        -- Commented on 20-DEC-17
--    lc_projfunc_rate_type VARCHAR2(30);  -- Commented on 20-DEC-17
   --    ln_projfunc_ex_rate   NUMBER;        -- Commented on 20-DEC-17

     
     
-- +====================================================================+
--   Declaring PLSQL TABLE TYPE VARIABLES.
-- +====================================================================+     

       lt_in_tbl_type     pa_event_pub.event_in_tbl_type;
       lt_out_tbl_type    pa_event_pub.event_out_tbl_type;
     
-- +====================================================================+
--   lcu_6666_reversal Cursor is used to fetch the events data.
-- +====================================================================+

CURSOR lcu_6666_reversal
IS 
SELECT pp.segment1,
       pe.project_id,
       pe.organization_id,
       round(-1 * sum(pe.bill_trans_rev_amount),2) trx_rev_amt,
       pe.bill_trans_currency_code        trx_rev_curr, 
       pe.PROJECT_CURRENCY_CODE  proj_curr,
       round(-1 * sum(pe.PROJECT_REVENUE_AMOUNT)) proj_rev_amt,
       pe.PROJFUNC_CURRENCY_CODE projfunc_curr,
       round(-1 * sum(pe.PROJFUNC_REVENUE_AMOUNT)) projfunc_rev_amt
FROM 
       pa_projects pp,
       pa_project_classes_v ppc,
       pa_tasks pt,
       pa_events pe,
       pa_event_types pet
WHERE ppc.class_category          =  CN_PROJECT_CLASS
AND   ppc.class_code              =  CN_CLASS_CODE
AND   ppc.project_id              =  pp.project_id
AND   pp.project_id               =  p_project_id
AND   pp.project_id               =  pt.project_id
AND   pe.project_id               =  pp.project_id
AND   pt.task_id                  =  pe.task_id
AND   pe.revenue_distributed_flag = 'Y'
AND   pe.event_type               = pet.event_type
AND   pet.attribute3              = CN_EVENT_QUALIFIER
AND   nvl(pe.attribute7,'@#$')   != CN_EVENT_QUALIFIER
AND   pe.completion_date         <=  p_as_of_date
GROUP BY pp.segment1,
         pe.project_id,
         pe.organization_id,
         pe.bill_trans_currency_code
         ,pe.PROJECT_CURRENCY_CODE
         ,pe.projfunc_currency_code
HAVING SUM(pe.bill_trans_rev_amount) != 0
order by 1;

-- +====================================================================+
--   lcu_6666_task Cursor used to fetch the 6666 reversal Task.
-- +====================================================================+
CURSOR lcu_6666_task(p_project_id NUMBER)
IS
SELECT task_number
FROM   pa_tasks pt
WHERE  pt.project_id = p_project_id
AND    pt.attribute6 =  CN_6666_CONT_EVENT_QUALIFIER;


BEGIN
 
  debug('XXX_reversal_proc => begining of procedure');
 
FOR rev_rec IN lcu_6666_reversal
LOOP

     debug('XXX_reversal_proc => Inside rev_rec loop');
   
     SELECT XXX_REV_SEQ.NEXTVAL INTO ln_seq_val FROM DUAL; 
       
        debug('XXX_reversal_proc => ln_seq_val Sequence value '||ln_seq_val);
       
   lv_flag := 'S';
 

   debug('XXX_reversal_proc => LV_FLAG '||LV_FLAG);
   debug('XXX_reversal_proc => projfunc_curr '||rev_rec.projfunc_curr);
   debug('XXX_reversal_proc => proj_curr '||rev_rec.proj_curr);
 
 IF  LV_FLAG <> 'E' then

   
   
      debug('XXX_6666_main => prepare data for Creating event');   

        ln_line_num       := 1;

            lt_in_tbl_type(ln_line_num).p_project_number            := rev_rec.segment1;
            lt_in_tbl_type(ln_line_num).p_event_type                := p_event_type;
            lt_in_tbl_type(ln_line_num).p_description               := 'Revenue reversal for project: '||rev_rec.segment1;
            lt_in_tbl_type(ln_line_num).p_completion_date           := p_as_of_date;
            lt_in_tbl_type(ln_line_num).p_task_number               := lv_6666_rev_task;
            lt_in_tbl_type(ln_line_num).p_organization_name         := lv_org_name;
            lt_in_tbl_type(ln_line_num).p_bill_trans_bill_amount    := 0;
            lt_in_tbl_type(ln_line_num).p_bill_trans_rev_amount     := rev_rec.trx_rev_amt;
            lt_in_tbl_type(ln_line_num).p_desc_flex_name            := lv_flexfield_name;
            lt_in_tbl_type(ln_line_num).p_attribute2                := rev_rec.Organization_Id;
            lt_in_tbl_type(ln_line_num).p_attribute7                := CN_EVENT_QUALIFIER;
            lt_in_tbl_type(ln_line_num).p_bill_hold_flag            := lv_bill_hold_flag;
            lt_in_tbl_type(ln_line_num).p_bill_trans_currency_code  := rev_rec.trx_rev_curr;
            lt_in_tbl_type(ln_line_num).p_pm_event_reference        := '6666 REVERSAL' || ln_seq_val;
          --  lt_in_tbl_type(ln_line_num).P_project_rate_type         := lc_proj_rate_type;     -- Commented on 20-DEC-17
          --  lt_in_tbl_type(ln_line_num).P_project_exchange_rate     := ln_proj_ex_rate;       -- Commented on 20-DEC-17
          --  lt_in_tbl_type(ln_line_num).P_projfunc_rate_type       := lc_projfunc_rate_type; -- Commented on 20-DEC-17
          --  lt_in_tbl_type(ln_line_num).P_projfunc_exchange_rate    := ln_projfunc_ex_rate;   -- Commented on 20-DEC-17
           
-- +====================================================================+
--   Calling PA_EVENT_PUB api,
--   to create a reversal event for 6666 revenue events.
-- +====================================================================+     
 debug('XXX_6666_main => calling API');
    pa_event_pub.create_event(
                p_api_version_number   => 1.0,
                p_commit               => fnd_api.g_true,
                p_init_msg_list        => fnd_api.g_true,
                p_pm_product_code      => lv_product_code,
                p_event_in_tbl         => lt_in_tbl_type,
                p_event_out_tbl        => lt_out_tbl_type,
                p_msg_count            => ln_msg_count,
                p_msg_data             => lv_msg_data,
                p_return_status        => lv_return_status
                               );
                     
     COMMIT;
   
         
   
     IF lv_return_status='S' THEN
   
          SELECT pe.event_id INTO ln_event_id
          FROM   pa_events pe
          WHERE  pe.PM_EVENT_REFERENCE = '6666 REVERSAL' || ln_seq_val
            AND  pe.organization_id = ln_org_id;
   
                                     
              ln_event_id := 0;
   
       
           
     UPDATE PA_EVENTS pe SET pe.attribute7 = CN_EVENT_QUALIFIER
     WHERE  pe.project_id = rev_rec.project_id
         AND  pe.revenue_distributed_flag = 'Y'
      --   AND pe.event_type = rev_rec.event_type
      --   AND pe.task_id    = rev_rec.task_id
         AND nvl(pe.attribute7,'@#$') != CN_EVENT_QUALIFIER  
         AND pe.completion_date  <=  p_as_of_date; 

         COMMIT; 

     ELSE
          debug('XXX_reversal_proc => Return Status: '||lv_return_status);

     END IF;
   
 END IF;
 
END LOOP;
 
    debug('XXX_reversal_proc => end of procedure');
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    debug('XXX_reversal_proc => No data found to reverse for the project number:' ||p_project_num);
  WHEN OTHERS THEN
    debug('XXX_reversal_proc => Error is '||SQLCODE||','||SQLERRM||' for project number:' ||p_project_num);
 
END XXX_reversal_proc;



PROCEDURE XXX_6666_main(
        errbuf               OUT VARCHAR2,
        retcode              OUT VARCHAR2,
        p_project_from       IN  VARCHAR2,
        p_project_to         IN  VARCHAR2,
        p_period             IN  VARCHAR2,
        p_program_mode       IN  VARCHAR2
                          )
IS

-- +====================================================================+
--     Local VARIABLES
-- +====================================================================+

 lv_resp_name       VARCHAR2(100);
 lv_user_name       VARCHAR2(100);
 ln_request_id      NUMBER;
 ld_as_of_date      DATE;
 lv_6666_event_type  VARCHAR2(40);
 lv_flag            CHAR(1);
 lv_6666_rev_task    VARCHAR2(40);


CURSOR lcu_6666_main
IS
SELECT pp.segment1 project_number
      ,pp.project_id
FROM   pa_projects pp,
       pa_project_classes_v ppc,
       pa_events pe,
       pa_event_types pet
WHERE ppc.class_category        = CN_PROJECT_CLASS
AND   ppc.class_code            = CN_CLASS_CODE
AND   ppc.project_id            = pp.project_id
AND   pp.project_id             = pe.project_id
and   pe.event_type             = pet.event_type
and   pet.attribute3            = CN_EVENT_QUALIFIER
AND   pp.segment1 BETWEEN NVL(p_project_from,pp.segment1) AND NVL(p_project_to,pp.segment1)
AND   nvl(pe.attribute7,'@#$') != CN_EVENT_QUALIFIER
GROUP BY  pp.segment1
         ,pp.project_id;

-- +====================================================================+
--   lcu_org_name Cursor used to fetch the organisation name.
-- +====================================================================+
CURSOR lcu_org_name(p_org_id number)
IS
SELECT hou.NAME
FROM   hr_operating_units hou
WHERE  hou.organization_id = p_org_id;


BEGIN

  debug('XXX_6666_main => begining of procedure');

-- +====================================================================+
--   Initalizing the variables.
-- +====================================================================+
  ln_org_id        := fnd_global.org_id;
  ln_request_id    := fnd_global.conc_request_id;
  lv_resp_name     := fnd_profile.value('RESP_NAME');
  lv_user_name     := fnd_profile.value('USERNAME');
 
-- +====================================================================+
--   Initalizing session.
-- +====================================================================+
  mo_global.set_policy_context('S',ln_org_id);
 
 
 
    SELECT TO_DATE(fnd_date.canonical_to_date(p_period),'YY-MON-DD')             
           INTO   ld_as_of_date
    FROM   dual;
   
    OPEN  lcu_org_name(ln_org_id);
        FETCH lcu_org_name INTO lv_org_name;
               IF lcu_org_name%NOTFOUND THEN
                  lv_flag := 'E';
                  debug('XXX_6666_main => Organization name not found');
               END IF;
    CLOSE lcu_org_name;
 

FOR lr_rec_6666_main IN lcu_6666_main
LOOP
 
 -- +====================================================================+
-- | Calling procedure XXX_reversal_proc to create the reversal 
-- | event.                                                           
-- +====================================================================+ 
     XXX_reversal_proc(lr_rec_6666_main.project_id
                             ,lr_rec_6666_main.project_number
                             ,p_program_mode
                             ,ld_as_of_date
                             ,CN_REVERSAL_EVENT_TYPE
                             ,lv_6666_rev_task);
   

END LOOP;

debug('XXX_6666_main => Complete generating events');
-- +====================================================================+
--   Printing output in concurrent program output.
-- +====================================================================+
 IF p_program_mode = 'FINAL' THEN
      report_success;
      report_errors;
 ELSE
      draft_output;
      report_errors;
 END IF;
  debug('XXX_6666_main => end of procedure');

EXCEPTION
WHEN NO_DATA_FOUND THEN
      debug('XXX_6666_main => No data found to reverse for the given projects from '||p_project_from || ' to ' || p_project_to);

WHEN OTHERS THEN
   debug('XXX_6666_main => Error is: '||SQLCODE||','||SQLERRM);
END XXX_6666_main;
END XXX_6666_reversal_pkg;
/
SHO ERRORS
/

How to find the secuity profile id by using the Organization Id in R12 ?

select           psp.SECURITY_PROFILE_ID
                   ,psp.BUSINESS_GROUP_ID
                   ,psp.ORGANIZATION_ID
                   ,psp.SECURITY_PROFILE_NAME
from per_Security_profiles psp
        ,per_security_organizations pso
        ,hr_operating_units hou
where 1=1
and psp.SECURITY_PROFILE_ID = pso.SECURITY_PROFILE_ID
and pso.organization_id = hou.organization_id
and hou.organization_id = 202
and psp.BUSINESS_GROUP_ID = 81;

How to load(Interface) Contract note text into Oracle Apps R12 ?

This Package is used to load the OKL contracts note text into Oracle Apps Base tables.

After loading the data, you can check it in JTF_NOTES_B,JTF_NOTES_TL tables.

--Package Spec
create or replace PACKAGE XX_NOTESCON_PKG AUTHID CURRENT_USER
AS
 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
   );
lv_error_location varchar2(2000);
END XX_NOTESCON_PKG;
/
create or replace PUBLIC synonym XXEC_OKL_AGCN_NOTESCON_PKG for XXEC.XXEC_OKL_AGCN_NOTESCON_PKG
/
GRANT ALL ON XXEC.XXEC_OKL_AGCN_NOTESCON_PKG TO APPS
/
SHOW ERRORS
/


--Package Body.

create or replace PACKAGE BODY XXEC_NOTESCON_PKG
AS

 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
 )
 IS
/*-------------------------------------------------------
  Defining the Cursor 
 --------------------------------------------------------*/
 --Cursor to get the data from stage table.

      CURSOR notes_data
      IS
         select XNT.CUSTOMER_NAME
               ,XNT.CONTRACT_NUMBER
               ,HAB.ID
               ,HAB.BILL_TO_SITE_USE_ID
               ,to_date(XNT.creation_date,'DD-MM-YY') creation_date
               ,XNT.NOTE_TEXT
               ,XNT.STATUS_FLAG
        from XXEC_OKL_NOTESCON_TMP XNT
            ,OKC_K_HEADERS_ALL_B HAB
        WHERE 1=1
          AND XNT.CONTRACT_NUMBER=HAB.CONTRACT_NUMBER
          ORDER BY XNT.CONTRACT_NUMBER;


 -- Decalring Local Variables.
      l_api_version          NUMBER;
      l_init_msg_list        VARCHAR2 (1);
      l_validation_level     NUMBER;
      l_commit               VARCHAR2 (1);
      l_return_status        VARCHAR2 (1);
      l_msg_count            NUMBER;
      l_msg_data             VARCHAR2 (2000);
      l_jtf_note_id          NUMBER                                   := NULL;
      l_source_object_code   VARCHAR2 (50);
      l_note_status          VARCHAR2 (8);
      l_note_contexts        jtf_notes_pub.jtf_note_contexts_tbl_type;
      l_msg_index            NUMBER;
      l_msg_index_out        NUMBER;
      l_count                NUMBER                                   := 0;
      l_acount               NUMBER                                   := 0;
      l_success  number:=0;
      l_error  number:=0;
      l_error_e number:=0;
      l_notes_err_count      NUMBER                                   := 0;
      l_note_type            VARCHAR2(80) ;
      l_flag                 VARCHAR2(1)  := 'N';
BEGIN
   XXEC_NOTESCON_PKG.lv_error_location:='Starts Execution of Code'; --Intializing value for error location variable.
   fnd_file.put_line (fnd_file.LOG, 'Begin ACCESS NOTES Conversion ');
   fnd_file.put_line (fnd_file.LOG, fnd_profile.VALUE ('ORG_ID'));

   --Intializing the values for Local Variables.
   l_api_version := 1.0;
   l_init_msg_list := fnd_api.g_true;
   l_validation_level := fnd_api.g_valid_level_full;
   l_commit := fnd_api.g_true;
   l_msg_count := 0;
   l_note_status := 'I';
   l_source_object_code := 'OKC_K_HEADER';
   l_note_type := 'AS_USER';

   XXEC_NOTESCON_PKG.lv_error_location:='Before loop1'; --Intializing value for error location variable.
   FOR v_cur_notes_data IN notes_data
   LOOP  --For Loop1
      XXEC_NOTESCON_PKG.lv_error_location:='Inside loop1';  --Intializing value for error location variable.
      fnd_file.put_line (fnd_file.LOG, 'Customer : ' || v_cur_notes_data.customer_name);
      fnd_file.put_line (fnd_file.LOG, 'Cteation Date : ' || v_cur_notes_data.creation_date);
      fnd_file.put_line (fnd_file.LOG, 'Note : ' || v_cur_notes_data.note_text);
      l_flag := 'N';
      l_acount := l_acount + 1;

           l_flag := 'Y';
         /*-------------------------------------------------
           Populating the l_note_contexts of pl/sql table type
           -------------------------------------------------*/
         l_count := l_count + 1;
         fnd_file.put_line (fnd_file.LOG, 'Current Cursor count ' || l_count);
         l_note_contexts (1).note_context_type := 'OKC_K_HEADER';
         l_note_contexts (1).note_context_type_id := v_cur_notes_data.ID;
         l_note_contexts (1).last_update_date := SYSDATE;
         l_note_contexts (1).last_updated_by := fnd_global.user_id;
         l_note_contexts (1).creation_date := SYSDATE;
         l_note_contexts (1).created_by := fnd_global.user_id;
         l_note_contexts (1).last_update_login := fnd_global.login_id;
         /*-------------------------------------------------
                     Calling the API
           ------------------------------------------------*/
         -- fnd_file.put_line (fnd_file.LOG, 'Calling API ');

         XXEC_NOTESCON_PKG.lv_error_location:='Before calling API JTF_NOTES_PUB';  --Intializing value for error location variable.

        jtf_notes_pub.create_note  --Calling API.
                 (p_api_version                => l_api_version,
                  p_init_msg_list              => l_init_msg_list,
                  p_commit                     => l_commit,
                  p_validation_level           => l_validation_level,
                  x_return_status              => l_return_status,
                  x_msg_count                  => l_msg_count,
                  x_msg_data                   => l_msg_data,
                  p_jtf_note_id                => l_jtf_note_id,
                  --p_entered_by                 => 1396,
                  p_entered_date               => v_cur_notes_data.creation_date,
                 -- p_source_object_id           => v_cur_notes_data.bill_to_site_use_id,
                  p_source_object_id           => v_cur_notes_data.ID,
                  p_source_object_code         => l_source_object_code, --OKC_K_HEADER
                  p_notes                      => v_cur_notes_data.note_text,
                  p_notes_detail               => NULL,
                  p_note_type                  => l_note_type,
                  p_note_status                => l_note_status,
                  p_jtf_note_contexts_tab      => l_note_contexts,
                  x_jtf_note_id                => l_jtf_note_id,
                  p_last_update_date           => SYSDATE,
                  p_last_updated_by            => fnd_global.user_id,
                  p_creation_date              => v_cur_notes_data.creation_date,
                  --p_created_by                 => 1396,
                  p_last_update_login          => fnd_global.login_id
                 );
                fnd_file.put_line(fnd_file.LOG,'ACCESS NOTES transferred into Collections and Note ID '|| l_jtf_note_id);
                 XXEC_NOTESCON_PKG.lv_error_location:='After Calling the API JTF_NOTES_PUB';

                 XXEC_NOTESCON_PKG.lv_error_location:='Updating Status Flag in XXEC_OKL_NOTESCON_TMP table';
                 if l_return_status='S' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting the Success records';
                   l_success := l_success+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                    fnd_file.put_line(fnd_file.output,'Successed Contract'||v_cur_notes_data.CONTRACT_NUMBER);
                 elsif l_return_status='E' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                    l_error := l_error+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 else
                  XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                      l_error_e := l_error_e+1;
                       update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'l_error_e Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 end if;
                 COMMIT;

         /*----------------------------------------------
           Checking for Errors
           ---------------------------------------------*/
         IF (fnd_msg_pub.count_msg > 0)
         THEN
            FOR i IN 1 .. fnd_msg_pub.count_msg
            LOOP --Loop3

            XXEC_NOTESCON_PKG.lv_error_location:='Inside Loop3';  --Intializing value for error location variable.
               fnd_msg_pub.get (p_msg_index          => i,
                                p_encoded            => 'F',
                                p_data               => l_msg_data,
                                p_msg_index_out      => l_msg_index_out
                               );
               fnd_file.put_line (fnd_file.LOG, 'API ERROR: ' || l_msg_data);
            END LOOP; --end of loop3
            XXEC_NOTESCON_PKG.lv_error_location:='End of Loop3';  --Intializing value for error location variable.
            l_notes_err_count := l_notes_err_count + 1;
         END IF;        
   END LOOP; --end of loop1
   XXEC_NOTESCON_PKG.lv_error_location:='end of Loop1';  --Intializing value for error location variable.
   COMMIT;  --commiting the processed records.

   fnd_file.put_line (fnd_file.LOG, 'Total count of ALL Customer Notes - ' || l_acount); --Printing Total Count of Customer Notes.
   fnd_file.put_line (fnd_file.LOG, 'Total Success Records Count - '|| l_success);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count - '|| l_error);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count e- '|| l_error_e);
   fnd_file.put_line (fnd_file.LOG, 'Total Error count  - ' || l_notes_err_count); --Printing Total error count.
   fnd_file.put_line (fnd_file.LOG, 'END of ACCESS NOTES Conversion ');
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR: ' || SQLERRM);
         fnd_file.put_line (fnd_file.log,'The error is at '||XXEC_NOTESCON_PKG.lv_error_location);
         p_errbuf_out := SQLERRM;
         p_retcode_out := 2;
 END XXEC_OKL_MAIN_PROC;
END XXEC_NOTESCON_PKG;
/
SHOW ERRORS
/

How to grant tables,views,synonyms and packages to other schema in Oracle Apps R12.2.X ?

TABLES,VIEW,SYNONYM FOR OKL:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
      ,OWNER
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OWNER||'.'||GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


TABLES,VIEW,SYNONYM FOR OKC:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

GRANTING OKL PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


GRANTING OKC PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

How to complie all objects of a schema in Oracle ?

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures,
functions, packages, and triggers in the specified schema.
The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

How to compile an invalid views and triggers by using dynamic sql in oracle ?

--TO COMPILE THE INVALID VIEWS IN APPS&XXEC SCHEMA'S.

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'VIEW'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'VIEW' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

--TO COMPILE THE INVALID TRIIGERS IN APPS&XXFIN SCHEMAS

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'TRIGGER'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TRIGGER' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/