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

OAF : Important Code

 How to retrieve Current row value

ü using Row Reference

           //Getting selected Emp Id-----------------------------------

            OAApplicationModule am= pageContext.getApplicationModule(webBean);

            OAViewObject vo3 = (OAViewObject)am.findViewObject("DataEntryVO1");

           //Get Row Refrence

            String rowReference =    pageContext.getParameter(EVENT_SOURCE_ROW_REFERENCE);          

          //Get current Row using Row Reference   

             OARow currRow = (OARow)am.findRowByRef(rowReference);                 

           //Get attribute value from current row

            String lv_emp_id = (String)currRow.getAttribute("EmpId").toString();

          

           throw new OAException(" Selected Emp id is : !!!"+lv_emp_id  ,OAException.CONFIRMATION);

           // end-----------------------------------

ü using VO.currentRecord

               vo3.last(); --retrieving last row       

              String lv_emp_id =vo3.currRow().getAttribute("EmpId").toString();

                    

ü  using OAMessageStyle Bean

        //CustomerNameTxt  

        OAMessageStyledTextBean CustomerNameTxt =

            (OAMessageStyledTextBean)webBean.findIndexedChildRecursive("CustomerNameTxt");

        System.out.println("CustomerNameTxt" +

                           CustomerNameTxt.getValue(pageContext));

How to Button/fields value using PageContext.getParameter

   (pageContext.getParameter("CreatePage")!=null)     

How to print Message

System.out.println("Process Request Starts****");

 pageContext.writeDiagnostics(this,  "Unable to get DeliveryId" + e.getMessage().toString(),  6);

How to use Link button 

Create an item  and  change the following property

 

Item Style	Link
Destination URI	Link of the calling page OA.jsp?page=/pk/oracle/apps/fnd/dataentry/server/EntryPG Passing Parameter OA.jsp?page=/xxmj/oracle/apps/ibe/customer/delivery/webui/CustDeliveryDetailsPG&retainAM=Y&DeliveryId={@DeliveryNumber}   OA.jsp?page=/xxmj/oracle/apps/ibe/customer/reports/webui/DeliveryReportPG&retainAM=Y&DeliveryId={@DeliveryNumber}&ProgramShortName={@DynamicUrl}&OrderNum={@OrderNumber}
Target Frame	_blank Note:- It will Open the Target Page in New Window Page

   

How to use ComparetoIgnore method

        if ("Order Confirmation".compareToIgnoreCase(pageContext.getParameter("ProgramShortName").toString()) ==

            0) {

            System.out.println("Order Confirmation is chsen to be run");

            //Run Order Confirmation Report

            getOutput(pageContext, webBean, "XXMOMRPT55555OAF");

        }

 

How to use Date Format and get Current date

                SimpleDateFormat format =

            new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");

        oracle.jbo.domain.Date convertDate =

            am3.getOADBTransaction().getCurrentDBDate();

        java.util.Date date = null;

        try {

            date = format.parse(convertDate.toString());

        } catch (ParseException e) {

            // TODO

        }

        SimpleDateFormat formatDate =

            new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        String convStr = formatDate.format(date);

        System.out.println(convStr);

 

        //2015/03/13 00:00:00

        SimpleDateFormat formatDate2 =

            new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");

        String currentDate = formatDate.format(date);

        System.out.println(currentDate);

OAF : Migration Scripts

 ----------------------------------------------Export command from unix---------------------------

java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/perimage/webui/PerImgViewPG -rootdir $JAVA_TOP -username apps -password apps -dbconnection "(description = (address_list= (address= (community = tcp.world)(protocol=tcp) (host=192.168.1.35) (port = 1523))) (connect_data=(sid=TEST)))"

----------------------------------------------Import command from unix----------------------------

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/pk/oracle/apps/fnd/insertdemo/webui/InsertPG.xml -username apps -password apps -dbconnection "(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.35) (PORT=1523)) (CONNECT_DATA=(SID=TEST)))" -rootdir $JAVA_TOP

-------------------------------------------------IMPORT COMMAND from desktop-----------

C:\A_OA\jdevhome\jdev\myprojects\pk\oracle\apps\fnd\insertdemo\webui\InsertPG.xml -rootdir C:\A_OA\jdevhome\jdev\myprojects -username apps -password apps -dbconnection "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.35)(PORT =1523)))(CONNECT_DATA = (SERVER = DEDICATED) (SID =TEST)))"

