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;

/

 

Oracle PLSQL : How to resolve package state in invalid state

How to resolve PLSQL package state in invalid state

Error in Workflow APINL/125-806353 ORA-04061: existing state of  has been invalidated

ORA-04061: existing state of package body "APPS.APINV_NO_WF_PKG" has been invalidated

ORA-04065: not executed, altered or dropped package body "    

Solution :-  Go to v$Session  and find your object and kill the session

HOW TO ADD MICROSOFT LOOP ADAPTER in Windows OS

 HOW TO ADD MICROSOFT LOOP ADAPTER in Windows

1)Go to run and type ‘hdwwiz.exe’

2) 

1)   

EBS: How to find XDO Config File in Server (XML Bursting)

XML Bursting

How to find XDO Config file in server

1. Go to below directory

                 /erptest/erpapp/fs1/EBSapps/appl/xdo/12.0.0/resource/xdodelivery.cfg

  

We can define XML Bursting Property in this file. Example SFTP Configuration.

Oracle : How to connect SFTP using cmd, unix mode

How to connect SFTP using cmd, unix mode

CMD Mode

1) Go to run and type "CMD" and click on OK

2)Type below command and then entered password.

sftp ebs_es1@ftp.alam.corp.net

EBS : Payables : List of 1099 (tax) Reports in oracle

 List of 1099 (tax) Reports in oracle

• 1099 Forms
• Update Income Tax Details Utility
• 1099 Payments Report
• 1099 Supplier Exceptions Report
• 1099 Invoice Exceptions Report

Oracle : How to move file from one directory to another directory in PLSQL

 How to move file from one directory to another directory in PLSQL

procedure xx_move_file_to_archive_dir(p_file_name in varchar2)

is

begin

debug_msg_prc(chr(10)||'-----Start xx_move_file_to_archive_dir: To Move same name file to archive if exists.',g_log);

debug_msg_prc( 'Source Path:'||g_directory_path_out||',Target Path:'||g_directory_path_archive ||',FileName:'||p_file_name,g_log);

UTL_FILE.FRENAME (g_directory_path_out,--source

 p_file_name,

g_directory_path_archive,--target archive

p_file_name||'ar',--new archive file name

TRUE);

debug_msg_prc('FileName:'||p_file_name ||' move to archive directory',g_log);

exception when others then

debug_msg_prc('Error xx_move_file_to_archive_dir.Seems File not available to move. '||substr(SQLERRM,1,200),g_log);

end xx_move_file_to_archive_dir;

EBS Query : Customer: Query to get Customer Account Level and Site Level Collector

 Query to get Customer Account Level and Site Level Collector

select  

       (select name from hr_operating_units where organization_id = hcas.org_id )  Operating_unit   ,

       hca.account_number customer_number, 
       hca.account_name customer_name,
       hcsu.location site_number,
      (select name from ar_collectors where collector_id = hcp.collector_id) account_level_collector ,

      (select name from ar_collectors where collector_id = hcp_site.collector_id) site_level_collector ,

       hl.country, 
       hl.address1, 
       hl.address2,

       hl.city ,
       hl.postal_code ,

       hl.state ,

       (select territory_short_name  from FND_TERRITORIES_tl where territory_code =  hl.country  and language = 'US' ) country 

       --hcsu.site_use_code ,     
        --  , hca.cust_account_id , hcsu.site_use_id, hcp.status, hcp_site.status
  from hz_cust_accounts_all hca, 

       hz_cust_acct_sites_all hcas, 

       hz_cust_site_uses_all hcsu, 
       hz_party_sites hps, 
       hz_locations hl, 
       hz_customer_profiles hcp, 
       hz_customer_profiles hcp_site

where hca.cust_account_id = hcas.cust_account_id

   and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
   and hcas.party_site_id = hps.party_site_id
   and hps.location_id = hl.location_id
   and hca.cust_account_id = hcp.cust_account_id
   and hcp.site_use_id is null 
   and hca.cust_account_id = hcp_site.cust_account_id
   and hcsu.site_use_id = nvl(hcp_site.site_use_id,0)
   and hca.status = 'A'
   and hcas.status = 'A'

   and hcsu.status = 'A'

   and hcsu.site_use_code = 'BILL_TO'
   and hcas.org_id in ('971')
   --and hca.account_number = '33333333'
order by hca.account_number ,
         hcsu.location

EBS : AP Payment Useful Query

Query to get Process Payment Request count having multiple Currency

select PAYMENT_PROCESS_REQUEST_NAME,COUNT(PAYMENT_CURRENCY_CODE)

from (

select PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE

from apps.iby_paymentS_all

--where PAYMENT_PROCESS_REQUEST_NAME='FAITRAD'

group by PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE

) xx

group by  PAYMENT_PROCESS_REQUEST_NAME--, PAYMENT_CURRENCY_CODE

having count(PAYMENT_PROCESS_REQUEST_NAME) > 1

order by 1

Query to get Payment Details 

select PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,     

        BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY

from (       

Select aba.Bank_Account_Num Bank_Account_Num, aba.BANK_ACCOUNT_NAME Bank_account_name,aba.bank_account_id bank_account_id,

abb.BANK_BRANCH_NAME,

(select bank_name from ce_banks_v where PK_ID =aba.bank_id) bank_name,

(select name from hr_operating_units where organization_id = ac.org_id ) ou_name,

(select NLS_TERRITORY from fnd_territories where TERRITORY_CODE = ac.COUNTRY) payee_country,

ac.org_id,ac.PAYMENT_METHOD_CODE,  ac.CURRENCY_CODE,ac.COUNTRY,

(select ispp.SYSTEM_PROFILE_NAME from iby_acct_pmt_profiles_b ibyac,IBY_SYS_PMT_PROFILES_VL ispp

where PAYMENT_PROFILE_ID =ac.PAYMENT_PROFILE_ID

and ibyac.SYSTEM_PROFILE_CODE=ispp.SYSTEM_PROFILE_CODE) payment_process_profile,ac.check_number

From AP_CHECKS_ALL ac,

     CE_PAYMENT_DOCUMENTS acs,

     CE_BANK_ACCOUNTS aba,

     CE_BANK_BRANCHES_V abb

WHERE 1 = 1

--and ac.PAYMENT_METHOD_CODE = 'CHECK'

and ac.STATUS_LOOKUP_CODE = 'NEGOTIABLE'

AND ac.PAYMENT_DOCUMENT_ID = acs.PAYMENT_DOCUMENT_ID

AND acs.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id

And abb.BRANCH_PARTY_ID = aba.Bank_Branch_Id

And upper(ac.Status_Lookup_Code) = upper('NEGOTIABLE')

and ac.BANK_ACCOUNT_NAME ='BOA USD ALL'

and trunc(ac.creation_Date) >= '31-AUG-2014' 

) xx

group by PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,     

        BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY