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

EBS : Sql Query: OE : How to get open Supply Demand Query for Internal Sales Order

How to get open Supply Demand Query for Internal Sales Order

/********* Internal Sales Order & Sale Order Query***/

SELECT  TO_CHAR (ooha.order_number) order_num,ml.meaning supply_demand_type,d.requirement_date required_date,      oola.line_id, oola.request_date, wda.delivery_id,

       NVL (  -1 * (  d.primary_uom_quantity - d.total_reservation_quantity    - d.completed_quantity ), 0 ) qty,

  FROM mtl_parameters p,

       mtl_system_items_b i,

       bom_calendar_dates c,

       mrp_demand_om_reservations_v d,

       oe_order_headers_all ooha,

       oe_order_lines_all oola,

       wsh_delivery_assignments wda,

       wsh_delivery_details wdd,

       mfg_lookups ml,

       (SELECT DECODE (demand_source_type,

                       2, DECODE (reservation_type, 1, 2, 3, 23, 9),

                       8, DECODE (reservation_type, 1, 21, 22),

                       demand_source_type

                      ) supply_demand_source_type,

               demand_id

          FROM mrp_demand_om_reservations_v) dx

 WHERE d.open_flag = 'Y'

   AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'

   AND ml.lookup_code = dx.supply_demand_source_type

   AND d.demand_id = dx.demand_id

   AND ooha.header_id = oola.header_id

   AND oola.line_id = d.demand_id

   AND wdd.source_line_id(+) = oola.line_id

   AND wdd.source_header_id(+) = oola.header_id

   AND wdd.delivery_detail_id = wda.delivery_detail_id(+)

   AND d.reservation_type != 2

   AND d.organization_id = :p_org_id

   AND d.primary_uom_quantity >

                        (d.total_reservation_quantity + d.completed_quantity

                        )

   AND d.inventory_item_id = :p_inventory_item_id

   AND (   d.visible_demand_flag = 'Y'

        OR (    NVL (d.visible_demand_flag, 'N') = 'N'

            AND d.ato_line_id IS NOT NULL

            AND NOT EXISTS (

                   SELECT 1

                     FROM oe_order_lines_all ool, mtl_demand md

                    WHERE TO_CHAR (ool.line_id) = md.demand_source_line

                      AND ool.ato_line_id = d.ato_line_id

                      AND ool.item_type_code = 'CONFIG'

                      AND md.reservation_type IN (2, 3))

           )

       )

   AND d.reservation_type != -1

   AND d.reservation_type != -1

   AND d.demand_source_type != -1

   AND d.demand_source_type != -1

   AND TRUNC (d.requirement_date) <= :p_to_date

   AND (   d.subinventory IS NULL

        OR d.subinventory IN (

              SELECT s.secondary_inventory_name

                FROM mtl_secondary_inventories s

               WHERE s.organization_id = d.organization_id

                 AND s.inventory_atp_code = 1

                 AND s.attribute1 = 'FG')

       )

   AND i.organization_id = d.organization_id

   AND i.inventory_item_id = d.inventory_item_id

   AND p.organization_id = d.organization_id

   AND p.calendar_code = c.calendar_code

   AND p.calendar_exception_set_id = c.exception_set_id

   AND c.calendar_date = TRUNC (d.requirement_date)

   AND d.inventory_item_id =

          DECODE (d.reservation_type,

                  1, DECODE (d.parent_demand_id,

                             NULL, d.inventory_item_id,

                             -1

                            ),

                  2, d.inventory_item_id,

                  3, d.inventory_item_id,

                  -1

                 )

Oracle Reports : How to add Barcode in rtf Template Report

 How to add Barcode in rtf Template Report

1) Create form felds , change the font and write below syntax in the field properties.

2) Font uploading and mapping in xml publisher Administrator

WEBADI : How to add Multiple column in LOV

 How to add Multiple column in LOV WEBADI

1) Go to Components

2) Click on Update and modify as shown below. Click on apply.

table-column-alias=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

table-columns=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

table-headers=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

3) Same column mapping need to be provided on  description LOV section of Integrator Interface as shown below

description =ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID

3) Table Value set view

 

Output

Oracle APPS: AP : Query to get Document sequence Name and generate value

How to get document Sequence name

1) Write query to get db sequence name

