GL Account Analysis Query for Multiple sources

SELECT  xga.period_name,
                    xga.ledger_name,
                    xga.ledger_id,
                    xga.account,
                    xga.project,
                    xga.resource_org,
                    xga.work_location,
                    xga.client_vertical,
                    xga.product,
                    xga.entity,
                    xga.inter_entity,
                    xga.spare1,
                    xga.spare2,
                    xga.combinations,
                    xga.je_source,
                    xga.je_category,
                    pv.vendor_name party_name,
                    pv.segment1 party_number,
                    NULL transaction_type,
                    xal.currency_code transaction_currency,
                    xte.transaction_number,
                    TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') transaction_date,
                    xga.gl_date,
                    gir.subledger_doc_sequence_value subledger_doc_no,
                    xga.doc_sequence_value,
                    xal.description transaction_description,
                    xal.currency_code entered_currency,
                    NVL (xal.entered_dr, 0) entered_dr,
                    NVL (xal.entered_cr, 0) entered_cr,
                    xga.ledger_currency accounted_currency,
                    NVL (xal.accounted_dr, 0) accounted_dr,
                    NVL (xal.accounted_cr, 0) accounted_cr,
                    xga.code_combination_id
            FROM    gl_code_combinations xga,
                    gl_import_references gir,
                    xla_ae_lines xal,
                    xla_ae_headers xah,
                    xla_events xe,
                    xla.xla_transaction_entities xte,
                    po_vendors pv
            WHERE   xga.je_batch_id = gir.je_batch_id
            AND xga.je_header_id = gir.je_header_id
            AND xal.ledger_id = xah.ledger_id
            AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
            AND xga.je_header_id = gir.je_header_id
            AND xga.je_line_num = gir.je_line_num
            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.gl_sl_link_table = xal.gl_sl_link_table
            AND xal.ae_header_id = xah.ae_header_id
            AND xal.application_id = xah.application_id
            AND xah.event_id = xe.event_id
            AND xah.application_id = xe.application_id
            AND xe.entity_id = xte.entity_id
            AND xe.application_id = xte.application_id
            AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
            AND xal.party_id = pv.vendor_id(+)
            AND UPPER (xga.je_source) in ( 'PAYABLES','RECEIVABLES','ASSETS')

API/Script to create Oracle HRMS - Employee Extra Information details

