How to find Full Infomation Employee in Oracle R12


SELECT   person_id,
         title,
         first_name,
         middle_names,
         last_name,
         Decode(sex,'M', 'Male','F','Female','NA') Gender,
         date_of_birth,
         Decode(marital_status,'M', 'Married','S', 'Single') Marital_Status,
         nationality,
         national_identifier,
         resume_exists,
         email_address,
         town_of_birth,
         business_group_id,
         employee_number,
         full_name,
         original_date_of_hire,
         party_id
  FROM   per_all_people_f prf
 WHERE   1 = 1
         AND TRUNC (SYSDATE) BETWEEN prf.effective_start_date
                                 AND  prf.effective_end_date;

Procure to Pay Cycle (P2P) Query


SELECT
  PRH.SEGMENT1 REQ_NO,PRL.QUANTITY REQ_QTY,PRL.UNIT_PRICE REQ_PRICE,PRH.AUTHORIZATION_STATUS REQ_STATUS
,PHA.ORG_ID,PHA.SEGMENT1 PO_NUMBER,PLA.QUANTITY,PLA.UNIT_PRICE,PHA.AUTHORIZATION_STATUS PO_STATUS
,(SELECT APT.NAME FROM AP_TERMS APT WHERE APT.TERM_ID =PHA.TERMS_ID) PAYMENT_TERMS
,(SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID=RT.ORGANIZATION_ID) ORGZ_NAME
,RSH.SHIPMENT_NUM,RT.SUBINVENTORY,RT.PRIMARY_QUANTITY RCV_QTY,RT.TRANSACTION_TYPE
,AIA.INVOICE_NUM, AIA.VENDOR_ID
,AIP.PAYMENT_NUM,ACA.CHECK_NUMBER
FROM
     PO_REQUISITION_HEADERS_ALL PRH
    ,PO_REQUISITION_LINES_ALL   PRL
    ,PO_REQ_DISTRIBUTIONS_ALL   PRD
    ,PO_DISTRIBUTIONS_ALL       PDA
    ,PO_HEADERS_ALL             PHA
    ,PO_LINES_ALL               PLA
    ,RCV_TRANSACTIONS           RT
    ,RCV_SHIPMENT_HEADERS       RSH
    ,RCV_SHIPMENT_LINES         RSL
    ,AP_INVOICES_ALL            AIA
    ,AP_INVOICE_LINES_ALL       AIL
    ,AP_INVOICE_DISTRIBUTIONS_ALL AID
    ,AP_INVOICE_PAYMENTS_ALL    AIP
    ,AP_PAYMENT_SCHEDULES_ALL   APS
    ,AP_CHECKS_ALL              ACA
WHERE
    PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID  = PRD.DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID= RT.PO_DISTRIBUTION_ID
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND AIA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID --AIA.QUICK_PO_HEADER_ID
--AND AIL.PO_LINE_ID(+)=PLA.PO_LINE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.RCV_TRANSACTION_ID(+)=RT.TRANSACTION_ID
AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND AID.INVOICE_ID=AIP.INVOICE_ID(+)
AND AIP.INVOICE_ID=APS.INVOICE_ID(+)
AND AIP.INVOICE_PAYMENT_ID =ACA.PAYMENT_ID(+)
Order By PO_NUMBER

Order to Cash cycle (O2C) Query

SELECT
     AC.CUSTOMER_NAME
    ,OOH.ORDER_NUMBER
    ,OOH.ORDERED_DATE
    ,(Select  ORGANIZATION_CODE From mtl_parameters where ORGANIZATION_ID=OOH.SHIP_FROM_ORG_ID) ORG_CODE
    ,OT.NAME ORDER_TYPE
    ,OOL.ORDERED_ITEM
    ,OOL.ORDERED_QUANTITY
    ,OOL.SHIPPED_QUANTITY
    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE
    ,WND.NAME DELIVERY_NO
    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=RCT.TERM_ID) TERM_NAME
    ,RCT.TRX_NUMBER
    ,RCT.TRX_DATE
    ,ARPS.AMOUNT_LINE_ITEMS_ORIGINAL TOTAL_INVOICE_AMOUNT
    ,ARPS.TAX_ORIGINAL      ORIGINAL_TAX_AMT
    ,ARPS.TAX_REMAINING     REMAINING_TAX_AMT
    ,ARPS.AMOUNT_DUE_REMAINING DUE_AMOUNT