SELECT seq.db_sequence_name

       -- INTO l_sequence_name

        FROM fnd_document_sequences seq, fnd_doc_sequence_assignments sa

       WHERE seq.doc_sequence_id = sa.doc_sequence_id

         AND sa.application_id = 200

         AND sa.category_code ='AP Invoice'-- :l_document_name

         AND (sa.method_code = 'A' )

         

2)  get sequence value using below query

    BEGIN

      l_query :=

           'select ' || l_sequence_name || '.nextval into :next_val from sys.dual';

      EXECUTE IMMEDIATE l_query

                   INTO l_doc_sequence_value;

    END;       

    

    return l_doc_sequence_value;

    

Oracle APPS : AP : Due Date Derivation Logic based on Payment Terms

 

1) Payment Terms

2) Special Calendars

Navigation:-  Below Screenshot

Table Name :-  AP_OTHER_PERIOD_TYPES, AP_OTHER_PERIODS

-------------------------------------Query--------------------------------------------

SELECT apt.NAME,atl.calendar, atl.fixed_date, atl.due_day_of_month, atl.due_days

  FROM ap_terms apt, ap_terms_lines atl

 WHERE apt.term_id = atl.term_id  

 AND apt.NAME in ('RTEST')

select * from  AP_OTHER_PERIOD_TYPES

where MODULE ='PAYMENT TERMS'

and period_type =:lv_payment_term

select due_date from  AP_OTHER_PERIODS

where MODULE ='PAYMENT TERMS'

and period_type =:lv_payment_term

and :p_invoice_date between start_date and end_date

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

Logic Explained to derive Due date based on Invoice Date and Payment Term

Oracle APPS : HRMS : Query

HRMS Query

1) How to fetch Employee Latest Grade?

SELECT paaf.full_name || ' ' || paaf.employee_number emp_name,

       paaf.person_id emp_id, pg.NAME grade

  FROM per_all_people_f paaf,

       per_all_assignments_f pa,

       per_grades pg,

       fnd_user fu

 WHERE paaf.person_id = pa.person_id

   AND paaf.effective_end_date IN (SELECT MAX (paafs.effective_end_date)

                                     FROM per_all_people_f paafs

                                    WHERE paafs.person_id = paaf.person_id)

   AND pa.effective_end_date IN (

          SELECT MAX (paaf.effective_end_date)

            FROM per_all_assignments_f paafs

           WHERE paafs.assignment_id = pa.assignment_id

             AND paafs.person_id = pa.person_id)

   AND pa.grade_id(+) = pg.grade_id

   AND pa.person_id = fu.employee_id

   AND fu.user_id = :UserId;

2) 

Oracle Apps Query : How to delete Concurrent Program through API

 begin

fnd_program.delete_program('XX_EMP_COST_ITAX','Payroll');

fnd_program.delete_executable('XX_EMP_COST_ITAX','Payroll');

end;

Oracle : SQL Query : Special Character, Substr

 

1) How to remove Special Characters

"REPLACE(REPLACE(TRIM(REGEXP_REPLACE(:FREIGHT_TERMS_CODE,'[^a-z_A-Z ]')),CHR(11),''),CHR(13),'')" 

2) How to remove last 2 character from String

 substr( xpem.period_name,1,length(xpem.period_name)-2)

3) Query to get Sunday?

select

(next_day(last_day(trunc(sysdate)),'?')-7

-next_day(trunc(sysdate,'mm')-1,'?'))/7+1

as "sundays"

from dual;

SELECT TO_CHAR(dat,'DD-MON-RRRR')

FROM

    (SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual

    connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)

    WHERE TO_CHAR(dat,'DY') = 'SUN'

 GROUP BY TO_CHAR(dat,'MON-RRRR')

  

/* Formatted on 2018/04/06 15:18 (Formatter Plus v4.8.8) */

SELECT     TRUNC (:p_to_dat, 'MM') + LEVEL - 1 dat,TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') days,

           CASE

              WHEN TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') =

                                                                    'SUN'

                 THEN 'Holiday'

           END abc

      FROM DUAL

CONNECT BY LEVEL <= LAST_DAY (TRUNC (:p_to_dat)) - TRUNC (:p_to_dat, 'MM') + 1

------------------------------------------------------End Query to get sunday--------------------