-----------------------------scripts to check file uploaded or not----------------------------

DECLARE

BEGIN

jdr_utils.printDocument('/pk/oracle/apps/fnd/insertdemo/webui/InsertPG',1000);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

SQLSERVER : How to Login SQLSERVER

 HOW TO LOGIN SQLSERVER

1) OPEN CMD

2) TYPE BELOW COMMAND:-

     mysql -u root -p

3) enter password  

(Note : if u dont know password pls reset it usin update commmand

update mysql.user set password ='new pasword1' 

where user='root';

)

4) if u dont know the password just change default int file..which located in BIN folder.

------------------------How to change password of root user---------

1)mysqladmin -u root -p password 

2) enter password      (Note :- skip and plz press enter)

3) new password        (Note :- plz enter your new password)

4) confirm password        (Note :- plz enter your confirm password)

EBS : HRMS : SQL Query

 HRMS SQL Query

---Employee Tab 

select * from per_all_people_f where EMPLOYEE_NUMBER ='12'

---Employee Assignment Tab

select * from per_assignments_f where person_id=35347

---Payroll Entries Tab

select * from pay_element_entries_f where ASSIGNMENT_ID= 3539 and ELEMENT_TYPE_ID=6523

select nvl(pet1.retro_summ_ele_id, pet1.element_type_id) from pay_element_types_f pet1 where pet1.element_type_id = 647;

Important Table

pay_process_events;

PAY_RETRO_ASSIGNMENTS 

PAY_RETRO_ENTRIES

Important Standard Program

pay_in_tax_utils.get_balance_value

PAY_RETRO_PKG

EBS : IExpense : How IEXPENSE page is registered in Oracle Apps

 How IEXPENSE page is registered in Oracle Apps

Function Register for “Expense Home”

OA.jsp?OAHP=OIE_EXPENSES_APPLICATION&OAFunc=OIEHOMEPAGE

    

EBS :Project Billing - Navigation Flow

 How to open Period

Expenditure Inquiry

Invoice Review Inquiry

Revenue Review Inquiry

EBS : OM Query (Sales Order)

Sales Order Query 

--Query to get quote number in Sales Order Table

select quote_number,flow_status_code,a.* from oe_order_headers_all a

where 1=1 and order_number=4800785

ORDER BY CREATION_DATE DESC

--Line Table

select flow_status_code, a.LINE_CATEGORY_CODE, a.* from oe_order_lines_all a

where header_id=42137275

--Delivery Detail Table

select a.RELEASED_STATUS,a.* from wsh_delivery_details a

where source_header_id=213727

--Delivery assignment Table

select * from wsh_delivery_assignments

where DELIVERY_DETAIL_ID =43978725

--Delivery Table

select * from wsh_new_deliveries

where source_header_id=42137275

--MTL Demand Table

select * from mtl_demand

where DEMAND_SOURCE_line_ID=418307

--Reservation Tab

select * from mtl_reservations

where DEMAND_SOURCE_line_ID =541830743 

--Query toget Order WF Details

SELECT   h.header_id, l.line_id, l.org_id,h.order_number

    FROM oe_order_headers_all h,

         oe_order_lines_all x,

         wf_item_activity_statuses wias,

         wf_process_activities wpa

   WHERE h.header_id = l.header_id

     AND h.org_id = l.org_id

     AND NVL (h.transaction_phase_code, 'F') = 'F'

     AND h.open_flag = 'Y'

     AND l.open_flag = 'Y'     

     AND l.line_category_code <> 'RETURN'    

    AND l.item_type_code <> 'SERVICE'

     AND l.source_type_code <> 'EXTERNAL'

     AND h.order_number >= :order_number_low

     AND h.order_number <= :order_number_high     

     AND wias.item_type = 'OEOL'

     AND wias.process_activity = wpa.instance_id     

     AND wpa.activity_item_type = 'OEOL'     

     AND wpa.activity_name = 'SCHEDULING_ELIGIBLE'

     AND wias.activity_status = 'NOTIFIED'     

     AND wias.item_key = TO_CHAR (l.line_id)

ORDER BY l.org_id, l.top_model_line_id, l.line_id