FROM
    OE_ORDER_HEADERS_ALL OOH
    ,OE_ORDER_LINES_ALL OOL
    ,OE_TRANSACTION_TYPES_TL OT
    ,AR_CUSTOMERS AC
    ,WSH_DELIVERY_DETAILS WDD
    ,WSH_NEW_DELIVERIES   WND
    ,WSH_DELIVERY_ASSIGNMENTS WDA
    ,RA_CUSTOMER_TRX_ALL RCT
    --,RA_CUSTOMER_TRX_LINES_ALL RCTL
    ,AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE
 OOH.HEADER_ID=OOL.HEADER_ID
AND  OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID
AND  AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID
--AND  WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND  WDD.SOURCE_LINE_ID(+)=OOL.LINE_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID(+)
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID(+)
--AND  WND.CUSTOMER_ID=AC.CUSTOMER_ID
--AND  OOH.ORG_ID=RCT.ORG_ID
--AND  OOH.ORDER_NUMBER=RCT.INTERFACE_HEADER_ATTRIBUTE1(+)
AND  WDA.DELIVERY_ID=RCT.INTERFACE_HEADER_ATTRIBUTE3(+)
AND ARPS.CUSTOMER_TRX_ID(+)=RCT.CUSTOMER_TRX_ID
--AND  RCT.ORG_ID=RCTL.ORG_ID
--AND  RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID(+)
--AND RCTL.INTERFACE_LINE_ATTRIBUTE6(+)=TO_CHAR(OOL.LINE_ID)
--AND RCTL.interface_line_context = 'ORDER ENTRY'
--AND RCTL.LINE_TYPE='LINE'
--AND OOH.ORDER_NUMBER=RCTL.INTERFACE_LINE_ATTRIBUTE1
Order By ORDER_NUMBER

How to enable Operating Unit LOV has no values When Submitting Concurrent Requests For a Responsibility

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent -> Programs
3. Query for short name of the concurrent program
4. Click on Update pencil icon of your program
5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit
     Mode' field
7. Save changes by clicking on 'Apply' button.




UPDATE FND_CONCURRENT_PROGRAMS
SET MULTI_ORG_CATEGORY='S'
WHERE CONCURRENT_PROGRAM_NAME = '<your program name>'

How to set a User as Worker

Step 1: Go to system administrator responsibility
Step 2:  Go to Security 
Step 3: then go User
Step 4: then Define menu.
Step 5: Query to user name.
Step 6: Set to person name.


How to find how many and what DFF (Descriptive Flex Field) are enabled in the Oracle APPS R12

SELECT APPLICATION_TABLE_NAME,TITLE ,FORM_CONTEXT_PROMPT
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE FREEZE_FLEX_DEFINITION_FLAG='Y'
AND LAST_UPDATED_BY NOT IN (-1,1,2,0,120,121);

DFF :- Personalizing the flexfield segments is not possible through forms personalization due to below reason:

Personalizing the flexfield segments is not possible through forms personalization due to below reason:

A Flexfield is a single field in a Form but then when you click into it and it opens up the flexfields window, 
the multiple fields you see is actually a user exit with multi segment values, not form fields.
No Form Personalization events are passed to flexfield windows (user exits).
No Form Personalization events are passed to segments inside the flexfield window (user exit).

If you want to disabled the segments, you can disable the segment
in flexfield definition form(Uncheck the enabled checkbox).


The flex developer has stated this would involve a major re-write of the flex code for APPCORE
to process forms personalization rules and actions pertaining to flexfields.
One need to understand that flexfields are not assigned to a canvas, 
the flexfield UIs in forms are not native form fields but built on the fly with user exits.