create or replace PROCEDURE XX_EMP_RESIGNATION_HIS(P_PERSON_ID IN NUMBER)AS
 
  PRAGMA AUTONOMOUS_TRANSACTION;

  ln_person_id NUMBER;
  ln_lwd_date per_people_extra_info.pei_information3%TYPE := NULL;
  lc_TERMI_ACCEPTED_PERSON_ID per_people_extra_info.pei_information1%TYPE;
  lc_ACCEPTED_TERMINATION_DATE per_people_extra_info.pei_information2%TYPE;
  lc_ACTUAL_TERMINATION_DATE per_people_extra_info.pei_information3%TYPE;
  lc_FINAL_PROCESS_DATE per_people_extra_info.pei_information4%TYPE;
  lc_LAST_STANDARD_PROCESS_DATE per_people_extra_info.pei_information5%TYPE;
  lc_LEAVING_REASON per_people_extra_info.pei_information6%TYPE;
  lc_NOTIFIED_TERMINATION_DATE per_people_extra_info.pei_information7%TYPE;
  lc_PROJECTED_TERMINATION_DATE per_people_extra_info.pei_information8%TYPE;
  lc_ATTRIBUTE1 per_people_extra_info.pei_information9%TYPE;
  lc_ATTRIBUTE2 per_people_extra_info.pei_information10%TYPE;
  lc_ATTRIBUTE3 per_people_extra_info.pei_information17%TYPE;
  lc_ATTRIBUTE4 per_people_extra_info.pei_information11%TYPE;
  lc_ATTRIBUTE5 per_people_extra_info.pei_information12%TYPE;
  lc_ATTRIBUTE6 per_people_extra_info.pei_information13%TYPE;
  lc_ATTRIBUTE7 per_people_extra_info.pei_information14%TYPE;
  lc_ATTRIBUTE8 per_people_extra_info.pei_information15%TYPE;
  lc_ATTRIBUTE9 per_people_extra_info.pei_information16%TYPE;
 
  lc_information_type per_people_extra_info.information_type%TYPE :=  'EIT_RESIGNATION_HISTORY';
  lc_pei_information_category  per_people_extra_info.pei_information_category%TYPE := 'EIT_RESIGNATION_HISTORY';
  ln_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE :=  NULL;
  ln_object_version_number per_people_extra_info.object_version_number%TYPE := NULL;
  v_error VARCHAR2 (3000);
 
  CURSOR cur_pps_det(c_person_id IN NUMBER)
  IS
   
    SELECT
    PERSON_ID
    , TERMINATION_ACCEPTED_PERSON_ID
    , TO_CHAR(ACCEPTED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') ACCEPTED_TERMINATION_DATE
    , TO_CHAR(ACTUAL_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') ACTUAL_TERMINATION_DATE
    , TO_CHAR(FINAL_PROCESS_DATE,'RRRR/MM/DD HH24:MI:SS') FINAL_PROCESS_DATE
    , TO_CHAR(LAST_STANDARD_PROCESS_DATE,'RRRR/MM/DD HH24:MI:SS') LAST_STANDARD_PROCESS_DATE
    , LEAVING_REASON
    , TO_CHAR(NOTIFIED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') NOTIFIED_TERMINATION_DATE
    , TO_CHAR(PROJECTED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') PROJECTED_TERMINATION_DATE
    , ATTRIBUTE1
    , ATTRIBUTE2
    , ATTRIBUTE3
    , ATTRIBUTE4
    , ATTRIBUTE5
    , ATTRIBUTE6
    , ATTRIBUTE7
    , ATTRIBUTE8
    , ATTRIBUTE9
    FROM
      per_periods_of_service
    WHERE
      PERSON_ID = c_person_id
      AND actual_termination_date is not null;
BEGIN
 
 
  FOR i IN cur_pps_det(p_person_id)
  LOOP
    --DBMS_OUTPUT.put_line ('After Cursor');
    BEGIN
      ln_person_id                      := i.PERSON_ID;
      lc_TERMI_ACCEPTED_PERSON_ID       := i.TERMINATION_ACCEPTED_PERSON_ID;
      lc_ACCEPTED_TERMINATION_DATE      := i.ACCEPTED_TERMINATION_DATE;
      lc_ACTUAL_TERMINATION_DATE        := i.ACTUAL_TERMINATION_DATE;
      lc_FINAL_PROCESS_DATE             := i.FINAL_PROCESS_DATE;
      lc_LAST_STANDARD_PROCESS_DATE     := i.LAST_STANDARD_PROCESS_DATE;
      lc_LEAVING_REASON                 := i.LEAVING_REASON;
      lc_NOTIFIED_TERMINATION_DATE      := i.NOTIFIED_TERMINATION_DATE;
      lc_PROJECTED_TERMINATION_DATE     := i.PROJECTED_TERMINATION_DATE;
      lc_ATTRIBUTE1                     := i.ATTRIBUTE1;
      lc_ATTRIBUTE2                     := i.ATTRIBUTE2;
      lc_ATTRIBUTE3                     := i.ATTRIBUTE3;
      lc_ATTRIBUTE4                     := i.ATTRIBUTE4;
      lc_ATTRIBUTE5                     := i.ATTRIBUTE5;
      lc_ATTRIBUTE6                     := i.ATTRIBUTE6;
      lc_ATTRIBUTE7                     := i.ATTRIBUTE7;
      lc_ATTRIBUTE8                     := i.ATTRIBUTE8;
      lc_ATTRIBUTE9                     := i.ATTRIBUTE9;
     
         
      hr_person_extra_info_api.create_person_extra_info (p_validate => FALSE,
      p_person_id                 => ln_person_id,
      p_information_type          => lc_information_type,
      p_pei_information_category  => lc_pei_information_category,
      p_pei_information1          => lc_TERMI_ACCEPTED_PERSON_ID,
      p_pei_information2          => lc_ACCEPTED_TERMINATION_DATE,
      p_pei_information3          => lc_ACTUAL_TERMINATION_DATE,
      p_pei_information4          => lc_FINAL_PROCESS_DATE,
      p_pei_information5          => lc_LAST_STANDARD_PROCESS_DATE,
      p_pei_information6          => lc_LEAVING_REASON,
      p_pei_information7          => lc_NOTIFIED_TERMINATION_DATE,
      p_pei_information8          => lc_PROJECTED_TERMINATION_DATE,
      p_pei_information9          => lc_ATTRIBUTE1,
      p_pei_information10         => lc_ATTRIBUTE2,
      p_pei_information11         => lc_ATTRIBUTE4,
      p_pei_information12         => lc_ATTRIBUTE5,
      p_pei_information13         => lc_ATTRIBUTE6,
      p_pei_information14         => lc_ATTRIBUTE7,
      p_pei_information15         => lc_ATTRIBUTE8,
      p_pei_information16         => lc_ATTRIBUTE9,
      p_pei_information17         => lc_ATTRIBUTE3,
      p_person_extra_info_id      => ln_person_extra_info_id,
      p_object_version_number     => ln_object_version_number );
      COMMIT;
      --DBMS_OUTPUT.put_line ('After API');
 
 
    EXCEPTION
   
    WHEN OTHERS THEN
      v_error := 'Loc API :' || ln_person_id || SQLERRM;
      --DBMS_OUTPUT.put_line (v_error);
    END;
 
  END LOOP;

EXCEPTION

WHEN OTHERS THEN
  v_error := SQLERRM;
  --DBMS_OUTPUT.put_line (v_error);
END XX_EMP_RESIGNATION_HIS;

EMPLOYEE type Supplier Creation API/Script in Oracle Apps AP

create or replace PACKAGE BODY XX_AP_EMP_TYPE_SUPP_PKG AS

  PROCEDURE main(errbuf   OUT VARCHAR2,
                 retcode  OUT NUMBER,
                 p_emp_id IN number,
                 p_pay_method IN varchar2,
                 p_org_id IN number
                 ) AS
 
    CURSOR c_empdet(p_emp_id IN VARCHAR2) IS
      SELECT PAPF.FULL_NAME, PAPF.PERSON_ID, PAPF.EMPLOYEE_NUMBER
        FROM PER_ALL_PEOPLE_F PAPF
       WHERE PAPF.BUSINESS_GROUP_ID = g_business_group_id
         AND PAPF.PERSON_ID = p_emp_id;
 
    cursor lcu_accoutdet(p_org_id In NUMBER) IS
      SELECT UPPER(flv.tag) SITE_CODE, attribute2, attribute3
        FROM FND_LOOKUP_VALUES FLV
       where flv.lookup_type = 'XX_AP_EMP_SUP_SITE'
         AND p_org_id = to_number(flv.attribute1);
 
    cursor c_hou IS
      select hou.name
        from hr_operating_units hou
       where hou.organization_id = p_org_id;
 
    cursor c_vendor(v_vendor_name IN varchar2) IS
      select pv.vendor_id, pv.vendor_type_lookup_code
        from po_vendors pv
       where UPPER(vendor_name) = UPPER(v_vendor_name);
     
    cursor c_vensite(p_vendor_id IN NUMBER,p_org_id IN NUMBER) IS
     SELECT COUNT(1)
     FROM ap_supplier_sites_all
     WHERE vendor_id = p_vendor_id
     AND UPPER(VENDOR_SITE_CODE) IN ('HOME','OFFICE','PROVISIONAL')
     AND ORG_ID = p_org_id;
 
    l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;
    l_vendor_site_rec  ap_vendor_pub_pkg.r_vendor_site_rec_type;
    ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.external_payee_rec_type;

    l_return_status    VARCHAR2(10);
    l_msg_count        NUMBER;
    l_msg_data         VARCHAR2(1000);
    l_vendor_id        NUMBER;
    l_party_id         NUMBER;
    v_empname          VARCHAR2(50);
    v_empid            NUMBER;
    p_count            number;
    l_vendor_site_id   NUMBER;
    l_party_site_id    NUMBER;
    l_location_id      NUMBER;
    l_liability_id     NUMBER;
    l_prepay_id        NUMBER;
    v_error            varchar2(1000);
    v_org_name         varchar2(50);
    l_vendor_type_code varchar2(20);
    v_employee_number  varchar2(20);
    ln_sup_site_cnt     number;
 
  BEGIN
 
    v_error := 'S';
 
    apps.fnd_file.put_line(apps.fnd_file.output,
                           '***************Employee Type Supplier Creation***********************');
    apps.fnd_file.put_line(apps.fnd_file.output, ' ');
 
    OPEN c_hou;
    FETCH c_hou
      INTO v_org_name;
    CLOSE c_hou;
 
    apps.fnd_file.put_line(apps.fnd_file.output,
                           ' Organization : ' || v_org_name);
    apps.fnd_file.put_line(apps.fnd_file.output, ' ');
 
    OPEN c_empdet(p_emp_id);
    FETCH c_empdet
      INTO v_empname, v_empid, v_employee_number;
    CLOSE c_empdet;
 
    IF v_empname IS NULL THEN
   
      apps.fnd_file.put_line(apps.fnd_file.output,
                             ' Employee does not exist for Employee Number#' ||
                             v_employee_number);
   
    ELSE
   
      OPEN c_vendor(v_empname);
      FETCH c_vendor
        INTO l_vendor_id, l_vendor_type_code;
      CLOSE c_vendor;
   
      IF l_vendor_id IS NOT NULL THEN
     
        v_error := 'Supp_Exists';
        apps.fnd_file.put_line(apps.fnd_file.output,
                               ' Supplier Already Exists with Vendor Id#' ||
                               l_vendor_id || ' and Type ' ||
                               l_vendor_type_code);
     
        OPEN c_vensite(l_vendor_id,p_org_id);
        FETCH c_vensite INTO ln_sup_site_cnt;
        CLOSE c_vensite;
       
        IF ln_sup_site_cnt = 3 THEN
       
          retcode := '2';
          errbuf  := ' Error - Supplier and Sites have already been created.  ';
       
        END IF;
     
      END IF;
   
      IF v_error <> 'Supp_Exists' THEN
     
        apps.fnd_file.put_line(apps.fnd_file.output,
                               ' Creating Employee Type Supplier for :' ||
                               v_empname);
        apps.fnd_file.put_line(apps.fnd_file.output, ' ');
        -- --------------
        -- Required
        -- --------------
        l_vendor_rec.vendor_name             := v_empname;
        l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE := 'EMPLOYEE';
        l_vendor_rec.employee_id             := v_empid;
       
         
        apps.fnd_file.put_line(apps.fnd_file.output,
                               ' Calling CREATE_VENDOR API....');
     
        pos_vendor_pub_pkg.create_vendor( -- -------------------------
                                         -- Input Parameters
                                         -- -------------------------
                                         p_vendor_rec => l_vendor_rec,
                                         -- ----------------------------
                                         -- Output Parameters
                                         -- ----------------------------
                                         x_return_status => l_return_status,
                                         x_msg_count     => l_msg_count,
                                         x_msg_data      => l_msg_data,
                                         x_vendor_id     => l_vendor_id,
                                         x_party_id      => l_party_id);
     
        IF (l_return_status <> 'S') THEN
       
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Error in Creating Employee Type Supplier..');
       
          IF (l_msg_count = 1) THEN
            apps.fnd_file.put_line(apps.fnd_file.output,
                                   'x_msg_data ' || l_msg_data);
         
          ELSIF (l_msg_count > 1) THEN
            LOOP
              p_count    := p_count + 1;
              l_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,
                                            fnd_api.g_false);
           
              IF (l_msg_data IS NULL) THEN
                EXIT;
              END IF;
           
              apps.fnd_file.put_line(apps.fnd_file.output,
                                     ' Error Message' || p_count || ' ---' ||
                                     l_msg_data);
            END LOOP;
          END IF;
       
        ELSE
       
          apps.fnd_file.put_line(apps.fnd_file.output, ' ');
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Successfully Cretaed Supplier with below details,');
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Supplier Name :' || v_empname);
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Vendor Id     :' || l_vendor_id);
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Party Id      :' || l_party_id);
       
          apps.fnd_file.put_line(apps.fnd_file.output, ' ');
          apps.fnd_file.put_line(apps.fnd_file.output,
                                 ' Creating Supplier Sites...');
          apps.fnd_file.put_line(apps.fnd_file.output, ' ');
       
    END IF;
   
    END IF;
   
    IF l_vendor_id IS NOT NULL AND NVL(ln_sup_site_cnt,0) <> 3 THEN
         
          BEGIN
         
            FOR i_rec IN lcu_accoutdet(p_org_id) LOOP
           
              l_liability_id := f_ccid(p_org_id, i_rec.attribute2);
              l_prepay_id    := f_ccid(p_org_id, i_rec.attribute3);
           
              IF l_liability_id IS NULL OR l_prepay_id IS NULL THEN
             
                v_error := 'E';
                apps.fnd_file.put_line(apps.fnd_file.output,
                                       ' Liability Account/Prepayment Account does not exists for bewlow segments,');
                apps.fnd_file.put_line(apps.fnd_file.output,
                                       ' Liability Account  :' ||
                                       i_rec.attribute2);
                apps.fnd_file.put_line(apps.fnd_file.output,
                                       ' Prepayment Account :' ||
                                       i_rec.attribute3);
             
              END IF;
           
              IF v_error <> 'E' THEN
             
                l_vendor_site_rec.vendor_id                     := l_vendor_id;
                l_vendor_site_rec.vendor_site_code              := i_rec.site_code;
                l_vendor_site_rec.org_id                        := p_org_id;
                l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_liability_id;
                l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID    := l_prepay_id;
                l_vendor_site_rec.TERMS_DATE_BASIS              := 'Invoice';
               
                ext_payee_rec.default_pmt_method := p_pay_method;
                l_vendor_site_rec.ext_payee_rec.default_pmt_method:= p_pay_method;
             
                pos_vendor_pub_pkg.create_vendor_site(
                                                      -- ------------------------------
                                                      -- Input data elements
                                                      -- ------------------------------
                                                      p_vendor_site_rec => l_vendor_site_rec,
                                                      -- ---------------------------------
                                                      -- Output data elements
                                                      -- ---------------------------------
                                                      x_return_status  => l_return_status,
                                                      x_msg_count      => l_msg_count,
                                                      x_msg_data       => l_msg_data,
                                                      x_vendor_site_id => l_vendor_site_id,
                                                      x_party_site_id  => l_party_site_id,
                                                      x_location_id    => l_location_id);
             
                IF (l_return_status <> 'S') THEN
               
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Error in Creating Employee Type Supplier..');
               
                  retcode := '1';
                  errbuf  := ' Error in Supplier Site Creation-';
                 
                  IF (l_msg_count = 1) THEN
                    apps.fnd_file.put_line(apps.fnd_file.output,
                                           'x_msg_data ' || l_msg_data);
                 
                  ELSIF (l_msg_count > 1) THEN
                    LOOP
                      p_count    := p_count + 1;
                      l_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                    fnd_api.g_false);
                   
                      IF (l_msg_data IS NULL) THEN
                        EXIT;
                      END IF;
                   
                      apps.fnd_file.put_line(apps.fnd_file.output,
                                             ' Error Message' || p_count ||
                                             ' ---' || l_msg_data);
                    END LOOP;
                  END IF;
               
                ELSE
               
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Successfully Cretaed Supplier Site with below details,');
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Supplier Site       :' ||
                                         i_rec.site_code);
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Vendor Site Id      :' ||
                                         l_vendor_site_id);
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Party Site Id       :' ||
                                         l_party_site_id);
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Location Id         :' ||
                                         l_location_id);
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Liability Account   :' ||
                                         i_rec.attribute2);
                  apps.fnd_file.put_line(apps.fnd_file.output,
                                         ' Prepayment Account  :' ||
                                         i_rec.attribute3);
                  apps.fnd_file.put_line(apps.fnd_file.output, ' ');
               
                END IF;
             
              END IF;
           
            END LOOP;
         
          EXCEPTION
            WHEN OTHERS THEN
              apps.fnd_file.put_line(apps.fnd_file.output,
                                     ' Error -  ' || SQLERRM);
           
          END;
       
        END IF;
     
      END IF;
   

    apps.fnd_file.put_line(apps.fnd_file.output,
                           '*************** END ***********************');
 
  EXCEPTION
    WHEN OTHERS THEN
      retcode := '2';
      errbuf  := ' Error in Main-  ' || SQLERRM;
      apps.fnd_file.put_line(apps.fnd_file.output,
                             ' Error in Main-  ' || SQLERRM);
   
  END main;

  FUNCTION f_ccid(p_org_id IN NUMBER, p_segments IN VARCHAR2) RETURN NUMBER IS
 
    v_ccid NUMBER;
 
  BEGIN
 
    SELECT gcc.code_combination_id
      INTO v_ccid
      FROM apps.gl_code_combinations_kfv gcc,
           apps.hr_operating_units       hou,
           apps.gl_Sets_of_books         sob
     WHERE NVL(gcc.end_date_active, SYSDATE + 1) >= TRUNC(sysdate)
       AND NVL(gcc.enabled_flag, 'N') = 'Y'
       AND hou.organization_id = p_org_id
       AND sob.set_of_books_id = hou.set_of_books_id
       AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id
       AND UPPER(gcc.concatenated_segments) = UPPER(p_segments);
 
    RETURN v_ccid;
 
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 0;
  END f_ccid;

