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

EBS : PA Invoice Billing Steps

PA Invoice Billing Steps:-

1)Go to Billing-> Events-> Create Invoice Events

2) Run PRC: Generate Draft Invoice for Single Project. This will generate Draft Invoice

3) Go to Billling-> Invoice review > Enter project and search.

4) Approved and release Draft Invoice.

5) Run  PRC: Interface to Receivables - This will populate data into RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL to create AR INVOICE.

6) Run "Auto Import Master Program" - This will pick the interface table and create AR Invoice. AR Invoice will be created with Batch Source as "PROJECT INVOICES". Project Number gets stored in column INTERFACE_HEADER_ATTRIBUTE1 or INTERFACE_LINE_ATTRIBUTE1

OAF : How to find DBC File ( Oracle Apps)

 How to locate correct DBC file

 Navigate to System Administrator -> Concurrent -> Requests, Click on "Submit a New Request" and submit 

   "Generate concurrent processing environment information" CP and let it complete. 

   Click on "View Output" button and locate "FND_SECURE" environment variable to find dbc file.

   DBC file can be found in $FND_SECURE directory or $FND_SECURE/<SID> directory with name <SID>.dbc

--------------------------------------------------------------------------------------------------------------------------------

How to get the DBC file for a particular instance

#Oracle Apps #OAF