Query to get the Project Expenditures Defined for an Employee for a Specific Period

SELECT
  EXPT.EXPENDITURE_TYPE ,
  EXPI.Quantity ,
  CASE
    WHEN (EXPT.unit_of_measure='HOURS')
    THEN NVL(CDL.PROJECT_BURDENED_COST,cdl.project_raw_cost)
  END Raw_cost ,
  --EXPI.DENOM_RAW_COST
  EXPI.ACCRUED_REVENUE ,
  EXPI.expenditure_item_id ,
  EXPI.expenditure_id ,
  EXPI.EXPENDITURE_ITEM_DATE,
  PAP.GL_PERIOD_NAME GL_PERIOD,
  PAP.PERIOD_NAME PA_PERIOD,
  PAP.START_DATE Period_Start_Date,
  PAP.END_DATE Period_End_Date,
  --  (SELECT
  --DECODE(EXPT.Expenditure_type,'Straight Time',EXPI.Quantity,'Overtime 0.0X',EXPI.Quantity, 'Overtime 1.0X', EXPI.Quantity,'Overtime 1.5X',EXPI.Quantity,'Overtime 2.0X',EXPI.Quantity, 'Overtime 2.5X',EXPI.Quantity,'Premium',EXPI.quantity,0)Total_Hours,
  CASE
    WHEN EXPT.Expenditure_category='Labor'
    AND EXPT.unit_of_measure      ='HOURS'
    THEN EXPI.Quantity
    ELSE 0
  END Total_Hours,
  --  (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN EXPI.QUANTITY
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN EXPI.QUANTITY
        ELSE 0
      END
    ELSE 0
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --      PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --  AND EXPI.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  --  AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  OT_DT_Hours,
  -- (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE NOT IN('Overtime 0.0','Overtime 1.5X','Overtime 2.0X','Overtime 1.0X','Overtime 2.5X','Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT','Union Vac/Supp Dues')
        THEN EXPI.QUANTITY
      END
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --     PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --    AND EXP.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  -- AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  Regular_Hours,
  CDL.acct_raw_cost,
  EXP.INCURRED_BY_PERSON_ID
FROM apps.PA_PERIODS_ALL pap,
  apps.PA_COST_DISTRIBUTION_LINES_ALL CDL,
  apps.PA_EXPENDITURE_ITEMS_ALL EXPI,
  apps.PA_EXPENDITURE_TYPES EXPT,
  apps.PA_EXPENDITURES_ALL EXP
WHERE CDL.PA_DATE BETWEEN PAP.START_DATE(+) AND PAP.END_DATE(+)
AND CDL.ORG_ID                 = PAP.ORG_ID(+)
AND PAP.GL_PERIOD_NAME         ='SEP-17'
AND CDL.LINE_NUM(+)            = 1
AND CDL.EXPENDITURE_ITEM_ID(+) = EXPI.EXPENDITURE_ITEM_ID
AND EXPI.EXPENDITURE_TYPE      = EXPT.EXPENDITURE_TYPE
AND EXP.EXPENDITURE_ID         = EXPI.EXPENDITURE_ID
AND EXP.INCURRED_BY_PERSON_ID IS NOT NULL

JDeveloper Installation and Setting Environment

Prerequisites 

Desktop with 1.5 GB RAM 

1.Telnet and FTP access to apps and db server 

2.Database connectivity details:

3.Apps username and password,

4.SID,

5.Host Name and port. 

6.Exact version of OA Framework on server

Get the JDeveloper software ( Ex:ZIP File  : p4141787_11i_GENERIC.zip )

Copy  into required drive ( folder ) / ( C:\)

Eg: C:\ JDEV  ( create JDEV ( name can be any one ) folder in C-drive

Extract the ZIP file name p4141787_11i_GENERIC.zip )

Right click à WinZip à Extract To Here

After extracting it generates following

JDEV---- ( user created folder )

|____ jdevbin  

|____ Jdevdoc

|____   jdevhome

Take the shortcut of  C:\JDEV\jdevbin\jdev\bin\ jdevW.exe     to desktop

Copy the  TEST.dbc file form Oracle Apps Server to JDeveloper

Source Oracle Apps path:   

\oracle\inst_TOP\apps\test2\appl\fnd\12.0.0\secure\TEST.dbc

JDeveloprer Path: C:\JDEV\jdevhome\jdev\dbc_files\secure

 

Set the Environment variables of O/S

My Computer à Advanced à Environment Variables à New à 

Variable Name : JDEV_USER_HOME

Variable Value : C:\JDEV\jdevhome\jdev

OK à OKàOK

Testing Functionality of Jdeveloper

Go to connection  à Right Click à New database connection à Next à 

Connection Name : test ( as desired )

Connection Type : Oracle ( JDBC )

Next :

User Name : apps

Password : apps

Next

Driver  : thin

Host Name : localhost ( if database is on the local system, else URL of DB server )

JDBC Port: 1521

SID       : VIS

For details see the vis.dbc located in the folder :

\\oracle\inst_TOP\apps\fnd\12.0.3\secure\apps

APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(LOAD_BALANCE\=YES)(FAILOVER\=YES)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=tcp)(HOST\=APPS.ora.com)(PORT\=1521)))(CONNECT_DATA\=(SID\=VIS)))