END XX_AP_EMP_TYPE_SUPP_PKG;

API/Script to update Supplier and Supplier Sites Oracle Apps R12

DECLARE

  l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;

  l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;

  x_vendor_site_id NUMBER;

  x_vendor_id NUMBER;

  x_msg_data       VARCHAR2(1000);

  x_msg_count      NUMBER;

  p_count          NUMBER;

  x_return_status VARCHAR2(10);

 

  CURSOR lcu_rec

  IS

    SELECT vendor_id,

      vendor_site_id,

      org_id

    FROM ap_supplier_sites_all

    WHERE match_option <> 'R';

   

  CURSOR lcu_head IS

   SELECT vendor_id

   FROM PO_VENDORS

   WHERE match_option <> 'R';

 

    

BEGIN

/*Supplier Update*/

  FOR j IN lcu_head

  LOOP

    x_vendor_id := j.vendor_id;      -- Supplier Id

    l_vendor_rec.match_option := 'R';              -- eg: Match Option

   

    AP_VENDOR_PUB_PKG.Update_Vendor(p_api_version          => 1,

                x_return_status => x_return_status,

    x_msg_count => x_msg_count,

    x_msg_data => x_msg_data,

    p_vendor_rec                               => l_vendor_rec,

    p_vendor_id                  => x_vendor_id

  );

      

    IF (x_msg_count = 1) THEN

      dbms_output.put_line('x_msg_data ' || x_msg_data);

    ELSIF (x_msg_count > 1) THEN

      LOOP

        p_count         := p_count + 1;

        x_msg_data     := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

        IF (x_msg_data IS NULL) THEN

          EXIT;

        END IF;

        dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

    END IF;

   

  END LOOP;

  /*Supplier Sites Update*/

  FOR i IN lcu_rec

  LOOP

    l_vendor_site_rec.vendor_id    := i.vendor_id;      -- Supplier Id

    l_vendor_site_rec.org_id       := i.org_id;         -- Operating Unit id

    x_vendor_site_id               := i.vendor_site_id; -- Site Id to be updated

    l_vendor_site_rec.match_option := 'R';              -- eg: Match Option

   

    AP_VENDOR_PUB_PKG.Update_Vendor_Site ( p_api_version => 1,

    x_return_status => x_return_status,

    x_msg_count => x_msg_count,

    x_msg_data => x_msg_data,

    p_vendor_site_rec => l_vendor_site_rec,

    p_vendor_site_id => x_vendor_site_id );

   

    IF (x_msg_count = 1) THEN

      dbms_output.put_line('Vendor:'||i.vendor_id||' x_msg_data ' || x_msg_data);

    ELSIF (x_msg_count > 1) THEN

      LOOP

        p_count         := p_count + 1;

        x_msg_data     := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

        IF (x_msg_data IS NULL) THEN

          EXIT;

        END IF;

        dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

    END IF;

   

    COMMIT;

   

  END LOOP;

 

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Error:'||SQLERRM);

