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

 

OAF : IExpense Page Controller Extension Personalization setup

Page: GeneralInformationPG

Customization List

a)      Employee Grade added along with Employee Name

b)     NoteTOApprover Field:- Made Read-Only 

Personalization Setup

1)         Controller Class :- HeaderCO

Navigation:-

a)      Click on Personalize link from the Page -> Select “Complete view” in Personalization Structure-> Click on Personalize “pencil” as shown below

 

a)      Enter the Extended Controller Name at site level as shown below and click on Apply Button

Controller Class:-

Original Definition :- oracle.apps.ap.oie.webui.HeaderCO

Site level :- oracle.apps.ap.oie.webui.XXExtHeaderCO

 

EBS :GL Query : Query to get Ledger details (Oracle)

 GL

-------Query to get GL Ledger details-------------

select name,currency_code,CHART_OF_ACCOUNTS_ID,

gl_flexfields_pkg.get_coa_name(CHART_OF_ACCOUNTS_ID) COA

from gl_ledgers where name in ('XXG18_500_USD');

EBS: GL Encumbrance API

 GL Encumbrance API

gl_funds_available_pkg.calc_funds

                                                     ('YTDE',

                                                      ---X_AMOUNT_TYPE /*YTDE,QTDE , PTD, PJTD*/

                                                      i.ccid,

                                                      NULL,

                                                      -- 'A',--x_account_type,

                                                      NULL,       --x_template_id,

                                                      l_ledger_id,

                                                      l_currency_code,

                                                      'Y',    --x_po_install_flag,

                                                      l_accounted_period_type,

                                                      l_period_set_name,

                                                      l_period_name,

                                                      l_period_num,

                                                      l_quarter_num,

                                                      l_period_year,

                                                      l_closing_status,

                                                      l_budget_version_id,

                                                      l_encumbrance_type_id,

                                                      NULL,

                                                           --x_req_encumbrance_id,

                                                      NULL, --x_po_encumbrance_id,

                                                      x_budget,

                                                      x_encumbrance,

                                                      x_actual,

                                                      x_funds_available,

                                                      x_req_encumbrance_amount,

                                                      x_po_encumbrance_amount,

                                                      x_other_encumbrance_amount

                                                     );

EBS: GL Encumbrance Query

Query to get GL Encumbrance Details for Source REQ, PO and Invoice