Next

Test Connection

Result: success

Next

Finish

ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters

Symptoms:-

Concurrent program completed warning with   “ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters” this reason.

Solution:-

SQL> alter session set "_fix_control"='17376322:OFF'; 

or at system level : 
SQL> alter system set "_fix_control"='17376322:OFF'; 

Enabling Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility

How to Enable Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility

Goal:-

What are the steps to enable Create/View Accounting for a user-defined responsibility?

Solution:-

Please add Subfunction "SLA: View Accounting - Lines Inquiries" and "XLA: View Accounting Lines" into your own menu as following:

Responsibility: System Administrator
Navigation : Application > Menu

   Query for the Menu value: FUN_AR_RECEIPT_PROCESSING
   Scroll down and add following lines :


         Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'SLA: View Accounting - Lines Inquiries' from the LOV
         Description: Leave blank

       Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'XLA: View Accounting Lines' from the LOV
         Description: Anything say "View Accounting Lines"

then save the change and retest the issue.

Workflow download and upload commands in Oracle apps

Workflow upload

WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]

Example:

WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXDOY_TOP/install/XXDOYAP.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE	Honors both protection and customization levels of data
UPLOAD	Honors only protection level of data [No respect of Customization Level]
FORCE	Force upload regardless of protection or customization level

Workflow Download

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>

Example:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD XXDOYAP.wft

Convert the Amount in to the Word using Function in Oracle Apps EBS R12

 

Convert the Amount in to the Word using Function in Oracle Apps EBS R12.

Function:

CREATE OR REPLACE FUNCTION APPS.Get_amount_to_word(P_LC_AMOUNT IN NUMBER,P_CURRENCY_CODE IN VARCHAR2)
RETURN VARCHAR2
IS

x_tot_amount1     NUMBER            := 0;
x_tot_amount      VARCHAR2(30)      := 0;
x_amount_in_word  VARCHAR2(2000)    := 0;

BEGIN

x_tot_amount1 := p_lc_amount;

    x_tot_amount := TO_CHAR(x_tot_amount1,'999999999999.99');
   
    BEGIN
        IF NVL(x_tot_amount1,0) >= 0 THEN
            IF p_currency_code ='INR' THEN -- ## g_const_currency_bdt = 'BDT' ##--
           
                BEGIN
                    SELECT  'Rupee '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Paisa'||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
            ELSIF p_currency_code <>'INR' THEN
           
               BEGIN
                    SELECT  REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
--            ELSIF p_currency_code ='USD' THEN   
--           
--               BEGIN
--                    SELECT  'Dollar '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
--                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
--                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
--                      INTO x_amount_in_word
--                      FROM DUAL;
--                END;
           
            END IF;
        END IF;
    END;
   
    RETURN (x_amount_in_word);
   
    EXCEPTION
    WHEN OTHERS THEN
    FND_FILE.put_line(fnd_file.log,'--##ERROR##--In PACKAGE.function = XXBEX_ASIA_LC_ATHRZTN_PRINT_PK.xxget_amount_to_word/'||SQLERRM);
    RETURN 0;   
END Get_amount_to_word;

====================================================================

CREATE OR REPLACE FUNCTION APPS.XX_CONVERT_AMOUNT_TO_WORDS (P_AMT       IN NUMBER )                                    
                                               RETURN VARCHAR2 IS