END;

Oracle HRMS Update Person API script

DECLARE

  ln_object_version_number PER_ALL_PEOPLE_F.OBJECT_VERSION_NUMBER%TYPE := 3;
  --   lc_dt_ud_mode                            VARCHAR2(100)                                                                                     := NULL;

  lc_employee_number VARCHAr2(30) := '105929';

  -- Out Variables for Find Date Track Mode API
  -- ----------------------------------------------------------------
  lb_correction           BOOLEAN;
  lb_update               BOOLEAN;
  lb_update_override      BOOLEAN;
  lb_update_change_insert BOOLEAN;

  -- Out Variables for Update Employee API
  -- -----------------------------------------------------------
  ld_effective_start_date     DATE;
  ld_effective_end_date       DATE;
  lc_full_name                PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
  ln_comment_id               PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
  lb_name_combination_warning BOOLEAN;
  lb_assign_payroll_warning   BOOLEAN;
  lb_orig_hire_warning        BOOLEAN;
  lc_dt_ud_mode               VARCHAR2(200) := 'CORRECTION';

BEGIN

  hr_person_api.update_person( -- Input Data Elements
                              -- ------------------------------
                              p_effective_date        => TO_DATE('26-MAY-2014'),
                              p_datetrack_update_mode => lc_dt_ud_mode,
                              p_person_id             => 3575,
                              p_last_name             => 'Prasad',
                              -- Output Data Elements
                              -- ----------------------------------
                              p_employee_number          => lc_employee_number,
                              p_object_version_number    => ln_object_version_number,
                              p_effective_start_date     => ld_effective_start_date,
                              p_effective_end_date       => ld_effective_end_date,
                              p_full_name                => lc_full_name,
                              p_comment_id               => ln_comment_id,
                              p_name_combination_warning => lb_name_combination_warning,
                              p_assign_payroll_warning   => lb_assign_payroll_warning,
                              p_orig_hire_warning        => lb_orig_hire_warning);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Oracle AP -> SLA -> GL SQL Tables Linkage

SELECT
  aia.INVOICE_ID "Invoice Id",
  aia.INVOICE_NUM "Invoice Number",
  aia.INVOICE_DATE "Invoice Date",
  aia.INVOICE_AMOUNT "Amount",
  xal.ENTERED_DR "Entered DR in SLA",
  xal.ENTERED_CR "Entered CR in SLA",
  xal.ACCOUNTED_DR "Accounted DR in SLA",
  xal.ACCOUNTED_CR "Accounted CR in SLA",
  gjl.ENTERED_DR "Entered DR in GL",
  gjl.ACCOUNTED_DR "Accounted DR in GL",
  xal.ACCOUNTING_CLASS_CODE "Accounting Class",
  gcc.SEGMENT1
  ||'.'
  ||gcc.SEGMENT2
  ||'.'
  ||gcc.SEGMENT3
  || '.'
  ||gcc.SEGMENT4
  ||'.'
  ||gcc.SEGMENT5
  ||'.'
  ||gcc.SEGMENT6
  ||'.'
  || gcc.SEGMENT7 "Code Combination",
  aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
  aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
  aia.GL_DATE "GL Date",
  xah.PERIOD_NAME "Period",
  aia.PAYMENT_METHOD_CODE "Payment Method",
  aia.VENDOR_ID "Vendor Id",
  aps.VENDOR_NAME "Vendor Name",
  xah.JE_CATEGORY_NAME "JE Category Name"
FROM
  ap.ap_invoices_all aia,
  xla.xla_transaction_entities XTE,
  xla.xla_events xev,
  xla.xla_ae_headers XAH,
  xla.xla_ae_lines XAL,
  gl.GL_IMPORT_REFERENCES gir,
  gl.gl_je_headers gjh ,
  gl.gl_je_lines gjl,
  gl.gl_code_combinations gcc,
  ap.ap_suppliers aps,
  (
    SELECT
      aid1.invoice_id,
      pa.project_id,
      NVL(pa.segment1,'NO PROJECT') Project
    FROM
      ap.ap_invoice_distributions_all aid1,
      ap.PA_PROJECTS_ALL pa
    WHERE
      aid1.rowid IN
      (
        SELECT
          MAX(rowid)
        FROM
          ap.ap_invoice_distributions_all aid2
        WHERE
          aid1.INvoice_ID=aid2.INvoice_ID
        GROUP BY
          aid1.invoice_id
      )
    AND aid1.project_id=pa.project_id(+)
  )
  sql1,
  (
    SELECT
      aid1.invoice_id,
      pt.task_id,
      NVL(pt.task_number,'NO TASK') Task
    FROM
      ap.ap_invoice_distributions_all aid1,
      apps.PA_TASKS pt
    WHERE
      aid1.rowid IN
      (
        SELECT
          MAX(rowid)
        FROM
          apps.ap_invoice_distributions_all aid2
        WHERE
          aid1.INvoice_ID=aid2.INvoice_ID
        GROUP BY
          aid1.invoice_id
      )
    AND aid1.task_id=pt.task_id(+)
  )
  sql2
