Total Pageviews

Thursday, 9 March 2023

Oracle APPS important Links

 Oracle APPS important Links


Bursting Link

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=946475775951165&parent=EXTERNAL_SEARCH&sourceId=REFERENCE&id=1574210.1&_afrWindowMode=0&_adf.ctrl-state=phiipascj_4#aref_section33

Tuesday, 7 March 2023

Oracle Workflow : How to create Adhoc role in Workflow

How to create Adhoc role in Workflow


   PROCEDURE create_send_adhoc_role_prc (

      p_to_send_role        IN OUT   VARCHAR2,

      p_to_send_user_name   IN       VARCHAR2,

      p_user_name_tbl       IN       user_name_tbl,

      p_rtn_status          OUT      VARCHAR2,

      p_rtn_msg             OUT      VARCHAR2

   )

   IS

      l_role_cnt           NUMBER                    := 0;

      l_user_name          fnd_user.user_name%TYPE   := NULL;

      l_delete_user_name   VARCHAR2 (2000)           := NULL;

      --store multiple user name with comma seperated

      l_lst_delete_user    VARCHAR2 (2000)           := NULL;

      --store multiple user name with comma seperated

      l_user_name_tbl      user_name_tbl             := p_user_name_tbl;

      l_rtn_msg            VARCHAR2 (2000)           := NULL;

      l_user_del_sql       VARCHAR2 (2000)           := NULL;

      l_role_orig_system   VARCHAR2 (50)             := 'WF_LOCAL_ROLES';


      -------Get all the User exists in the role excepts the user pass---

      CURSOR cur_role_user (v_user_exists VARCHAR2)

      IS

         SELECT DISTINCT user_name

                    FROM wf_user_role_assignments

                   WHERE assigning_role = p_to_send_role

                     AND NVL (end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)

                     AND role_orig_system = 'WF_LOCAL_ROLES'

                     AND user_name NOT IN (v_user_exists);

   BEGIN

      -------Check role Exists----------

      SELECT COUNT (NAME)

        INTO l_role_cnt

        FROM wf_roles

       WHERE NAME = p_to_send_role AND status = 'ACTIVE';


      ------role not Exists , create role-------

      IF l_role_cnt = 0

      THEN

         l_rtn_msg := 'Start Calling createadhocrole Program.';

         p_rtn_status := 'S';

         wf_directory.createadhocrole (p_to_send_role,

                                       p_to_send_role,

                                       NULL,

                                       NULL,

                                       'Role for ' || p_to_send_role,

                                       'MAILHTML',

                                       p_to_send_user_name,

                                       -- 'NAME1 NAME2', --USER NAME SHOULD BE IN UPPER CASE

                                       NULL,

                                       NULL,

                                       'ACTIVE',

                                       NULL

                                      );

      ELSE

         ---########################Add user to adhoc Role#############---------

         BEGIN

            l_rtn_msg := 'Start Adding User to Role';


            FOR i IN 1 .. l_user_name_tbl.COUNT

            LOOP

               l_delete_user_name :=

                    l_delete_user_name || ',''' || l_user_name_tbl (i)

                    || '''';


               /*DBMS_OUTPUT.put_line (   'l_delete_user_name:'

                                     || l_delete_user_name

                                    );*/


               -------------check User Assignment for the role from role assignments table---------------

               BEGIN

                  l_rtn_msg :=

                        'check User Assignment for the role :'

                     || p_to_send_role

                     || '  from role assignments table for User:'

                     || l_user_name_tbl (i);

                  p_rtn_status := 'S';


                  SELECT DISTINCT user_name

                             INTO l_user_name

                             FROM wf_user_role_assignments

                            WHERE assigning_role = p_to_send_role

                              AND NVL (end_date, TRUNC (SYSDATE)) >=

                                                               TRUNC (SYSDATE)

                              AND role_orig_system = 'WF_LOCAL_ROLES'

                              AND user_name = l_user_name_tbl (i);


                  DBMS_OUTPUT.put_line

                             (   ' after check User Assignment for the role :'

                              || p_to_send_role

                              || '  from role assignments table for User:'

                              || l_user_name_tbl (i)

                             );

               EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                     l_rtn_msg :=

                           'user:'

                        || l_user_name_tbl (i)

                        || ' not found in role then add user to role.calling adduserstoadhocrole ';

                     p_rtn_status := 'S';

                     DBMS_OUTPUT.put_line (l_rtn_msg);


                     ----if user not found in role then add user to role--------

                     BEGIN

                        wf_directory.adduserstoadhocrole (p_to_send_role,

                                                          l_user_name_tbl (i)

                                                         );

                        l_rtn_msg :=

                             'user:' || l_user_name_tbl (i)

                             || ' Added to role';

                     --dbms_output.put_line('User Added to Role');

                     EXCEPTION

                        WHEN OTHERS

                        THEN

                           l_rtn_msg :=

                                 'Error Calling wf_directory.adduserstoadhocrole. user:'

                              || l_user_name_tbl (i)

                              || ','

                              || SQLERRM;

                           DBMS_OUTPUT.put_line (l_rtn_msg);

                           p_rtn_status := 'E';

                     END;

               END;

            END LOOP;


            --######Delete User from ADhoc Role if not present in current setup##---

            BEGIN

               l_rtn_msg :=

                     'Start Deleting User from Role. User :'

                  || l_delete_user_name;


               BEGIN

                  --------Query to Get List of lis of user which are not part of current Role setup------------

                  l_rtn_msg := 'Start preparing Query';

                  l_user_del_sql :=

                     (   'SELECT listagg(user_name,'' '') within group (order by user_name)  FROM wf_user_role_assignments WHERE assigning_role = '''

                      || p_to_send_role

                      || ''' 

                    AND NVL(end_date,TRUNC (SYSDATE))>= TRUNC (SYSDATE) AND role_orig_system='''

                      || l_role_orig_system

                      || ''' AND user_name NOT IN ('

                      || SUBSTR (l_delete_user_name, 2, 1000)

                      || ')'

                     );

                  l_rtn_msg :=

                        'Start Executing Query  to get user which are not in current role setup . Current User: '

                     || SUBSTR (l_delete_user_name, 2, 1000);


                  EXECUTE IMMEDIATE l_user_del_sql

                               INTO l_lst_delete_user;


                  IF l_lst_delete_user IS NOT NULL

                  THEN

                     l_rtn_msg :=

                           'Start Calling removeusersfromadhocrole Program for User: '

                        || l_lst_delete_user;

                     wf_directory.removeusersfromadhocrole (p_to_send_role,

                                                            l_lst_delete_user

                                                           );

                  END IF;


                  --dbms_output.put_line( l_user_del_sql  ||'---'||l_lst_delete_user);

                  --dbms_output.put_line('User kkk'||substr(l_delete_user_name,2,1000));

                  l_rtn_msg :=

                        '

                    User:'

                     || l_lst_delete_user

                     || ' Deleted Successfully from Role:'

                     || p_to_send_role

                     || '. User Exists in Role: '

                     || '('

                     || SUBSTR (l_delete_user_name, 2, 1000)

                     || ')';

                  p_rtn_status := 'S';

                  DBMS_OUTPUT.put_line (l_rtn_msg);

               EXCEPTION

                  WHEN OTHERS

                  THEN

                     l_rtn_msg :=

                           'Error Calling wf_directory.adduserstoadhocrole. user:'

                        || l_delete_user_name

                        || ','

                        || SQLERRM;

                     p_rtn_status := 'E';

               END;

            END;

         --######end Delete User from ADhoc Role if not present in current setup##---

         END;                         --###### End Add/Delete User block###---

      END IF;


      p_rtn_msg := l_rtn_msg;

   EXCEPTION

      WHEN OTHERS

      THEN

         p_rtn_status := 'E';

         p_rtn_msg := p_rtn_msg || ',' || SQLERRM;

   END create_send_adhoc_role_prc;

Monday, 27 February 2023

EBS: Oracle Forms : How to display message from database to Oracle Forms

 How to display message from database to Oracle Forms


User below code to print message in oracle forms


       fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
       fnd_message.set_token ('MESSAGE', 'Record already exists');
       fnd_message.raise_error; 

Wednesday, 15 February 2023

EBS : AP Payment API : How to upload AP Payment using API (Oracle Payment API)

 How to upload AP Payment using API

Using Below program AP Payment can be uploaded. It upload using API

CREATE OR REPLACE procedure APPS.XX0024PRX(P_PAY_ADV_NO in VARCHAR2,P_VOUCHER_ID in VARCHAR2,P_ORG_ID in NUMBER,P_MSG OUT VARCHAR2)

Is

 

V_Rowid                 Varchar2(50);

Lv_Doc                  Number;

Lv_Period_Name          Varchar2(30);

Lv_Accounting_Event_Id  Number;

L_Transaction_Type      Varchar2(20):= 'PAYMENT CREATED';

lv_check_id             Number;

lv_doc_seq_val          Number;

lv_doc_seq_id           Number ;

lv_doc_seq_name         varchar2(100);

lv varchar2(50);

lv_doc_category Varchar2(240);

Lv_Str varchar2(60);

lv_sql varchar2(60);

lv_chk_rec_exists number;

lv_month number;

lv_year number;

lv_year_next number;

lv_from_date date;

lv_to_date date;

 

------------*prc :Used to generate payments-------------------

 

Cursor cur_voucher Is

SELECT phdr.LEGAL_ENTITY_ID

     ,(select SUM (amount) from xxfah_pay_adv_lines

        where PAY_ADV_NO =phdr.PAY_ADV_NO and org_id= phdr.ORG_ID ) total_amount

     , phdr.bank_acct_use_id, phdr.bank_account_id,cb.BANK_ACCOUNT_NAME,cb.BANK_ACCOUNT_NUM,cb.BANK_ACCOUNT_TYPE

     , phdr.payment_date,phdr.PAYMENT_METHOD,phdr.payment_profile_id---,phdr.PAYMENT_TYPE_FLAG

     , phdr.PAYMENT_DOCUMENT_ID

     , phdr.currency_code, phdr.cheque_num

     , phdr.created_by, phdr.creation_date,phdr.last_update_date, phdr.last_updated_by

     , ap.PARTY_ID,aps.PARTY_SITE_ID,ap.VENDOR_NAME,aps.VENDOR_SITE_CODE,phdr.VENDOR_ID,phdr.VENDOR_SITE_ID,

       phdr.PAY_ADV_NO,phdr.VOUCHER_NO

  FROM xxfah_pay_adv_hdr phdr

     , ap_suppliers ap

     , ap_supplier_sites_ALL aps

     , ce_bank_accounts cb

 WHERE phdr.pay_adv_no = p_pay_adv_no

   AND phdr.voucher_id = p_voucher_id

   AND phdr.org_id     = p_org_id

   AND phdr.VENDOR_ID=ap.VENDOR_ID

   AND phdr.VENDOR_SITE_ID=aps.VENDOR_SITE_ID

   AND ap.VENDOR_ID=aps.VENDOR_ID

   AND phdr.BANK_ACCOUNT_ID=cb.BANK_ACCOUNT_ID;  

   

Cursor Cur_invoice Is

SELECT ppvl.invoice_id, aia.invoice_type_lookup_code, ppvl.payment_num,

       pdtl.amount, aia.set_of_books_id, accts_pay_code_combination_id,

       pdtl.last_update_date, pdtl.last_updated_by, pdtl.created_by,

       pdtl.creation_date

  FROM xxfah_pay_adv_lines pdtl,

       ap_invoices_all aia,

       pwcss_payment_voucher_lines ppvl

 WHERE pdtl.pay_adv_no = P_PAY_ADV_NO

   AND pdtl.voucher_line_id = ppvl.voucher_line_id

   AND aia.invoice_id = ppvl.invoice_id

 Order by aia.INVOICE_ID;

     

    

Begin

   BEGIN

    mo_global.set_policy_context ('S', p_org_id);

   END;

  

  Lv_Month := To_Char(Sysdate,'MM');

  If Lv_Month In ('01','02','03') Then --- Following codes check the current Financial Year....Kalyan Mitra...06-Aug-2013....

     Select To_Char(Sysdate,'YY')-1 Into Lv_Year From Dual;

  Else

     Select To_Char(Sysdate,'YY') Into Lv_Year From Dual;

  End If;   

 

  Lv_Year_Next := Lv_Year+1;

  Lv_From_Date := To_Char('01-Apr-'||Lv_Year)     ;

  Lv_To_Date   := To_Char('31-Mar-'||Lv_Year_Next);      

 

  P_MSG := 'Inserting';

  /* Begin

   select PAY_ADV_NO into lv

   from xxfah_pay_adv_hdr a

   where a.PAY_ADV_NO=1;

   Exception When Others then

     P_MSG := 'Data Not Found'||SQLERRM;

   APP_EXCEPTION.RAISE_EXCEPTION;      

   End;

   */

 

select FND_DOC_SEQ_1237_S.nextval into lv_doc_seq_val from dual;

 

 

  For i in Cur_Voucher Loop  

      

      Select count(attribute9) into lv_chk_rec_exists

      from ap_checks_all

      where trim(attribute9) =i.pay_adv_no

      and check_number=i.cheque_num;        

       

      If lv_chk_rec_exists > 0 then

       fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

       fnd_message.set_token ('MESSAGE', 'Record already exists');

       fnd_message.raise_error;

      End If;

 

        SELECT cep.payment_doc_category

          INTO lv_doc_category

          FROM ce_payment_documents cep

         WHERE payment_document_id = i.payment_document_id;

        

       --added on 010114 faheiz---------fetching doc dequence id against doc category------

       ---application 200 is for payables-----------------

        select x.doc_sequence_id into lv_doc_seq_id 

        from (

        SELECT doc_sequence_id     --fetching autmatic type sequence id

          FROM fnd_doc_sequence_assignments

         WHERE category_code = lv_doc_category

           AND start_date >= Lv_From_Date

           AND end_date <= Lv_To_Date

           AND method_code = 'A'

           AND application_id = 200

        UNION

        SELECT doc_sequence_id  --if autmatic not found then fetch method type null-----

          FROM fnd_doc_sequence_assignments

         WHERE category_code = lv_doc_category

           AND start_date >= Lv_From_Date

           AND end_date <=   Lv_To_Date

           AND method_code IS NULL

           AND application_id = 200) x;

        

        --fetching doc seq name against doc seq id-------  

        select DB_SEQUENCE_NAME into lv_doc_seq_name

        from fnd_document_sequences

        where    DOC_SEQUENCE_ID=lv_doc_seq_id;

       

        --generating seq value------

        Lv_Str := lv_doc_seq_name||'.nextval';       

        Execute immediate 'Select '|| Lv_Str || '  from dual ' into lv_doc_seq_val ;

       

      ------------calling ap_checks_pkg.insert_row PKG to insert Records in the AP_Checks_All-------------------------     

        P_MSG := 'Insert into ap_checks';

        ap_checks_pkg.insert_row (

                       X_Rowid               =>           v_rowid , 

                       X_Amount              =>           i.total_amount,

                       X_Ce_Bank_Acct_Use_Id =>           i.bank_acct_use_id,             

                       X_Bank_Account_Name   =>           i.BANK_ACCOUNT_NAME,

                       X_Check_Date          =>           i.payment_date,          

                       X_Check_Id            =>           ap_checks_s.NEXTVAL ,     

                       X_Check_Number        =>           i.cheque_num,       

                       X_Currency_Code       =>           i.currency_code,        

                       X_Last_Updated_By     =>           i.last_updated_by,     

                       X_Last_Update_Date    =>           i.last_update_date,

                       X_Payment_Type_Flag   =>          'Q',      ---Q = Quick Payment    

                       X_Address_Line1       =>           null, 

                       X_Address_Line2       =>           null,      

                       X_Address_Line3       =>           null,     

                       X_Checkrun_Name       =>           'Quick Payment: ID='||ap_checks_s.CURRVAL,            

                       X_Check_Format_Id     =>           null,       

                       X_Check_Stock_Id      =>           null,        

                       X_City                =>           null,    

                       X_Country             =>           null,      

                       X_Created_By          =>           i.Created_By,       

                       X_Creation_Date       =>           i.Creation_Date,   

                       X_Last_Update_Login   =>           FND_GLOBAL.USER_ID, 

                       X_Status_Lookup_Code  =>           'NEGOTIABLE', 

                       X_Vendor_Name         =>           i.Vendor_Name,

                       X_Vendor_Site_Code    =>           i.Vendor_Site_Code ,       

                       X_External_Bank_Account_Id =>      null,              

                       X_Zip                 =>           null,             

                       X_Bank_Account_Num    =>           i.BANK_ACCOUNT_NUM,       

                       X_Bank_Account_Type   =>           i.BANK_ACCOUNT_TYPE,          

                       X_Bank_Num            =>           null,           

                       X_Check_Voucher_Num   =>           null,           

                       X_Cleared_Amount      =>           null,         

                       X_Cleared_Date        =>           null,             

                       X_Doc_Category_Code   =>           lv_doc_category,--- 'AXIS BANK - 910020035884475',     

                       X_Doc_Sequence_Id     =>           lv_doc_seq_id,                     

                       X_Doc_Sequence_Value  =>           lv_doc_seq_val, 

                       X_Province            =>           null,

                       X_Released_Date       =>           null,

                       X_Released_By         =>           null,

                       X_State               =>           null,

                       X_Stopped_Date        =>           null,

                       X_Stopped_By          =>           null,

                       X_Void_Date           =>           null,

                       X_Attribute1          =>           null,

                       X_Attribute10         =>           null,

                       X_Attribute11         =>           null,

                       X_Attribute12         =>           null,

                       X_Attribute13         =>           null,

                       X_Attribute14         =>           null,

                       X_Attribute15         =>           null,

                       X_Attribute2          =>           null,

                       X_Attribute3          =>           i.VOUCHER_NO,

                       X_Attribute4          =>           null,

                       X_Attribute5          =>           null,

                       X_Attribute6          =>           null,

                       X_Attribute7          =>           null,

                       X_Attribute8          =>           null,

                       X_Attribute9          =>           i.PAY_ADV_NO,

                       X_Attribute_Category  =>           null,

                       X_Future_Pay_Due_Date =>           null,

                       X_Treasury_Pay_Date   =>           null,

                       X_Treasury_Pay_Number =>           null,

                       X_Withholding_Status_Lkup_Code =>  null, --'i' ,

                       X_Reconciliation_Batch_Id      =>  null,

                       X_Cleared_Base_Amount          =>  null,

                       X_Cleared_Exchange_Rate        =>  null,

                       X_Cleared_Exchange_Date        =>  null,

                       X_Cleared_Exchange_Rate_Type   =>  null,

                       X_Address_Line4                =>  null,

                       X_County                       =>  null,

                       X_Address_Style                =>  null,

                       X_Org_Id                       =>  p_org_id,

                       X_Vendor_Id                    =>  i.Vendor_Id,

                       X_Vendor_Site_Id               =>  i.Vendor_Site_Id,

                       X_Exchange_Rate                =>  null,

                       X_Exchange_Date                =>  null,

                       X_Exchange_Rate_Type           =>  null,

                       X_Base_Amount                  =>  null,

                       X_Checkrun_Id                  =>  null,

                       X_global_attribute_category    =>  null,

                       X_global_attribute1            =>  null,

                       X_global_attribute2            =>  null,

                       X_global_attribute3            =>  null,

                       X_global_attribute4            =>  null,

                       X_global_attribute5            =>  null, 

                       X_global_attribute6            =>  null, 

                       X_global_attribute7            =>  null,

                       X_global_attribute8            =>  null, 

                       X_global_attribute9            =>  null, 

                       X_global_attribute10           =>  null,

                       X_global_attribute11           =>  null, 

                       X_global_attribute12           =>  null,

                       X_global_attribute13           =>  null, 

                       X_global_attribute14           =>  null, 

                       X_global_attribute15           =>  null,

                       X_global_attribute16           =>  null,

                       X_global_attribute17           =>  null, 

                       X_global_attribute18           =>  null, 

                       X_global_attribute19           =>  null, 

                       X_global_attribute20           =>  null,

                       X_transfer_priority            =>  null, 

                       X_maturity_exchange_rate_type  =>  null, 

                       X_maturity_exchange_date       =>  null, 

                       X_maturity_exchange_rate       =>  null, 

                       X_description                  =>  null, 

                       X_anticipated_value_date       =>  null, 

                       X_actual_value_date            =>  null, 

                       x_payment_method_code          =>  i.PAYMENT_METHOD,

                       x_payment_profile_id           =>  i.payment_profile_id, 

                       x_bank_charge_bearer           =>  null, 

                       x_settlement_priority          =>  null, 

                       x_payment_document_id          =>  i.PAYMENT_DOCUMENT_ID, 

                       x_party_id                     =>  i.party_id, 

                       x_party_site_id                =>  i.party_site_id , 

                       x_legal_entity_id              =>  23273, --i.legal_entity_id, 

                       x_payment_id                   =>  IBY_PAYMENTS_ALL_S.nextval , 

                       X_calling_sequence             =>  'APXPAWKB', 

                       X_Remit_To_Supplier_Name       =>  null, 

                       X_Remit_To_Supplier_Id         =>  null, 

                       X_Remit_To_Supplier_Site       =>  null,

                       X_Remit_To_Supplier_Site_Id    =>  null,

                       X_Relationship_Id              =>  null, 

                       X_paycard_authorization_number =>  null,

                       X_paycard_reference_id         =>  null

                     );

                    

      P_MSG := 'Insert into ap_payment_history';

      P_MSG := FND_MESSAGE.GET;          

     

        

      AP_RECONCILIATION_PKG.insert_payment_history(

                      X_CHECK_ID                =>   ap_checks_s.CURRVAL,

                      X_TRANSACTION_TYPE        =>   l_transaction_type,

                      X_ACCOUNTING_DATE         =>   I.PAYMENT_DATE,

                      X_TRX_BANK_AMOUNT         =>   NULL  ,

                      X_ERRORS_BANK_AMOUNT      =>   NULL  ,

                      X_CHARGES_BANK_AMOUNT     =>   NULL  ,

                      X_BANK_CURRENCY_CODE      =>   NULL  ,

                      X_BANK_TO_BASE_XRATE_TYPE =>   NULL  ,

                      X_BANK_TO_BASE_XRATE_DATE =>   NULL  ,

                      X_BANK_TO_BASE_XRATE      =>   NULL  ,

                      X_TRX_PMT_AMOUNT          =>   i.total_amount,

                      X_ERRORS_PMT_AMOUNT       =>   NULL  ,

                      X_CHARGES_PMT_AMOUNT      =>   NULL  ,

                      X_PMT_CURRENCY_CODE       =>   i.currency_code,

                      X_PMT_TO_BASE_XRATE_TYPE  =>   NULL  ,

                      X_PMT_TO_BASE_XRATE_DATE  =>   NULL  ,

                      X_PMT_TO_BASE_XRATE       =>   NULL  ,

                      X_TRX_BASE_AMOUNT         =>   NULL  ,

                      X_ERRORS_BASE_AMOUNT      =>   NULL  ,

                      X_CHARGES_BASE_AMOUNT     =>   NULL  ,

                      X_MATCHED_FLAG            =>   NULL  ,

                      X_REV_PMT_HIST_ID         =>   NULL  ,

                      X_ORG_ID                  =>   P_ORG_ID ,

                      X_CREATION_DATE           =>   i.CREATION_DATE,

                      X_CREATED_BY              =>   i.CREATED_BY ,

                      X_LAST_UPDATE_DATE        =>   i.LAST_UPDATE_DATE,

                      X_LAST_UPDATED_BY         =>   i.LAST_UPDATED_BY,

                      X_LAST_UPDATE_LOGIN       =>   FND_GLOBAL.USER_ID ,

                      X_PROGRAM_UPDATE_DATE     =>   NULL  ,

                      X_PROGRAM_APPLICATION_ID  =>   NULL  ,

                      X_PROGRAM_ID              =>   NULL  ,

                      X_REQUEST_ID              =>   NULL  ,

                      X_CALLING_SEQUENCE        =>   'APXPAWKB (pay_sum_folder_pkg_i.insert_row)',

                      X_ACCOUNTING_EVENT_ID     =>   NULL  ,

                      x_invoice_adjustment_event_id=>NULL

                    );

 

    lv_check_id := ap_checks_s.CURRVAL; --assigning curent check id value to local variable

   

    BEGIN   ---fetching acount event id

       SELECT accounting_event_id

         INTO lv_accounting_event_id

         FROM ap_payment_history_all

        WHERE check_id =lv_check_id

          AND transaction_type = l_transaction_type;

    EXCEPTION

       WHEN NO_DATA_FOUND

       THEN

         lv_accounting_event_id := Null;

          P_MSG := 'Accounting Event id not created'||SQLERRM;  

          APP_EXCEPTION.RAISE_EXCEPTION;      

       WHEN OTHERS

       THEN

         lv_accounting_event_id := Null;

         P_MSG := 'Accounting Event id not created' ||SQLERRM;

    END;

   

    BEGIN     ---Fetching Period name--------

       SELECT period_name

         INTO lv_period_name

         FROM gl_period_statuses glp, ap_system_parameters_all aps

        WHERE application_id = 200

          AND i.payment_date BETWEEN start_date AND end_date

          AND glp.set_of_books_id = aps.set_of_books_id

          AND closing_status = 'O'

          AND NVL (adjustment_period_flag, 'N') = 'N'

          AND aps.org_id = p_org_id;

    EXCEPTION

       WHEN OTHERS

       THEN

          lv_period_name := null;

          P_MSG := 'Error Fetching Period Name' ||SQLERRM;

    END;              

                     

     For j in Cur_invoice Loop

       P_MSG := 'Insert into ap_invoice_payments';

       P_MSG := FND_MESSAGE.GET;                  

       ap_pay_invoice_pkg.ap_pay_invoice(

                    P_invoice_id                =>   j.invoice_id ,

                    P_check_id                  =>   ap_checks_s.CURRVAL ,  

                    P_payment_num               =>   j.payment_num     ,

                    P_invoice_payment_id        =>   ap_invoice_payments_s.nextval ,

                    P_old_invoice_payment_id    =>   NULL        ,

                    P_period_name               =>   lv_period_name , 

                    P_invoice_type              =>   j.invoice_type_lookup_code  , 

                    P_accounting_date           =>   i.payment_date    ,

                    P_amount                    =>   j.amount ,

                    P_discount_taken            =>   0     ,

                    P_discount_lost             =>   NULL  ,

                    P_invoice_base_amount       =>   NULL  ,

                    P_payment_base_amount       =>   NULL  ,

                    P_accrual_posted_flag       =>   'N'   ,

                    P_cash_posted_flag          =>   'N'   ,

                    P_posted_flag               =>   'N'   ,

                    P_set_of_books_id           =>   j.set_of_books_id  ,

                    P_last_updated_by           =>   j.last_updated_by ,

                    P_last_update_login         =>   FND_GLOBAL.USER_ID ,

                    P_currency_code             =>   i.currency_code   ,

                    P_base_currency_code        =>    NULL, 

                    P_exchange_rate             =>    NULL   ,

                    P_exchange_rate_type        =>    NULL ,

                    P_exchange_date             =>    NULL  ,

                    P_ce_bank_acct_use_id       =>    i.bank_acct_use_id  ,

                    P_bank_account_num          =>    i.bank_account_num  ,

                    P_bank_account_type         =>    i.bank_account_type    ,

                    P_bank_num                  =>    NULL  ,

                    P_future_pay_posted_flag    =>    NULL  ,

                    P_exclusive_payment_flag    =>    NULL  ,

                    P_accts_pay_ccid            =>    j.accts_pay_code_combination_id  ,

                    P_gain_ccid                 =>    NULL  ,

                    P_loss_ccid                 =>    NULL  ,

                    P_future_pay_ccid           =>    NULL  ,

                    P_asset_ccid                =>    NULL  ,

                    P_payment_dists_flag        =>    'N'  ,

                    P_payment_mode              =>   'PAY'  ,

                    P_replace_flag              =>    'N'  ,

                    P_attribute1                =>    NULL    ,

                    P_attribute2                =>    NULL    ,

                    P_attribute3                =>    NULL    ,

                    P_attribute4                =>    NULL    ,

                    P_attribute5                =>    NULL    ,

                    P_attribute6                =>    NULL    ,

                    P_attribute7                =>    NULL    ,

                    P_attribute8                =>    NULL    ,

                    P_attribute9                =>    NULL    ,

                    P_attribute10               =>    NULL    ,

                    P_attribute11               =>    NULL    ,

                    P_attribute12               =>    NULL    ,

                    P_attribute13               =>    NULL    ,

                    P_attribute14               =>    NULL    ,

                    P_attribute15               =>    NULL    ,

                    P_attribute_category        =>    NULL    ,

                    P_global_attribute1         =>    NULL    ,

                    P_global_attribute2         =>    NULL    ,

                    P_global_attribute3         =>    NULL    ,

                    P_global_attribute4         =>    NULL    ,

                    P_global_attribute5         =>    NULL    ,

                    P_global_attribute6         =>    NULL    ,

                    P_global_attribute7         =>    NULL    ,

                    P_global_attribute8         =>    NULL    ,

                    P_global_attribute9         =>    NULL    ,

                    P_global_attribute10        =>    NULL    ,

                    P_global_attribute11        =>    NULL    ,

                    P_global_attribute12        =>    NULL    ,

                    P_global_attribute13        =>    NULL    ,

                    P_global_attribute14        =>    NULL    ,

                    P_global_attribute15        =>    NULL    ,

                    P_global_attribute16        =>    NULL    ,

                    P_global_attribute17        =>    NULL    ,

                    P_global_attribute18        =>    NULL    ,

                    P_global_attribute19        =>    NULL    ,

                    P_global_attribute20        =>    NULL    ,

                    P_global_attribute_category =>    NULL    ,   

                    P_calling_sequence          =>   'Pay Invoice Forms <- Pre_inser trigger' ,

                    P_accounting_event_id       =>   lv_accounting_event_id   ,

                    P_org_id                    =>   p_org_id  );    

     End Loop;                                                                  

  End Loop;

 

End;

/

 


FUSION: Purchasing Query

  1) ---------CPA to PO Amount---------- select poh.segment1, (select sum(nvl(cpol.quantity,1)* cpol.unit_price) from po_headers_all cpoh,po...