Defaulting, populating, and or manipulating hidden fields for descriptive
flexfield attributes should not, and are not supported by flexfields to be
done through forms personalizations. Only Flexfields will support through
the flexfield setup and definition.

How to find Table Information and Application Name

SELECT   FT.TABLE_NAME,FT.USER_TABLE_
NAME,
           FT.DESCRIPTION TABLE_DESC,
           FAL.APPLICATION_NAME,
           FA.APPLICATION_SHORT_NAME,
           FC.COLUMN_SEQUENCE,
           FC.COLUMN_NAME,
           FC.USER_COLUMN_NAME,
           (SELECT FC.COLUMN_NAME FROM FND_PRIMARY_KEY_COLUMNS FPK
           WHERE FPK.APPLICATION_ID= FC.APPLICATION_ID
           AND   FC.TABLE_ID = FPK.TABLE_ID
           AND   FC.COLUMN_ID  = FPK.COLUMN_ID) PRIMARY_KEY_COLUMNS,
           FC.COLUMN_TYPE,
           FC.WIDTH,
           FC.PRECISION,
           FC.SCALE,
           FC.DEFAULT_VALUE,
           FC.NULL_ALLOWED_FLAG,
           FC.TRANSLATE_FLAG,
           FC.DESCRIPTION COL_DESCRIPTION,
           FC.FLEXFIELD_APPLICATION_ID,
           FC.FLEXFIELD_NAME,
           FLEXFIELD_USAGE_CODE
    FROM   FND_TABLES FT
         , FND_COLUMNS FC
         , FND_APPLICATION FA
         , FND_APPLICATION_TL FAL
   WHERE   FAL.APPLICATION_NAME = NVL(:P_APPLICATION_NAME,0) --Receivables --FC.APPLICATION_ID = 222)
   AND     FT.TABLE_NAME       = NVL(:P_TABLE_NAME,0)       --RA_CUSTOMERS --(FC.TABLE_ID = 52559)
   AND     FT.APPLICATION_ID = FC.APPLICATION_ID
   AND     FT.TABLE_ID       = FC.TABLE_ID
   AND     FT.APPLICATION_ID = FAL.APPLICATION_ID
   AND     FA.APPLICATION_ID = FAL.APPLICATION_ID
   AND     FT.TABLE_TYPE= 'T' --Transaction Data
ORDER BY   COLUMN_SEQUENCE

How many Colummn in a table?

SELECT column_name, data_type, data_length, data_precision, nullable
FROM all_tab_cols
WHERE table_name ='RA_CUSTOMER_TRX_ALL';

How to Find all RTF Report Details Query


SELECT   a.user_concurrent_program_name AS user_concurrent_program_name,
         va.execution_file_name short_name,
         va.executable_name executable_name,
         NVL ((SELECT                                             ---LOB_CODE,
                      file_name
                 FROM xdo_lobs
                WHERE application_short_name = 'XXJG'
                  AND xdo_file_type = 'RTF'
                  AND UPPER (lob_code) = UPPER (va.executable_name)),
              'NOT RTF Report'
             ) rtf_name,
         a.concurrent_program_name AS concurrent_program_name,
         a.output_file_type, a.output_print_style,
         c.application_short_name AS application_short_name,
         b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
         b.form_left_prompt AS prompt,
         d.flex_value_set_name AS values_set_name, va.application_name
    FROM fnd_concurrent_programs_vl a,
         fnd_descr_flex_col_usage_vl b,
         fnd_application c,
         fnd_flex_value_sets d,
         fnd_executables_form_v va
   WHERE a.enabled_flag = 'Y'
     AND a.application_id = va.application_id
     AND TRIM (va.executable_name) = TRIM (a.concurrent_program_name)
     AND a.concurrent_program_name = SUBSTR (b.descriptive_flexfield_name, 7, 100)
     AND a.application_id = c.application_id
     AND b.enabled_flag = 'Y'
     AND b.flex_value_set_id = d.flex_value_set_id
     AND a.application_id = 20003                      --(Your Application Id)