WHERE
  aia.INVOICE_ID                = xte.source_id_int_1
AND aia.INVOICE_ID              = sql1.Invoice_ID
AND aia.INVOICE_ID              = sql2.Invoice_ID
AND xev.entity_id               = xte.entity_id
AND xah.entity_id               = xte.entity_id
AND xah.event_id                = xev.event_id
AND XAH.ae_header_id            = XAL.ae_header_id
AND XAH.je_category_name        = 'Purchase Invoices'
AND XAH.gl_transfer_status_code = 'Y'
AND XAL.GL_SL_LINK_ID           = gir.GL_SL_LINK_ID
AND gir.GL_SL_LINK_TABLE        = xal.GL_SL_LINK_TABLE
AND gjl.JE_HEADER_ID            =gjh.JE_HEADER_ID
AND gjh.JE_HEADER_ID            =gir.JE_HEADER_ID
AND gjl.JE_HEADER_ID            =gir.JE_HEADER_ID
AND gir.JE_LINE_NUM             =gjl.JE_LINE_NUM
AND gcc.CODE_COMBINATION_ID     =XAL.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID     =gjl.CODE_COMBINATION_ID
AND aia.VENDOR_ID               =aps.VENDOR_ID
AND gjh.STATUS                  = 'P'
AND gjh.Actual_flag             ='A'
AND gjh.CURRENCY_CODE           = 'USD'
AND aia.Invoice_id              =
  &Invoice_Id;

Oracle Expenses SQL Query to find Expense details

SELECT GSOB.NAME "SOB Name",
       AEH.INVOICE_NUM "Report Number",
       AEH.TOTAL,
       AEH.DESCRIPTION,
       PPX.FULL_NAME,
       PPX.EMPLOYEE_NUMBER,
       decode(AEH.WORKFLOW_APPROVED_FLAG,'S','SAVED','I','IMPLICIT SAVE','R','MANAGER REJECTED','M','MANAGER APPROVED','P','PAYABLES APPROVED','A','AUTO APPROVED','W','WITHDRAWN','Y','APPROVED','IN PROGRESS') Status,
       (SELECT APPS.AP_WEB_POLICY_UTILS.GET_LOOKUP_MEANING('EXPENSE REPORT STATUS',
                                                      DECODE(AI.CANCELLED_DATE,
                                                             NULL,
                                                             AEH.EXPENSE_STATUS_CODE,
                                                             'CANCELLED'))
          FROM APPS.AP_INVOICES_ALL AI
         WHERE AI.INVOICE_ID(+) = AEH.VOUCHNO) "Report status",
       NVL(AEH.REPORT_SUBMITTED_DATE, AEH.CREATION_DATE) "Report submitted on",
       CASE
         WHEN NVL2(AAP.ENTERED_DATE, 'Y', 'N') = 'Y' THEN
          AAP.ENTERED_DATE
       END "Manager Approved on",
       AEH.EXPENSE_STATUS_CODE,
       TRUNC(AEH.WEEK_END_DATE) "Report entered date",
       ACA.EMPLOYEE_NUMBER "Auditor Emp Num",
       ACA.FULL_NAME "Auditor Name",
       ACA.ENTERED_DATE "Audited on"
  FROM APPS.GL_SETS_OF_BOOKS GSOB,
       APPS.AP_EXPENSE_REPORT_HEADERS_ALL AEH,
       APPS.PER_PEOPLE_X PPX,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               AN.NOTE_ID,
               AN.SOURCE_OBJECT_ID,
               AN.ENTERED_BY,
               AN.ENTERED_DATE,
               PPX.EMPLOYEE_NUMBER,
               PPX.FULL_NAME
          FROM APPS.AP_NOTES AN, APPS.FND_USER FU, APPS.PER_PEOPLE_X PPX
         WHERE AN.ENTERED_BY = FU.USER_ID
           AND FU.EMPLOYEE_ID = PPX.PERSON_ID
           AND AN.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(AN.NOTES_DETAIL, 1, 24)) =
               'Approver Action: Approve') AAP,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               ANS.NOTE_ID,
               ANS.SOURCE_OBJECT_ID,
               ANS.ENTERED_BY,
               ANS.ENTERED_DATE,
               PPLX.EMPLOYEE_NUMBER,
               PPLX.FULL_NAME
          FROM APPS.AP_NOTES ANS, APPS.FND_USER FUR, APPS.PER_PEOPLE_X PPLX
         WHERE ANS.ENTERED_BY = FUR.USER_ID
           AND FUR.EMPLOYEE_ID = PPLX.PERSON_ID
           AND ANS.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(ANS.NOTES_DETAIL, 1, 15)) = 'Complete Audit.') ACA
 WHERE 1 = 1
   AND AEH.REPORT_HEADER_ID = ACA.SOURCE_OBJECT_ID(+)
   AND AEH.REPORT_HEADER_ID = AAP.SOURCE_OBJECT_ID(+)
   AND AEH.EMPLOYEE_ID = PPX.PERSON_ID
   AND GSOB.SET_OF_BOOKS_ID = AEH.SET_OF_BOOKS_ID
   AND AEH.EXPENSE_STATUS_CODE NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED')  
   AND NVL (AAP.SRL, 1) = 1  
   AND NVL (ACA.SRL, 1) = 1  
   AND AEH.CREATION_DATE BETWEEN TO_DATE (:DATE_FROM_DDMONYYYY, 'DD-MON-YYYY') AND  TO_DATE (:DATE_TO_DDMONYYYY, 'DD-MON-YYYY')
   AND TRUNC (SYSDATE) BETWEEN TRUNC (PPX.EFFECTIVE_START_DATE) AND TRUNC (PPX.EFFECTIVE_END_DATE);

PO Approval History - SQL Query

SELECT DISTINCT
  prh.creation_date req_creation_date,
  prh.segment1 req_number,
  gl.SEGMENT3 COST_CENTER,
  hp.full_name REQUESTOR,
  lp.full_name PREPARER_NAME,
  gl.SEGMENT1 COMPANY,
  cat.segment1 category,
  (prl.unit_price* prl.quantity) requisition_amount,
  prh.AUTHORIZATION_STATUS,
  poh.segment1 po_number,
  pv.vendor_name vendor,
  poh.AUTHORIZATION_STATUS PO_STATUS,
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 0
  ) ||' BY ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 0
  ) ||' -> ' ||
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 1
  ) ||' -> ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 1
  ) ||' -> ' ||
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 2
  ) ||' -> ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 2
  ) ||' -> ' ||
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 3
  ) ||' -> ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 3
  ) ||' -> ' ||
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 4
  ) ||' -> ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 4
  ) ||' -> ' ||
  (
    SELECT app.action_code
    FROM apps.PO_ACTION_HISTORY app
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.sequence_num        = 5
  ) ||' -> ' ||
  (
    SELECT DISTINCT per.full_name
    FROM apps.PO_ACTION_HISTORY app,
      apps.PER_PEOPLE_V7 per,
      apps.PER_ASSIGNMENTS_V7 dep
    WHERE prh.REQUISITION_HEADER_ID = app.object_id (+)
    AND app.employee_id         = per.person_id(+)
    AND per.person_id           = dep.person_id(+)
    AND sequence_num            = 5
  )
  APPROVAL_FLOW