SELECT   '1REQ' TYPE, prh.requisition_header_id header_id,

                            prh.segment1 num, prd.budget_account_id ccid,

                            TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),

                            prd.last_update_date last_update_date,

                            SUM (NVL (prd.encumbered_amount, 0)) amount,prh.authorization_status status

                       FROM po_requisition_headers_all prh,

                            po_requisition_lines_all prl,

                            po_req_distributions_all prd

                      WHERE prh.requisition_header_id =prl.requisition_header_id

                        AND prl.requisition_line_id = prd.requisition_line_id

                        AND prh.authorization_status  in ('APPROVED','IN PROCESS')

                        AND NVL (prh.cancel_flag, 'N') = 'N'

                       ---- AND prd.budget_account_id = v_code_combination_id 

                        AND prd.budget_account_id IN (SELECT detail_code_combination_id

                                                      FROM gl_account_hierarchies

                                                     WHERE summary_code_combination_id = v_code_combination_id

                                                       AND ledger_id = 2041

                                                      UNION

                                                      SELECT v_code_combination_id FROM DUAL)

                        AND prd.org_id = v_org_id

                   GROUP BY prh.requisition_header_id,

                            prh.segment1,

                            prd.budget_account_id,

                            TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),

                            prd.last_update_date,prh.authorization_status

                   UNION ALL

                   SELECT   '2PO' TYPE, poh.po_header_id header_id,

                            poh.segment1 num, pda.budget_account_id,

                            TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),

                            pda.last_update_date last_update_date,

                            SUM (NVL (pda.encumbered_amount, 0)) amount,poh.authorization_status status

                       FROM po_headers_all poh, po_distributions_all pda

                      WHERE poh.po_header_id = pda.po_header_id

                        AND pda.org_id = v_org_id

                        AND poh.authorization_status  in ('APPROVED','IN PROCESS')

                        AND NVL (poh.cancel_flag, 'N') = 'N'

                       ----- AND pda.budget_account_id = v_code_combination_id  

                        AND pda.budget_account_id IN (SELECT detail_code_combination_id

                                                              FROM gl_account_hierarchies

                                                             WHERE summary_code_combination_id = v_code_combination_id

                                                               AND ledger_id = 2041

                                                              UNION

                                                            SELECT v_code_combination_id FROM DUAL)

                        AND NOT EXISTS ( --added to exclude the PO match Inovice --

                                        SELECT 1

                                          FROM ap_invoices_all aia, ap_invoice_lines_all apia

                                         WHERE aia.invoice_id = apia.invoice_id

                                           AND aia.org_id = apia.org_id

                                           AND apia.po_header_id = pda.po_header_id

                                           AND apia.org_id = pda.org_id

                                           AND ap_invoices_utility_pkg.get_approval_status

                                                                             (aia.invoice_id,

                                                                              aia.invoice_amount,

                                                                              aia.payment_status_flag,

                                                                              aia.invoice_type_lookup_code

                                                                             ) = 'APPROVED'

                                         union --added on 31mar17 to exclude po prepay invoice -- 

                                         SELECT 1

                                            FROM ap_invoices_all aia

                                            where aia.quick_po_header_id=pda.po_header_id  

                                            and aia.org_id=pda.org_id                                        

                                         )

                   GROUP BY poh.po_header_id,

                            poh.segment1,

                            pda.budget_account_id,

                            TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),

                            pda.last_update_date,poh.authorization_status

                   UNION ALL

                   SELECT   '3INV' TYPE, aia.invoice_id header_id,

                            aia.invoice_num num,

                            aid.dist_code_combination_id ccid,

                            TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),

                            aid.last_update_date last_update_date,

                            round(SUM(NVL ((aid.amount*nvl(aia.EXCHANGE_RATE,1)), 0)),2) amount,

                        'APPROVED' status

                       FROM ap_invoices_all aia,

                            ap_invoice_distributions_all aid

                      WHERE aia.invoice_id = aid.invoice_id

                        AND aia.org_id = aid.org_id

                        ---AND aid.dist_code_combination_id = v_code_combination_id

                        AND aid.dist_code_combination_id IN (SELECT detail_code_combination_id

                                                              FROM gl_account_hierarchies

                                                             WHERE summary_code_combination_id = v_code_combination_id

                                                               AND ledger_id = 2041

                                                              UNION

                                                             SELECT v_code_combination_id FROM DUAL)

                        AND aia.org_id = v_org_id

                        AND aia.cancelled_date IS NULL

                        and aid.invoice_id not in (  504841)------added on 29AUG16. For Special Case Journal has been made against invoice---

                        AND apps.ap_invoices_pkg.get_approval_status

                                                 (aia.invoice_id,

                                                  aia.invoice_amount,

                                                  aia.payment_status_flag,

                                                  aia.invoice_type_lookup_code

                                                 ) IN ('APPROVED', 'FULL', 'UNPAID','AVAILABLE')

                   GROUP BY aia.invoice_id,

                            aia.invoice_num,

                            aid.dist_code_combination_id,

                            TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),

                            aid.last_update_date,

                            aia.invoice_amount,

                            aia.payment_status_flag,

                            aia.invoice_type_lookup_code) main

            WHERE 1 = 1

              --AND main.header_id NOT IN v_header_id

              AND TO_CHAR (last_update_date, 'YYYYMMDDHH24MISS') <=v_last_date

              group by main.status

EBS: PA: AP to PA integration Technical Functional Flow (Oracle)

 AP to PA Integration Technical Functional Flow

1) Enter AP Invoice Distributions.

2) Run : Supplier Cost Interface - This will pick the eligible AP Inv Dist , it check PA_ADDITION column. This insert data into pa_transactions_interface.

3) Run : PRC Transaction Imports :- This will pick data from pa_transactions_interface and insert into Expenditure base table. 

4) In Expenditure Table we have 3 types of Invoice Source:

a) AP INVOICE

b) AP ERV

c) AP DISCOUNT

Link between PA Expenditure and AP Invoice

pa_expenditure_item.document_header_id = Invoice_id

pa_expenditure_item.document_distribution_id = invoice_distribution_id 

Main Table of PA Expenditure:-

PA_EXPENDITURE_ALL

PA_EXPENDITURE_ITEMS_ALL