Scripts to Create LOOKUP from BACKEND

fnd_lookup_values_pkg.load_row
                                   (x_lookup_type             => 'MJF_FINAL_DESTINATION',
                                    x_view_appsname           => 'WSH',
                                    x_lookup_code             => p_destination,
                                    x_enabled_flag            => 'Y',
                                    x_start_date_active       => SYSDATE,
                                    x_end_date_active         => NULL,
                                    x_territory_code          => NULL,
                                    x_tag                     => NULL,
                                    x_attribute_category      => NULL,
                                    x_attribute1              => NULL,
                                    x_attribute2              => NULL,
                                    x_attribute3              => NULL,
                                    x_attribute4              => NULL,
                                    x_attribute5              => NULL,
                                    x_attribute6              => NULL,
                                    x_attribute7              => NULL,
                                    x_attribute8              => NULL,
                                    x_attribute9              => NULL,
                                    x_attribute10             => NULL,
                                    x_attribute11             => NULL,
                                    x_attribute12             => NULL,
                                    x_attribute13             => NULL,
                                    x_attribute14             => NULL,
                                    x_attribute15             => NULL,
                                    x_last_update_date        => NULL,
                                    x_owner                   => NULL,
                                    x_meaning                 => p_destination,
                                    x_description             => p_destination,
                                    x_security_group          => NULL,
                                    x_custom_mode             => NULL
                                   );

YTD Opening Date Wise

SELECT CASE
          WHEN TO_DATE (:psdate) <
                             TO_DATE ('01-APR-' || SUBSTR (:psdate, 8, 4))
             THEN    '01-APR-'
                  || TO_CHAR (TO_NUMBER (SUBSTR (:psdate, 8, 4)) - 1)
          ELSE '01-APR-' || SUBSTR (:psdate, 8, 4)
       END CURRENT_YTD
FROM DUAL;

Oracle Apps Tables Name (Most Ueses)

General Ledger (GL)

GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS
GL_BALANCES
GL_JE_CATEGORIES_TL
GL_JE_SOURCES_TL

GL_LEDGERS
GL_SETS_OF_BOOKS

 Accounts Payables (AP):


AP_INVOICES_ALL
AP_EXPENSE_REPORT_HEADERS_ALL
AP_INVOICE_RELATIONSHIPS
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_HOLDS_ALL
AP_AE_LINES_ALL
AP_AE_HEADERS_ALL
AP_CHECKS_ALL

 Accounts Receivables (AR):

RA_CUSTOMER_TRX_ALL
RA_CUST_TRX_TYPES_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_CASH_RECEIPTS_ALL
AR_RECEIVABLES_TRX_ALL
AR_RECEIPT_METHOD_ACCOUNTS_ALL
AR_RECEIVABLE_APPLICATIONS_ALL

Oracle Inventory (INV)

MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_TL
MTL_ITEM_CATEGORIES
MTL_ITEM_SUB_INVENTORIES
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_CATEGORY_SETS_TL
MTL_DEMAND
MTL_ONHAND_QUANTITIES
MTL_MATERIAL_TRANSACTIONS_TEMP
MTL_SYSTEM_ITEMS_FVL
MTL_LOT_NUMBERS
MTL_UNITS_OF_MEASURE_TL
MTL_TXN_SOURCE_TYPES
MTL_MATERIAL_TRANSACTIONS
MTL_TRANSACTION_TYPES
MTL_SECONDARY_INVENTORIES -------Sub Inventory Table
MTL_ITEM_LOCATIONS  -------Locator Table

Purchasing (PO)
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES
PO_REQ_DISTRIBUTIONS_ALL
PO_VENDORS
PO_VENDOR_SITES_ALL

Order Management (OM)
Entered
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_PRICE_ADJUSTMENTS
OE_ORDER_PRICE_ATTRIBS
OE_ORDER_HOLDS_ALL
Booked
OE_ORDER_HEADERS_ALL
WSH_DELIVERY_DETAILS