FROM
  apps.PO_REQUISITION_HEADERS_ALL prh,
  apps.PO_REQUISITION_LINES_ALL prl,
  apps.PO_REQ_DISTRIBUTIONS_ALL prd,
  apps.per_all_people_f hp ,
  apps.per_all_people_f lp ,
  apps.po_distributions_all pod,
  apps.po_lines_all pol,
  apps.po_headers_all poh,
  apps.po_vendors pv,
  apps.GL_CODE_COMBINATIONS GL,
  apps.MTL_CATEGORIES CAT
WHERE
  prh.org_id     = 182
AND prh.segment1 = NVL(:REQ_NUMBER,prh.segment1)
AND prh.creation_date BETWEEN TRUNC(to_date(:P_FROM_DATE,
  'YYYY/MM/DD HH24:MI:SS')) AND TRUNC(to_date(:P_TO_DATE,
  'YYYY/MM/DD HH24:MI:SS'))
AND prh.preparer_id           = NVL(:CREATED_BY,prh.preparer_id)
AND prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
AND prl.REQUISITION_LINE_ID   = prd.REQUISITION_LINE_ID
AND prh.preparer_id           = hp.person_id
AND prl.to_person_id          = lp.person_id
AND prd.distribution_id       = pod.req_distribution_id (+)
AND pod.po_line_id            = pol.po_line_id (+)
AND pod.PO_HEADER_ID          = poh.PO_HEADER_ID (+)
AND poh.vendor_id             = pv.vendor_id (+)
AND prd.code_combination_id   = gl.code_combination_id
AND prl.category_id           = cat.category_id
AND prh.AUTHORIZATION_STATUS <> 'SYSTEM_SAVED'
ORDER BY
  prh.creation_date;

SQL Query to find Oracle Apps Concurrent Request Details

/*Query to find Error/Concurrent pragrams and their details based on date parameter*/
select fcr.request_id,
       fcp.USER_CONCURRENT_PROGRAM_NAME,
       flv.meaning,
      round((nvl(FCR.actual_completion_date,Sysdate) - FCR.actual_start_date)* 24 *60,2)  "TIME TAKEN SO FAR" ,
      to_char(fcr.actual_start_date,'DD-MON-RRRR HH24:MI:SS') start_date,
       to_char(fcr.actual_completion_date,'DD-MON-RRRR HH24:MI:SS') end_date,
      fcr.argument_text ,
       (select fu.user_name
          from apps.fnd_user fu
         where fu.user_id = fcr.requested_by) user_name,      
       fcr.status_code,
       (select fa.application_short_name
          from apps.fnd_application fa
         where fa.application_id = fcp.application_id) application_name,
       (select fr.responsibility_key from apps.fnd_responsibility fr where fr.responsibility_id = fcr.responsibility_id) responsibility_name,
       fcr.request_date,
       fcr.completion_text    
  from apps.fnd_concurrent_requests    fcr,
       apps.fnd_concurrent_programs_tl fcp,
       apps.fnd_lookup_values          flv
 where trunc(fcr.actual_start_date) >= trunc(sysdate-2) --Parameter
       and fcr.status_code in ('C','R','E','G','Q','W','X') and
       fcr.concurrent_program_id = fcp.concurrent_program_id and  
       fcp.language = 'US' and flv.language = 'US' and
       flv.lookup_code = fcr.status_code AND
       flv.lookup_type = 'CP_STATUS_CODE' and
       flv.start_date_active is not null
       and       fcp.USER_CONCURRENT_PROGRAM_NAME like 'GL Archive and Purge'
order by fcr.actual_start_date desc;

 SELECT fcp.concurrent_program_name "Prog Short Name",
  fcpt.user_concurrent_program_name "Program Name"   ,
  fcpt.description "Program desc"                    ,
  frg.request_group_name "Request Group"             ,
  frg.description "Request group desc"               ,
  frt.responsibility_name "Responsibility Name"      ,
  frt.description "Responsibility desc"
   FROM apps.fnd_responsibility fr    ,
  apps.fnd_responsibility_tl frt      ,
  apps.fnd_request_groups frg         ,
  apps.fnd_request_group_units frgu   ,
  apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_concurrent_programs fcp
  WHERE frt.responsibility_id  = fr.responsibility_id
AND fr.request_group_id        = frg.request_group_id
AND frg.request_group_id       = frgu.request_group_id
AND frgu.request_unit_id       = fcpt.concurrent_program_id
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND frgu.request_unit_type     = 'P'
AND fcpt.user_concurrent_program_name LIKE '%Cross-Validation Rules Listing%'

SELECT

  frsv.user_request_set_name "Request Set Name"

, frsv.description "Request Set desc"

, frg.request_group_name "Request Group Name"

, frg.description "Request group desc"

, frt.responsibility_name "Responsibility Name"

, frt.description "Responsibility desc"

FROM

  fnd_responsibility fr

, fnd_responsibility_tl frt

, fnd_request_groups frg

, fnd_request_group_units frgu

, fnd_request_sets_vl frsv

WHERE

  frt.responsibility_id        = fr.responsibility_id

AND fr.request_group_id        = frg.request_group_id

AND frg.request_group_id       = frgu.request_group_id

AND frgu.request_unit_id       = frsv.request_set_id

AND frgu.request_unit_type     = 'S'

AND frsv.user_request_set_name =

  'GL Daily and Periodic Rates Import Request Set' 

/*Query to find Concurrent program details and request set*/

SELECT DISTINCT

  FCPL.USER_CONCURRENT_PROGRAM_NAME

, FCP.CONCURRENT_PROGRAM_NAME

, FAPP.APPLICATION_NAME

, FRG.REQUEST_GROUP_NAME

, FNRTL.RESPONSIBILITY_NAME

FROM

  APPS.FND_REQUEST_GROUPS FRG

, APPS.FND_APPLICATION_TL FAPP

, APPS.FND_REQUEST_GROUP_UNITS FRGU

, APPS.FND_CONCURRENT_PROGRAMS FCP

, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL

, APPS.FND_RESPONSIBILITY FNR

, APPS.FND_RESPONSIBILITY_TL FNRTL

WHERE

  FRG.APPLICATION_ID          =fapp.APPLICATION_ID

AND FRG.APPLICATION_ID        = FRGU.APPLICATION_ID

AND FRG.REQUEST_GROUP_ID      = FRGU.REQUEST_GROUP_ID

AND FRG.REQUEST_GROUP_ID      = FNR.REQUEST_GROUP_ID

AND FRG.APPLICATION_ID        = FNR.APPLICATION_ID

AND FNR.RESPONSIBILITY_ID     = FNRTL.RESPONSIBILITY_ID

AND FRGU.REQUEST_UNIT_ID      = FCP.CONCURRENT_PROGRAM_ID

AND FRGU.UNIT_APPLICATION_ID  = FCP.APPLICATION_ID

AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID

AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE ' Non Cash and TRecs Request Set'

AND FNRTL.LANGUAGE = 'US'

AND FAPP.LANGUAGE  = 'US';

/*Query to find Responsibility and Group Attached to concurrent request*/

SELECT

  frt.responsibility_name

, frg.request_group_name

, frg.description

FROM

  fnd_request_groups frg

,fnd_request_group_units frgu

,fnd_concurrent_programs fcp

,fnd_concurrent_programs_tl fcpt

,fnd_responsibility_tl frt

,fnd_responsibility frs

WHERE

  frgu.unit_application_id    = fcp.application_id