M_MAIN_AMT_TEXT      VARCHAR2(2000) ;
M_TOP_AMT_TEXT       VARCHAR2(2000) ;
M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;
M_DECIMAL_TEXT       VARCHAR2(2000) ;
M_TOP                NUMBER(20,5) ;
M_MAIN_AMT           NUMBER(20,5) ;
M_TOP_AMT            NUMBER(20,5) ;
M_BOTTOM_AMT         NUMBER(20,5) ;
M_DECIMAL            NUMBER(20,5) ;
M_AMT                NUMBER(20,5);
M_TEXT               VARCHAR2(2000) ;
BEGIN
   M_MAIN_AMT        := NULL ;
   M_TOP_AMT_TEXT    := NULL ;
   M_BOTTOM_AMT_TEXT := NULL ;
   M_DECIMAL_TEXT    := NULL ;
  
   -- To get paise part
   M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;
  
   IF M_DECIMAL >0 THEN
   M_DECIMAL := ROUND(M_DECIMAL *100);
   END IF;
  
   M_AMT        := TRUNC(P_AMT) ;          


   M_TOP        := TRUNC(M_AMT / 100000) ;
   M_MAIN_AMT   := TRUNC(M_TOP / 100);
   M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;
   M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

  IF M_MAIN_AMT > 0 THEN
      M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;
      IF M_MAIN_AMT = 1 THEN
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;
      ELSE
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;
      END IF ;
   END IF ;

   IF M_TOP_AMT > 0 THEN
      M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;
      IF M_TOP_AMT = 1 THEN
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;
      ELSE
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;
      END IF;
   END IF ;
   IF M_BOTTOM_AMT > 0 THEN
      M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;
   END IF ;
   IF M_DECIMAL > 0 THEN
      IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN
         M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;
      ELSE
         M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';
      END IF ;
        END IF ;
   M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;
   M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);
   M_TEXT := 'Rupees'||' '|| M_TEXT;
   RETURN (M_TEXT);

END XX_CONVERT_AMOUNT_TO_WORDS;

 

Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EBS R12

Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise,
Customer wise, Sales Order Wise, Transaction Date and
GL Date Wise in Oracle Apps EBS R12.

SELECT   rct.trx_number invoice_number,
                                       -- ARPS.AMOUNT_DUE_ORIGINAL BALANCE,
                                       arps.amount_due_remaining balance,
         hp.party_name bill_to_customer,
         DECODE (rctt.TYPE,
                 'CB', 'Chargeback',
                 'CM', 'Credit Memo',
                 'DM', 'Debit Memo',
                 'DEP', 'Deposit',
                 'GUAR', 'Guarantee',
                 'INV', 'Invoice',
                 'PMT', 'Receipt',
                 'Invoice'
                ) invoice_class,
         rct.invoice_currency_code currency, rct.trx_date inv_date,
         rctd.gl_date gl_date, (SELECT NAME
                                  FROM apps.ra_terms rat
                                 WHERE rat.term_id = rct.term_id) terms,
         rctt.NAME order_type
    FROM ra_customer_trx_all rct,
         -- RA_CUSTOMER_TRX_LINES_ALL      RCTL,
         ra_cust_trx_line_gl_dist_all rctd,
         hz_parties hp,
         hz_cust_accounts_all hca,
         ra_cust_trx_types_all rctt,
         hr_operating_units hou,
         ar_payment_schedules_all arps
   WHERE rct.customer_trx_id = rctd.customer_trx_id
--AND RCT.CUSTOMER_TRX_ID       = RCTL.CUSTOMER_TRX_ID
--AND RCTL.CUSTOMER_TRX_LINE_ID = RCTD.CUSTOMER_TRX_LINE_ID
     AND rct.bill_to_customer_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND rct.cust_trx_type_id = rctt.cust_trx_type_id
     AND rct.org_id = rctt.org_id
     AND rct.org_id = hou.organization_id
     AND arps.customer_trx_id(+) = rct.customer_trx_id
     AND rct.org_id = NVL (:p_org_id, rct.org_id)
     AND rct.trx_number = NVL (:trx_number, rct.trx_number)
                                                           -- KAL/2017/DE/1114
     AND rct.trx_date BETWEEN NVL (:p_trx_date_from, rct.trx_date)
                          AND NVL (:p_trx_date_to, rct.trx_date)
     AND rctd.gl_date BETWEEN NVL (:p_gl_date_from, rctd.gl_date)
                          AND NVL (:p_gl_date_to, rctd.gl_date)
     AND hp.party_name = NVL (:p_cust_name, hp.party_name)
     AND NVL (rct.ct_reference, 'XX') =
                         NVL (NVL (:p_sales_order_no, rct.ct_reference), 'XX')
GROUP BY rct.trx_number,
         -- ARPS.AMOUNT_DUE_ORIGINAL,
         arps.amount_due_remaining,
         hp.party_name,
         rctt.TYPE,
         rct.invoice_currency_code,
         rct.trx_date,
         rctd.gl_date,
         rct.term_id,
         rctt.NAME;