Pick Released 
WSH_DELIVERY_DETAILS
WSH_PICKING_BATCHES
MTL_RESERVATIONS

Full Transaction
MTL_MATERIAL_TRANSACTIONS
MTL_TXN_REQUEST_HEADERS
MTL_TXN_REQUEST_LINES
WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS

Pick Confirmed
WSH_DELIVERY_DETAILS
Ship Confirmed
WSH_DELIVERY_DETAILS
MTL_MATERIAL_TRANSACTIONS
WSH_NEW_DELIVERIES
OE_ORDER_LINES_ALL
WSH_DELIVERY_LEGS
OE_ORDER_HEADERS_ALL

Auto invoice
WSH_DELIVERY_DETAILS
RA_INTERFACE_LINES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL

Price Details
QP_LIST_HEADERS_B
QP_PRICING_ATTRIBUTES
QP_LIST_LINES

Payment Terms
RA_TERMS
Auto Matic Numbering System
AR_SYSTEM_PARAMETES_ALL

Fixed Assets 
FA_ADDITIONS_B
FA_ADDITIONS_TL
FA_ADJUSTMENTS
FA_ADJUSTMENTS_T

Customer Information
HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
RA_CUSTOMERS

TAX
JAI_TAX_LINES
JAI_CMN_TAXES_ALL
JAI_PO_TAXES
JAI_CMN_INVENTORY_ORGS
JAI_RGM_REGISTRATIONS
JAI_OM_OE_SO_TAXES

Projects

PA_PROJECTS_ALL



How to print PO file attachments on the PO report (R12)

function CF_ATTACHFormula return Char is
V_ATTACH Varchar2(32000);
V_LONG_TXT LONG(32000);
begin
  SELECT DLT.LONG_TEXT INTO V_LONG_TXT
  /*AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DLT.LONG_TEXT */
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_LONG_TEXT DLT    ,
  PO_HEADERS_ALL    PHA
WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
AND DCT.CATEGORY_ID       = D.CATEGORY_ID
AND D.DATATYPE_ID         = DAT.DATATYPE_ID
AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID          = D.MEDIA_ID
AND DAT.NAME              = 'LONG_TEXT'
AND ENTITY_NAME = 'PO_HEADERS'
AND AD.PK1_VALUE  = PHA.PO_HEADER_ID
AND PHA.SEGMENT1 = :PO_ORDER;

DECLARE
   f_line       VARCHAR2 (32000);
   f            UTL_FILE.file_type;
   f_dir        VARCHAR2 (250);
   fname        VARCHAR2 (50);
   Comma1       VARCHAR (10);
   Comma2       VARCHAR (10);
   Comma3       VARCHAR (10);
   Comma4       VARCHAR (10);
   Comma5       VARCHAR (10);
 
BEGIN
   f_dir := '/usr/tmp';
   fname := 'TEST.txt';
   f := UTL_FILE.fopen (f_dir, fname, 'r');

 
   LOOP
      BEGIN
        
          UTL_FILE.get_line (f, f_line);
        EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

    
   V_ATTACH := V_ATTACH || CHR(10) ||f_line;
   
   SRW.MESSAGE(10001, V_LONG_TXT);

   ---   DBMS_OUTPUT.put_line(  f_line );

     
   END LOOP;

   UTL_FILE.fclose (f);
   COMMIT;
  
   Return V_ATTACH;
  
END;
end;

Workflow :- Hiding Workflow Buttons

1. Identify the .wft file that contains the definition of the process for which the email notification needs to be changed.

2. Connect to database using the Workflow Builder.

3. Open the .wft file in Oracle Workflow Builder.

4. Go to (M) Help > About Oracle Workflow Builder

5. Change Access level to 0 and check 'Allow modifications to customization'.

6. Expand the node of the item that needs to be changed, then under it, expand the  Messages node.

7. Right-click on the message that is currently sent to the users, and needs to be changed.

8. Define the attribute #WFM_OPEN_MAIL (Template for email with response).