AND frgu.request_unit_id      = fcp.concurrent_program_id

AND frg.request_group_id      = frgu.request_group_id

AND frg.application_id        = frgu.application_id

AND fcpt.source_lang          = USERENV('LANG')

AND fcp.application_id        = fcpt.application_id

AND fcp.concurrent_program_id = fcpt.concurrent_program_id

AND frs.application_id        = frt.application_id

AND frs.responsibility_id     = frt.responsibility_id

AND frt.source_lang           = USERENV('LANG')

AND frs.request_group_id      = frg.request_group_id

AND frs.application_id        = frg.application_id

  -- AND   fcp.concurrent_program_name = <shortname>

AND fcpt.user_concurrent_program_name LIKE

  '%US AMERITAS Outbound Interface Program%'

AND frt.responsibility_name LIKE 'US Fin%';

Inventory Items Update API/Interface Script

/**************************API******************************/
DECLARE
   l_item_tbl_typ                               ego_item_pub.item_tbl_type;
   x_item_table                                 ego_item_pub.item_tbl_type;
   x_inventory_item_id                          mtl_system_items_b.inventory_item_id%TYPE;
   x_organization_id                            mtl_system_items_b.organization_id%TYPE;
   x_return_status                              VARCHAR2 (1);
   x_msg_count                                  NUMBER (10);
   x_msg_data                                   VARCHAR2 (1000);
   x_message_list                               error_handler.error_tbl_type;
 
   CURSOR lcu_validated_data(cp_status_flag VARCHAR2) IS
      SELECT ORG_ID,
             inventory_item_id
        FROM INV_ITEM_CONV PIR
       WHERE PIR.status_flag = cp_status_flag
       AND EXISTS (select 1 from mtl_system_items_b where price_tolerance_percent = 0 AND segment1 =
       PIR.ITEM_CODE AND ORGANIZATION_ID = PIR.ORG_ID);
 
BEGIN


   FOR i IN lcu_validated_data('SS') LOOP
           --Setting FND global variables.
           --Replace MFG user name with appropriate user name.
        /*   fnd_global.apps_initialize (11224
                                     , 20634
                                     , 401
                                      );*/
         --FIRST Item definition
         l_item_tbl_typ (1).transaction_type := 'UPDATE';   -- Replace this with 'UPDATE' for update transaction.
         l_item_tbl_typ (1).inventory_item_id := i.inventory_item_id;
         l_item_tbl_typ (1).organization_id := i.org_id;
         l_item_tbl_typ (1).price_tolerance_percent := '';
         --DBMS_OUTPUT.put_line ('=====================================');
         --DBMS_OUTPUT.put_line ('Calling EGO_ITEM_PUB.Process_Items API');
         ego_item_pub.process_items (p_api_version        => 1.0
                                   , p_init_msg_list      => fnd_api.g_true
                                   , p_commit             => fnd_api.g_true
                                   , p_item_tbl           => l_item_tbl_typ
                                   , x_item_tbl           => x_item_table
                                   , x_return_status      => x_return_status
                                   , x_msg_count          => x_msg_count
                                    );
         --DBMS_OUTPUT.put_line ('==================================');
         --DBMS_OUTPUT.put_line ('Return Status ==>' || x_return_status);
   
         IF (x_return_status = fnd_api.g_ret_sts_success)
         THEN
            FOR i IN 1 .. x_item_table.COUNT
            LOOP
               DBMS_OUTPUT.put_line ('Inventory Item Id :' || TO_CHAR (x_item_table (i).inventory_item_id));
               --DBMS_OUTPUT.put_line ('Organization Id   :' || TO_CHAR (x_item_table (i).organization_id));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Error Messages :');
            error_handler.get_message_list (x_message_list      => x_message_list);
   
            FOR i IN 1 .. x_message_list.COUNT
            LOOP
               DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
            END LOOP;
         END IF;
   
         --DBMS_OUTPUT.put_line ('==================================');
       
  END LOOP;
 
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception Occured :');
      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
      DBMS_OUTPUT.put_line ('=====================================');
END;

/**************************Interface******************************/

DECLARE

LN_BULK_ERROR_CNT NUMBER;

GN_BULK_LIMIT NUMBER := 500;

CURSOR lcu_validated_data(cp_status_flag VARCHAR2) IS

      SELECT ORG_ID,

             last_update_date,

             last_updated_by,

             creation_date,

             created_by,

             last_update_login,

             program_update_date,

             ITEM_CODE,

             DESCRIPTION,

             PRIMARY_UOM,

             expense_account_id,

             asset_category_id,

             buyer_id,

             LIST_PRICE,

             PRIMARY_UNIT_MEASURE

        FROM INV_ITEM_CONV PIR

       WHERE PIR.status_flag = cp_status_flag;

      

       TYPE validated_data_tbl_typ IS TABLE OF lcu_validated_data%ROWTYPE INDEX BY BINARY_INTEGER;

    lt_validated_data_tbl_typ validated_data_tbl_typ;