--Transaction Account from SO Link

SELECT * FROM mtl_transaction_accounts mtaWHERE transaction_id IN (  SELECT transaction_id  FROM mtl_material_transactions 

WHERE trx_source_line_id IN (SELECT line_id FROM oe_order_lines_all WHERE header_id =1333 ))

--AR Invoice Header Table

select * from ra_customer_trx_all a

EBS : FND LOAD Scripts, LDT File (Oracle APPS)

FNDLOAD

The FNDLOAD loader program can move the EBS data between database and text file representations.
This is an Oracle utility provided by Oracle for the transfer of a wide range of Oracle Foundation (FND) data from one instance to another instance. This can be defined as a concurrent program that can move Oracle Apps data between database and text file or this can be defined as FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database very easily.

Its help to move the setup from once instance to another instance like Concurrent Program, Alerts, Lookup, Personalization,Data Definitions ,Template,Function etc.

How FNDLOAD Works

The Conversion between database format and text file format is specified by a configuration file in Oracle Apps. Oracle Apps does provide a confirmation file and it is important to just pass the configuration file name and then just call the loader and leave everything.

List of .lct file (Configuration File)

afcpprog.lct : Concurrent Program Definitions

afcprset.lct : Concurrent Request Sets

afffload.lct : Flexfields (KFF, DFF, value sets, value sets data)

aflvmlu.lct : Lookup types and codes

afscprof.lct : Profiles and Profile Values

afscursp.lct : Security data (users, responsibilities, etc)

afsload.lct : Menu / Function Security Data•Lookup Types

afattach.lct : Load attachments setup data

afcppstl.lct: : Concurrent Program Printer Styles

afcpreqg.lct : Concurrent Request Groups

Steps of Creating LDT file:-

1)Login to Application Server using WInscp -> Navigate to path like /home directory where you want to download ldt file.

2).ldt file shall always create in the directory where the fnd_load command will execute

2) Once Downloaded move to local directory and then move to target directory where its need to be uploaded.

----------Concurrent Program------------

Download:-

$FND_TOP/bin/FNDLOAD apps/alam  O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXX_GST_APXINRIR.ldt 

Upload:-

$FND_TOP/bin/FNDLOAD apps/alam64 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXPROMISE_DATE_CALC_CP.ldt

------Personalization-------

Download:-

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXHIL_PO_UPD_BPA.ldt FND_FORM_CUSTOM_RULES function_name='PO_POXPOEPO' RULE_KEY='XXO_UPD_BPA'

Upload:-

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PROMISE_DT.ldt

----------Valueset download----------------------------------------------------------------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXSANCTION_DISC_TYPE.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME ='XXSANCTION_DISC_TYPE'

Upload:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXSANCTION_DISC_TYPE_VL.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXOM_SANCTION_DISC_TYPE'

-------DATA DEFINITIONS---------------

$FND_TOP/bin/FNDLOAD apps/clone 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_GST_CASH_REGS_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="XX"  DATA_SOURCE_CODE="XX_GST_CASH_REGS" TMPL_APP_SHORT_NAME="XX" TEMPLATE_CODE="XX_GST_CASH_REGS"

-------DataDefintions and Template---

Download:-

Upload:-

FNDLOAD apps/techhards 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct $XXX_TOP/XXX_GST_APXINRIR_DD.ldt

---Upload RTF---------------------------------------------

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD B6gggggg -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=auohsitutxd09.oracleoutsourcing.com)(PORT=16710))(CONNECT_DATA=(SERVICE_NAME=DITU6I)))' -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXX -LOB_CODE XXX_GST_APXINRIR -LANGUAGE en -TERRITORY IN -XDO_FILE_TYPE RTF -FILE_NAME $XXX_TOP/XXX_GST_APXINRIR.rtf -OWNER apps -CUSTOM_MODE FORCE

--------Upload Bursting--------

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD B6zOC8qb -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=9192.168.0.1)(PORT=167154)(CONNECT_DATA=(SERVICE_NAME=1111I)))' -LOB_TYPE BURSTING_FILE -LOB_CODE XXX_GST_BIP_POXPRPOP -XDO_FILE_TYPE XML -FILE_NAME $XXX_TOP/XXX_GST_PO_PRINT_BURST_CONTROL2.xml -APPS_SHORT_NAME XXX -NLS_LANG 00 -TERRITORY 00 -LOG_FILE $LOG_FILE_NAME