9. Set its Default value to WFMAIL:OPEN_MAIL_OUTLOOK.

10. Click on Apply and save the change, and retest with a new WF process.

How to call a Concurrent Program from Database Trigger in oracle apps

CREATE OR REPLACE TRIGGER apps.xx_acct_analysis_tbl_t2
   AFTER INSERT
   ON gl.gl_je_headers
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   l_boolean        BOOLEAN;
   l_je_header_id   NUMBER;
   l_request_id     NUMBER;
BEGIN
   IF (    :NEW.status != 'P'
       AND :NEW.actual_flag = 'A'
       AND :NEW.attribute10 = 'Y'
       AND :NEW.je_source = 'AutoCopy'
      )
   THEN
      l_boolean := fnd_request.set_mode (TRUE);
      l_je_header_id := :NEW.je_header_id;
      l_request_id :=
         fnd_request.submit_request
                                   (application      => 'SQLGL',
                                    program          => 'XX_ACCT_ANALYSIS_IMPORT',
                                    start_time       => SYSDATE,
                                    argument1        => l_je_header_id,
                                    argument2        => NULL
                                   );
   END IF;
END xx_acct_analysis_tbl_t2;
/

----------------------------------------------------------------
CREATE OR REPLACE TRIGGER apps.xx_acct_analysis_tbl_t1
   AFTER UPDATE
   ON gl.gl_je_headers
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   l_boolean        BOOLEAN;
   l_je_header_id   NUMBER;
   l_request_id     NUMBER;
BEGIN
   IF (:NEW.status = 'P' AND :OLD.status != 'P' AND :OLD.actual_flag = 'A')
   THEN
      l_boolean := fnd_request.set_mode (TRUE);
      l_je_header_id := :OLD.je_header_id;
      l_request_id :=
         fnd_request.submit_request
                                   (application      => 'SQLGL',
                                    program          => 'XX_ACCT_ANALYSIS_IMPORT',
                                    start_time       => SYSDATE,
                                    argument1        => l_je_header_id,
                                    argument2        => NULL
                                   );
   END IF;
ENDxx_acct_analysis_tbl_t1;
/


How to find Legal Entity Address in Oracle Apps

SELECT    DECODE (hla.address_line_1, NULL, NULL, hla.address_line_1)
       || DECODE (hla.address_line_2, NULL, NULL, ', ' || hla.address_line_2)
       || DECODE (hla.address_line_3, NULL, NULL, ', ' || hla.address_line_3)
       || DECODE (hla.loc_information15,
                  NULL, NULL,
                  ', ' || hla.loc_information15
                 )
       || DECODE (hla.loc_information16,
                  NULL, NULL,
                  ', ' || hla.loc_information16
                 )
       || DECODE (ftv.territory_short_name,
                  NULL, NULL,
                  ', ' || ftv.territory_short_name
                 )
       || DECODE (hla.postal_code, NULL, NULL, ' - ' || hla.postal_code)  LE_Address
  FROM xle_entity_profiles etbp,
       xle_registrations reg,
       hr_locations_all hla,
       fnd_territories_vl ftv
 WHERE etbp.legal_entity_id = reg.source_id
   AND etbp.legal_entity_id = (SELECT   DISTINCT DEFAULT_LEGAL_CONTEXT_ID
                               FROM   HR_OPERATING_UNITS
                              WHERE   NAME = 'XX_OPERATING_UNIT')
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND identifying_flag = 'Y'
   AND hla.location_id = reg.location_id
   AND ftv.territory_code = hla.country;

How to find Legal Entity name in Oracle apps

SELECT   XEP.NAME
  INTO   LEGAL_ENTITY_NAME
  FROM   XLE_ENTITY_PROFILES XEP
 WHERE   LEGAL_ENTITY_ID  =  (SELECT   DISTINCT DEFAULT_LEGAL_CONTEXT_ID
                               FROM   HR_OPERATING_UNITS
                              WHERE   NAME = 'XX_OPERATING_UNIT')