BEGIN

  OPEN lcu_validated_data('SS');

    LOOP

   

      lt_validated_data_tbl_typ.DELETE;

   

      EXIT WHEN lcu_validated_data%NOTFOUND;

      FETCH lcu_validated_data BULK COLLECT

        INTO lt_validated_data_tbl_typ LIMIT gn_bulk_limit;

   

      ln_bulk_error_cnt := 0;

   

      IF lt_validated_data_tbl_typ.COUNT > 0 THEN

     

        BEGIN

       

          --===========

          --Bulk Insert

          --===========

          FORALL i IN 1 .. lt_validated_data_tbl_typ.COUNT SAVE EXCEPTIONS

            INSERT INTO apps.mtl_system_items_interface

              (organization_id,

               last_update_date,

               last_updated_by,

               creation_date,

               created_by,

               last_update_login,

               summary_flag,

               enabled_flag,

               description,

               segment1,

               segment2,

               purchasing_item_flag,

               shippable_item_flag,

               customer_order_flag,

               internal_order_flag,

               service_item_flag,

               inventory_item_flag,

               eng_item_flag,

               inventory_asset_flag,

               purchasing_enabled_flag,

               customer_order_enabled_flag,

               internal_order_enabled_flag,

               so_transactions_flag,

               mtl_transactions_enabled_flag,

               stock_enabled_flag,

               bom_enabled_flag,

               build_in_wip_flag,

               inspection_required_flag

               -- ,bom_item_type                                   

              ,

               pick_components_flag,

               replenish_to_order_flag,

               atp_components_flag,

               atp_flag,

               inventory_item_status_code,

               min_minmax_quantity,

               max_minmax_quantity,

               vendor_warranty_flag,

               serviceable_product_flag,

               invoiceable_item_flag,

               invoice_enabled_flag,

               must_use_approved_vendor_flag,

               request_id,

               program_application_id,

               program_id,

               program_update_date,

               outside_operation_flag,

               costing_enabled_flag,

               cycle_count_enabled_flag,

               auto_created_config_flag,

               process_flag,

               item_number,

               template_id,

               transaction_type,

               transaction_id,

               set_process_id,

               lot_control_code,

               primary_unit_of_measure,

               primary_uom_code,

               attribute_category,

               attribute11,

               attribute9,

               attribute18,

               hazardous_material_flag,

               EXPENSE_ACCOUNT,

               ASSET_CATEGORY_ID,

               BUYER_ID,

               QTY_RCV_EXCEPTION_CODE,

               LIST_PRICE_PER_UNIT,

               QTY_RCV_TOLERANCE,

               PRICE_TOLERANCE_PERCENT,

               ENFORCE_SHIP_TO_LOCATION_CODE,

               ALLOW_SUBSTITUTE_RECEIPTS_FLAG,

                ALLOW_UNORDERED_RECEIPTS_FLAG,

                ALLOW_EXPRESS_DELIVERY_FLAG,

                DAYS_EARLY_RECEIPT_ALLOWED,

                DAYS_LATE_RECEIPT_ALLOWED,

                RECEIPT_DAYS_EXCEPTION_CODE,

                RECEIVING_ROUTING_ID,

                ITEM_TYPE,

                SO_AUTHORIZATION_FLAG

                )

            VALUES

              (lt_validated_data_tbl_typ(i).org_id,

               lt_validated_data_tbl_typ(i).last_update_date,

               lt_validated_data_tbl_typ(i).last_updated_by,

               lt_validated_data_tbl_typ(i).creation_date,

               lt_validated_data_tbl_typ(i).created_by,

               lt_validated_data_tbl_typ(i).last_update_login,

               NULL, --lt_validated_data_tbl_typ(i).summary_flag 

               NULL, --lt_validated_data_tbl_typ(i).enabled_flag

               lt_validated_data_tbl_typ(i).description,

               lt_validated_data_tbl_typ(i).ITEM_CODE,

               NULL,

               'Y', --lt_validated_data_tbl_typ(i).purchasing_item_flag

               NULL, --lt_validated_data_tbl_typ(i).shippable_item_flag

               NULL, --lt_validated_data_tbl_typ(i).customer_order_flag

               NULL, --lt_validated_data_tbl_typ(i).internal_order_flag

               NULL, --lt_validated_data_tbl_typ(i).service_item_flag

               NULL, --lt_validated_data_tbl_typ(i).inventory_item_flag

               NULL ,--lt_validated_data_tbl_typ(i).eng_item_flag

               NULL ,--lt_validated_data_tbl_typ(i).inventory_asset_flag

               'Y', --lt_validated_data_tbl_typ(i).purchasing_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).customer_order_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).internal_order_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).so_transactions_flag

               NULL ,--lt_validated_data_tbl_typ(i).mtl_transactions_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).stock_enabled_flag 

               NULL, --lt_validated_data_tbl_typ(i).bom_enabled_flag 

               NULL ,--lt_validated_data_tbl_typ(i).build_in_wip_flag 

               NULL,

               --,'Y'    --lt_validated_data_tbl_typ(i).bom_item_type   

               NULL, --lt_validated_data_tbl_typ(i).pick_components_flag 

               NULL, --lt_validated_data_tbl_typ(i).replenish_to_order_flag                      

               --,null    --lt_validated_data_tbl_typ(i).base_item_id 

               NULL, --lt_validated_data_tbl_typ(i).atp_components_flag 

               NULL, --lt_validated_data_tbl_typ(i).atp_flag      

               'Active', --lt_validated_data_tbl_typ(i).inventory_item_status_code

               NULL,

               NULL,

               NULL, --lt_validated_data_tbl_typ(i).vendor_warranty_flag                            

               --,null    --lt_validated_data_tbl_typ(i).serviceable_component_flag

               NULL, --lt_validated_data_tbl_typ(i).serviceable_product_flag

               NULL ,--lt_validated_data_tbl_typ(i).invoiceable_item_flag                            

               --,null    --lt_validated_data_tbl_typ(i).tax_code     

               NULL, --lt_validated_data_tbl_typ(i).invoice_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).must_use_approved_vendor_flag 

               123456, --lt_validated_data_tbl_typ(i).request_id

               NULL,

               NULL,

               lt_validated_data_tbl_typ(i).program_update_date,

               NULL, --lt_validated_data_tbl_typ(i).outside_operation_flag

               NULL, --lt_validated_data_tbl_typ(i).costing_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).cycle_count_enabled_flag

               NULL, --lt_validated_data_tbl_typ(i).auto_created_config_flag           

               --      ,pfcinv_items_recordid_s.NEXTVAL    --lt_validated_data_tbl_typ(i).transaction_id

               1, --lt_validated_data_tbl_typ(i).process_flag     

               --      ,null    --lt_validated_data_tbl_typ(i).organization_code

               NULL, --lt_validated_data_tbl_typ(i).item_number

               --      ,null    --lt_validated_data_tbl_typ(i).copy_item_number

               --      ,lt_validated_data_tbl_typ(i).template_id

               NULL, --lt_validated_data_tbl_typ(i).template_id 

               'UPDATE', --lt_validated_data_tbl_typ(i).transaction_type

               NULL, --lt_validated_data_tbl_typ(i).transaction_id

               0, --lt_validated_data_tbl_typ(i).set_process_id                                  

               -- ,lt_validated_data_tbl_typ(i).long_description

               NULL ,--lt_validated_data_tbl_typ(i).lot_control_code

               lt_validated_data_tbl_typ(i).PRIMARY_UNIT_MEASURE, --lt_validated_data_tbl_typ(i).primary_unit_of_measure

               lt_validated_data_tbl_typ(i).PRIMARY_UOM,

               NULL, --lt_validated_data_tbl_typ(i).attribute_category

               NULL, --lt_validated_data_tbl_typ(i).attribute11

               NULL, --lt_validated_data_tbl_typ(i).attribute9

               NULL, --lt_validated_data_tbl_typ(i).attribute18

               NULL, --lt_validated_data_tbl_typ(i).hazardous_material_flag

               lt_validated_data_tbl_typ(i).expense_account_id,

               lt_validated_data_tbl_typ(i).asset_category_id,

               lt_validated_data_tbl_typ(i).BUYER_ID,

               'REJECT',

               lt_validated_data_tbl_typ(i).LIST_PRICE,

               0,

                NULL,

               'WARNING', --ENFORCE_SHIP_TO_LOCATION_CODE

               'N', --ALLOW_SUBSTITUTE_RECEIPTS_FLAG

               'N', --ALLOW_UNORDERED_RECEIPTS_FLAG

               'Y', --ALLOW_EXPRESS_DELIVERY_FLAG

               5, --DAYS_EARLY_RECEIPT_ALLOWED

               5, --DAYS_LATE_RECEIPT_ALLOWED

               'WARNING', --RECEIPT_DAYS_EXCEPTION_CODE

               3, --RECEIVING_ROUTING_ID

               'P', --ITEM_TYPE

               1 --SO_AUTHORIZATION_FLAG

               );

       

        EXCEPTION

          WHEN OTHERS THEN

         

            dbms_output.put_line('Bulk Error Count :-> ' ||

                                   ln_bulk_error_cnt);

            dbms_output.put_line('Bulk Insert Error Message :-> ' ||

                                   SQLERRM);

                     

            ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

            FOR i IN 1 .. ln_bulk_error_cnt LOOP

              dbms_output.put_line('Error# ' || i || ' at iteration# ' || SQL%BULK_EXCEPTIONS(i)

                                     .ERROR_INDEX);

              dbms_output.put_line('Error Message is ' ||

                                     SQLERRM(-SQL%BULK_EXCEPTIONS(i)

                                             .ERROR_CODE));

            END LOOP;

            --ROLLBACK;

          --RAISE;

       

        END;

     

      END IF;

   

      COMMIT;

   

    END LOOP;

    CLOSE lcu_validated_data;

END;