-------Valueset-----------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_SANC_DISC_TYPE.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME ='XX_SANC_DISC_TYPE'

Upload:-

---------Oracle Form-----

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct FABIL003_form.ldt FORM APPLICATION_SHORT_NAME="XX" FORM_NAME="FABIL003"

Upload:-

--------Oracle Form Func-----

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXHIL_PO_REQ_IMP_INTG.ldt FUNCTION FUNC_APP_SHORT_NAME="XXHIL" FUNCTION_NAME="XXHIL_PO_REQ_IMP_INTG"

--Oracle Function other than forms-

Download:-

$FND_TOP/bin/FNDLOAD apps/TECHHARDS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct TECHHARDS_PO_REQ_IMP_INTG_FUNC.ldt FUNCTION FUNCTION_NAME="TECHHARDS_PO_REQ_IMP_INTG"

Upload:-

*******Workflow******************

Download:-

WFLOAD apps/apps  0 Y DOWNLOAD XXTECHHARDS_POWFRQAG.wft POWFRQAG

Upload:-

WFLOAD apps/apps 0 Y FORCE XXTECHHARDS_POWFPOAG.wft

-------Lookup-----------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_OM_DEV_REMARKS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='ONT' LOOKUP_TYPE='XX_OM_DEV_REMARKS'

Upload:-

---Profile Options----------------------------------------------------------

Download:-

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='' APPLICATION_SHORT_NAME=”FND”

Upload:-

$FND_TOP/bin/FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXHIL_OM_DOM_MKTG_ROLE_PRF.ldt PROFILE PROFILE_NAME="XXHIL_OM_DOM_MKTG_ROLE" APPLICATION_SHORT_NAME="ONT"

###################################################################################WEBADI LDT File download/upload scripts

--################################################################################--

Below are the different components of WebADI the can be migrated from one instance to another

•     Integrators
•     Contents
•     Layouts
•     Mappings
•     Parameter List
•     Components

Integrator:-

  Download:-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XXFAHINTEGRATOR_LDT_FILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN=”XXFAHCUST” INTEGRATOR_CODE=”XXFAHINTEGRATOR_CODE”

  Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XXFAHINTEGRATOR_LDT_FILE.ldt 

-------Contents----------

Download:-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct XXFAHCONTENT_LDT_FILE.ldt BNE_CONTENTS CONTENT_ASN=”XXFAHCUST” CONTENT_CODE=”XXFAHCONTENT_CODE” 

Upload:-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecont.lct XXFAHCONTENT_LDT_FILE.ldt 

-------Layout-------

Download Layout:

 FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct XXFAHLAYOUT_LDT_FILE.ldt BNE_LAYOUTS LAYOUT_ASN=”XXFAHCUST” LAYOUT_CODE=”XXFAHLAYOUT_CODE” 

Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnelay.lct XXFAHLAYOUT_LDT_FILE.ldt 

-------Mappings-------

  Download :-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct XXFAHMAPPINGS_LDT_FILE.ldt BNE_MAPPINGS MAPPING_ASN=”XXFAHCUST” MAPPING_CODE=”XXFAHMAPPINGS_CODE” 

Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnemap.lct XXFAHMAPPINGS_LDT_FILE.ldt 

Parameter Lists

  Download Parameter List

    FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XXFAHPARAM_LIST_LDT_FILE.ldt BNE_PARAM_LISTS PARAM_LIST_ASN=”XXFAHCUST” PARAM_LIST_CODE=”XXFAHPARAM_LIST_CODE” 

  Upload Parameter List

    FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XXFAHPARAM_LIST_LDT_FILE.ldt 

Components

  Download Component

    FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecomp.lct XXFAHCOMPONENT_LDT_FILE.ldt BNE_COMPONENTS COMPONENT_ASN=”XXOM” COMPONENT_CODE=”XXFAHCOMPONENT_CODE” 

  Upload Component

    FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecomp.lct XXFAHCOMPONENT_LDT_FILE.ldt 

--################################################################################--

#FNDLOAD #Oracle Apps #Migration Utility #.lct #.ldt #EBS

Download / Upload LDT File

Download / Upload WFT File