Oracle Apps Query : how to Find DFF Flex Fields Attributes

 

To Find DFF Flex field  attribute

 

SELECT ffv.application_table_name, ffv.descriptive_flexfield_name,

       ffv.context_column_name, ffv.title, att.application_column_name,

       att.end_user_column_name, att.column_seq_num, att.enabled_flag,

       att.required_flag, att.security_enabled_flag, att.display_flag,

       att.flex_value_set_id, att.form_left_prompt, ffv.*

  FROM fnd_descriptive_flexs_vl ffv, fnd_descr_flex_col_usage_vl att

 WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

   -- AND ffv.descriptive_flexfield_name = 'PO_LINES'

   AND (ffv.title) = 'Transaction Information'

EBS : Query to find Operating units in Oracle Apps

How to find Operating Units in Oracle Apps

SELECT   hr.organization_id org_id, hr.NAME operating_unit,po_moac_utils_pvt.get_ou_shortcode (organization_id) ou_short_code

    FROM hr_operating_units hr

   WHERE po_moac_utils_pvt.check_access (hr.organization_id) = 'Y'

ORDER BY 1

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

#Oracle Apps #EBS #OU #Operating Units

Oracle Apps : how to delete Concurrent request/Executables

 

SET SERVEROUTPUT ON
 DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'Order_Details';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Selected template has been ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Selected Data Defintion has been ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;

Oracle Apps Query P2P-GL: PO to Gl Link Query and details

 select a.ITEM_DESCRIPTION,a.* 

 from ap_invoice_lines_all a

 

 select * from mtl_system_items_b

 where description like '%MILK%'

 AND ORGANIZATION_ID=103

 AND INVENTORY_ITEM_ID =2135

 

 

 2135  -- MILK (COW) 

---------------------PO---------------------------------------------------------

SELECT * FROM PO_HEADERS_ALL

 WHERE PO_HEADER_ID=2017

 

  SELECT *FROM PO_LINES_ALL

 WHERE ITEM_ID IN ( select INVENTORY_ITEM_ID from mtl_system_items_b

 where description like '%MILK%'

 AND ORGANIZATION_ID=103)

 AND ITEM_ID=2135

 AND PO_HEADER_ID=2017

-------------------Receipts----------------------------------------------------- 

 SELECT * FROM RCV_SHIPMENT_LINES R

 WHERE 1=1--ORGANIZATION_ID=103

 AND ITEM_ID=2135

 AND PO_HEADER_ID=2017

 AND PO_LINE_ID=2034

 

 SELECT TRANSACTION_ID,a.* FROM RCV_TRANSACTIONS a

 WHERE ORGANIZATION_ID=103

  --AND ITEM_ID=2135

 AND PO_HEADER_ID=2017

 AND SHIPMENT_LINE_ID = 5046;

--------------------MTL ACCOUNTING---------------------------------------------- 

 select TRANSACTION_ID,a.* from mtl_material_transactions a

 where a.RCV_TRANSACTION_ID in (2060,2062,2063);

 

 select * from mtl_transaction_accounts

 where TRANSACTION_ID =89463

 

--------------AP INV------------------------------------------------------------

 select * from ap_invoices_all

 where invoice_id = 35029

 

 select * from ap_invoice_distributions_all aid

 where (invoice_id ,aid.INVOICE_LINE_NUMBER) in (

 select invoice_id ,ail.LINE_NUMBER from ap_invoice_lines_all ail

 where po_header_id=2017

 AND PO_LINE_ID=2034)

  

--------------Sub Ledger Inv ---------------------------------------------------

  select *from xla.XLA_TRANSACTION_ENTITIES xte

 where 1=1

   AND xte.entity_code = 'AP_INVOICES'

 and SOURCE_ID_INT_1=35029  --invoice id--

 

 select * from xla_ae_headers

 where entity_id=60015

  

  select * from xla_ae_lines

 where ae_header_id=71836 

 

-----------------------INV GL---------------------------------------------------

select * from gl_import_references 

  where gl_sl_link_id in (  

  select gl_sl_link_id from xla_ae_lines

 where ae_header_id=71836 )

  

select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id,  5, gcc.segment5) FROM gl_code_combinations gcc

where  gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc

 ,gil.* from gl_je_lines gil

where je_header_id in (54034) 

 

-------------------AP Pay------------------

select * from ap_invoice_payments_all

where invoice_id=35029

 

select * from ap_checks_all

where check_id = 33542  

--------------Sub Ledger Pay ---------------------------------------------------

 

 select *from xla.XLA_TRANSACTION_ENTITIES xte

 where 1=1

   AND xte.entity_code = 'AP_PAYMENTS'

 and SOURCE_ID_INT_1=33542  --check id--

 

 select * from xla_ae_headers

 where entity_id=60079

  

  select gl_sl_link_id,a.* from xla_ae_lines a

 where ae_header_id=71837 

 

-----------------------Payments GL---------------------------------------------------

select * from gl_import_references 

  where gl_sl_link_id in (  

  select gl_sl_link_id from xla_ae_lines

 where ae_header_id in (71837 ,71836))

  

select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id,  5, gcc.segment5) FROM gl_code_combinations gcc

where  gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc

 ,gil.* from gl_je_lines gil

where je_header_id in (54036) 

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

 

 select * from gl_code_combinations_kfv

 where code_combination_id in (2012,3001)

 

 select * from ap_suppliers

 where vendor_id=2231

 

 

 

 

 1)PO 

 2)Receipt 

   a) MTL transaction---> 

                          Inventory Valuation dr 

                          Receiving Inspection cr  

                          

 3)AP INV DIST(standard Invoice)---

                          INVENTORY AP ACCRUAL

   a) subledger accounting--> 

                          1) Accural A/C Dr

                              To Liability A/C Cr   

 4) GL Lines --->  

                          liability ac Dr

                            Cash Clearing Cr

                                                                                                                   

OAF : VO Extension

EBS : Prepare Shell Scripts to download Concurrent program using PLSQL ( Oracle Apps)

 Oracle Apps: How to Prepare shell scripts file to download all custom concurrent Program from the system using plsql

DECLARE

l_string varchar2(2000);

l_conc_prog_string  varchar2(1000);

l_conc_prog_func_string  varchar2(1000);

fileHandler UTL_FILE.FILE_TYPE;

 ctlCode varchar2(2000);

 l_cnt Number :=0;

 

   CURSOR cur_conc_prog

   IS

    SELECT   concurrent_program_name conc_prog

        FROM fnd_concurrent_programs

       WHERE concurrent_program_name LIKE 'XX%'

    ORDER BY concurrent_program_name;

BEGIN

   -------------------------------Opeining File --------------------------------------------------

     fileHandler := UTL_FILE.FOPEN('APPS_DATA_FILE_DIR', 'Conc_Prog_download_LDT'||'_run.sh', 'W');

     

     

     --------------------------Start Priting Header Portion -----------------------------------------

    l_string := (   '# +===================================================================+'||chr(10) ||

                    '#  |                                                                   |'||chr(10)||

                    '#  |                                                                   |'||chr(10)||

                    '#  |File        : '||'Conc_Prog_download_LDT'||'_run.sh                                |'||chr(10)||

                    '#  |Description : This file will install all front-end                 |'||chr(10)||

                    '#  |Change History:                                                    |'||chr(10)||

                    '#  |---------------                                                    |'||chr(10)||

                    '#  |Version  Date         Author           Remarks                     |'||chr(10)||

                    '#  |-------  -----------  --------------   ----------------------------|'||chr(10)||

                    '#  |1.0 '||    to_Char(sysdate)||'  XX   Initial Version            |'||chr(10)||

                    '#  +===================================================================+'||chr(10)    

        );

      l_string := l_string ||chr(10)||('TIME_STAMP=`date +%Y%m%d%H%M`'||chr(10)||

                                       'LOG_FILE=`echo XX_$TIME_STAMP".log"`'||chr(10)||

                                       'TNS_DETAIL=`echo $HOSTNAME:$ORACLE_PORT:$ORACLE_SID` '||chr(10));

                                       

      UTL_FILE.PUTF(fileHandler, l_string);

      l_string :=                CHR(10) ||   

                 'CHKLOGIN(){  '||CHR(10)|| 

                            'if sqlplus /nolog <<! >/dev/null 2>&1 ' ||CHR(10)|| 

                            '     WHENEVER SQLERROR EXIT 1;   ' ||CHR(10)|| 

                            '      CONNECT $1 ;               ' ||CHR(10)|| 

                            '      EXIT;                      ' ||CHR(10)|| 

                       '!'||CHR(10)|| 

                       '      then                            ' ||CHR(10)||

                       '           echo OK                    ' ||CHR(10)||

                       '      else                            ' ||CHR(10)||

                       '           echo NOK                   ' ||CHR(10)||

                       '      fi                              ' ||CHR(10)||

                       ' }                                    ' ||CHR(10)||CHR(10)||CHR(10);

     

     UTL_FILE.PUTF(fileHandler, l_string);

     l_string := CHR(10) ||

                 'APPS_LOGIN_ID="$1"'||CHR(10)||

                 'XX_LOGIN_ID="$2"'||CHR(10)||CHR(10)||CHR(10)||CHR(10);

     UTL_FILE.PUTF(fileHandler, l_string);       

     

   l_string :=  '# *******************************************************************'||CHR(10)|| 

                '#  Check if APPS Login Id is entered else prompt to get it'||CHR(10)|| 

                '# *******************************************************************'||CHR(10)|| 

    'while [ "$APPS_LOGIN_ID" = "" -o `CHKLOGIN "$APPS_LOGIN_ID" "DUAL"` = "NOK" ]'||CHR(10)|| 

    'do'||CHR(10)|| 

        'if [ "$APPS_LOGIN_ID" = "" ];then'||CHR(10)|| 

            'echo " Enter APPS schema Userid/Passwd (apps/apps): "'||CHR(10)||  

            'read APPS_LOGIN_ID'||CHR(10)|| 

        'else'||CHR(10)|| 

            'echo "Enter APPS Userid/Passwd (apps/apps):  "'||CHR(10)||  

            'read APPS_LOGIN_ID'||CHR(10)|| 

        'fi'||CHR(10)|| 

    'done'||CHR(10)|| 

    'APPS_LOGIN=`echo $APPS_LOGIN_ID | cut -d"/" -f1`'||CHR(10)|| 

    'APPS_PWD=`echo $APPS_LOGIN_ID | cut -d"/" -f2`'||CHR(10);

     UTL_FILE.PUTF(fileHandler, l_string);    

     

     

     

     

     l_string := '# *******************************************************************'||chr(10)||

                 '# Creating Concurrent_Prog in Apps'||chr(10)||

                 '# *******************************************************************';

     UTL_FILE.PUTF(fileHandler, l_string);            

     

     -------------------------End Prinitng Header Portion ------------------------------------------ 

   FOR i IN cur_conc_prog

   LOOP

    l_string := NULL;

    l_conc_prog_string := NULL;

    l_conc_prog_func_string := NULL;

    

----------------------------------------------- CREATING RUN SCRIPT-----------------------------------------------------------------------------------------  

        -------------------------------Start Printing Concurrent Program  -------------------------------------------

       

     l_string  := chr(10)||chr(10)|| '# Start Deploying  : *******************************'|| i.conc_prog||'_CC'||'************************************************************';   

     l_string := l_string ||chr(10)||chr(10)||chr(10)||'echo "Downloading ldt of  Concurrent Programs '||i.conc_prog||'_CC.ldt in Apps ..."  | tee -a $LOG_FILE'||CHR(10);

     l_conc_prog_string := l_string ||CHR(10)||'$FND_TOP/bin/FNDLOAD $APPS_LOGIN_ID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ' ||i.conc_prog||'_CC.ldt PROGRAM APPLICATION_SHORT_NAME="'||'XX'||'" CONCURRENT_PROGRAM_NAME="'||i.conc_prog||'"';

                                          

     UTL_FILE.PUTF(fileHandler,chr(10)|| l_conc_prog_string);

     

     /* l_string := (CHR(10)||

       'if [ $? = 0 ];'     ||CHR(10)||            

        'then'              ||CHR(10)||

            'echo "Registration of    conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt successful " | tee -a $LOG_FILE'                       ||CHR(10)||

       'else'               ||CHR(10)||              

            'echo "Error in registration of  conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt Please correct and rerun" | tee -a $LOG_FILE'  ||CHR(10)||

            'exit'          ||CHR(10)||        

        'fi'                ||CHR(10));

     

      UTL_FILE.PUTF(fileHandler, l_string);*/  

      -------------------------------END Printing conc_prog  -------------------------------------------       

      l_cnt := l_cnt + 1;  

   END LOOP;

    UTL_FILE.PUTF(fileHandler, chr(10)||chr(10)|| '#Number Of Concurrent Program Downloaded Succesfully :-'||l_cnt);

    UTL_FILE.FCLOSE(fileHandler);

END;

Oracle Apps Query : Query To get item details

 

select segment1, description,  item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT,

       -- category_set_name,

        max(Inventory) Inventory ,

        max(Product_Family) Product_Family,

        max(MRP) MRP,

        max(CST) CST,

        max(PUR) PUR,

        max(PRAMAC_PRODUCT_LINE) PRAMAC_PRODUCT_LINE,

        max(QUALITY) QUALITY,

        max(SALES) SALES,

        max(INTRASTAT_Classification)  INTRASTAT_Classification ,

        max(Sales_Category_Subcategory ) Sales_Category_Subcategory,

        max(PRAMAC_CLASS_MAPPING ) PRAMAC_CLASS_MAPPING,

        max(MANUFACTURED_IN )MANUFACTURED_IN ,

        max(LEAN) LEAN,

        max(R&D_MAINTENANCE_BOM) R&D_MAINTENANCE_BOM,

        max(Product_Categories) Product_Categories,

        max(Sequence_of_Events)Sequence_of_Events,

        max(Product_Family1) Product_Family1

from 

(

SELECT distinct null source, A.ORGANIZATION_ID, a.segment1, a.description,  NULL long_description, a.PRIMARY_UNIT_OF_MEASURE, null templates,

a.item_type,inventory_item_status_code,A.INVENTORY_ITEM_FLAG,A.STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

        ,b.category_set_name,a.inventory_item_id,

        decode ( b.category_set_name,'Inventory',b.SEGMENT1) Inventory,

        decode ( b.category_set_name,'Product Family',b.SEGMENT1) Product_Family,

        decode ( b.category_set_name,'MRP',b.SEGMENT1) as "MRP",

        decode ( b.category_set_name,'CST',b.SEGMENT1) as "CST",

        decode ( b.category_set_name,'PUR',b.SEGMENT1) as "PUR",

        decode ( b.category_set_name,'PRAMAC PRODUCT LINE',b.SEGMENT1) as "PRAMAC_PRODUCT_LINE",

        decode ( b.category_set_name,'QUALITY',b.SEGMENT1) as "QUALITY",

        decode ( b.category_set_name,'SALES',b.SEGMENT1) as "SALES",

        decode ( b.category_set_name,'INTRASTAT Classification',b.SEGMENT1) INTRASTAT_Classification,

        decode ( b.category_set_name,'Sales Category & Subcategory',b.SEGMENT1) Sales_Category_Subcategory,

        decode ( b.category_set_name,'PRAMAC_CLASS_MAPPING',b.SEGMENT1) PRAMAC_CLASS_MAPPING,

        decode ( b.category_set_name,'MANUFACTURED IN',b.SEGMENT1) MANUFACTURED_IN,

        decode ( b.category_set_name,'LEAN',b.SEGMENT1) LEAN,

        decode ( b.category_set_name,'R&D MAINTENANCE BOM',b.SEGMENT1) R&D_MAINTENANCE_BOM,

        decode ( b.category_set_name,'Product Categories',b.SEGMENT1) Product_Categories,

        decode ( b.category_set_name,'Sequence of Events',b.SEGMENT1) Sequence_of_Events,

        decode ( b.category_set_name,'Product Family',b.SEGMENT2) Product_Family1        

  FROM mtl_system_items_b a, mtl_item_categories_v b

 WHERE 1 = 1

   AND a.inventory_item_status_code = 'Active'

   AND a.inventory_item_id = b.inventory_item_id

   AND a.organization_id = b.organization_id

   AND a.organization_id = 11

   ) xx

group by segment1, description, xx.inventory_item_id, item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

OAF : How to add Attachment Link in OAF Page through Personlization : Oracle APPS

How to create Attachment link through OAF Personalization

 

OAF Page for Customization = /oracle/apps/per/selfservice/extrainfotype/webui/EitPG

 

1)      Define Entity

a)      Go to Applciation Developer

a)      Enter Entity Data

Table := XX_PER_ARREAR_UPLOAD_STG

Entity Id := PERSON_ID

Entity Name := PERSON_ID

Prompt := PERSON_ID

Application := Human Resources

 

1)     2)   OAF Personalization to create Attachment Link

a)      a) Add Attachment Link Attribute

b) Entity mapping

c) Entity Primary Key mapping with VO Attribute

d) Fields added as shown below

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

Below Steps need to perform at Database

 

1)

CREATE TABLE XX_PER_ARREAR_UPLOAD_STG

(PERSON_ID NUMBER,

MEDIA_ID VARCHAR2(100),

FILE_NAME VARCHAR2(240),

message varcha2(2000)

);

6) 2) Create Procedures :- XX_PER_ARREAR_FILE_UPLOAD

3) 3) Check correct oracle directory and define in procedure 

8) 4) Run the Scripts to upload file

dPProcedure :- XX_PER_ARREAR_FILE_UPLOAD

CREATE OR REPLACE PROCEDURE XX_PER_ARREAR_FILE_UPLOAD

(P_EMP_NO VARCHAR2)

IS

 l_rowid                  ROWID;

   l_attached_document_id   NUMBER;

   l_document_id            NUMBER;

   l_media_id               NUMBER;

   l_category_id            NUMBER;

   l_pk1_value              fnd_attached_documents.pk1_value%TYPE ;

----<Primary Key information that uniquely identifies the product (such as the product_ID)>;

   l_description            fnd_documents_tl.description%TYPE := 'Arrear Attachment';

   l_filename               VARCHAR2 (240)                    := p_emp_no||'.xls';

   --l_file_path              VARCHAR2 (240)                          := 'test';

             ---'SALE_INVOICE_PATH'; --Server Directory Path for upload files

   l_seq_num                NUMBER;

   l_blob_data              BLOB;

   l_blob                   BLOB;

   l_bfile                  BFILE;

   l_byte                   NUMBER;

   l_fnd_user_id            NUMBER;

   l_short_datatype_id      NUMBER;

   x_blob                   BLOB;   

   fils                     BFILE;

   blob_length              INTEGER;

   l_entity_name            VARCHAR2 (100)                    := 'PERSON_ID';

   l_oracle_directory varchar2(100):= 'ECX_UTL_LOG_DIR_OBJ';

   l_content_type varchar2(100):= 'application/vnd.ms-excel';

                                 --application/vnd.ms-excel--application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

                                                           --- <entity_name>;

   l_category_name          VARCHAR2 (100)                      := 'Miscellaneous';--XX_ARREAR_FILE_UPLOAD';

                                                           --<category_name>;

l_message varchar2(2000);                                                           

BEGIN

  bEGIN

  select DISTINCT PERSON_ID INTO l_pk1_value from per_all_people_f

   where employee_number =P_EMP_NO;

  END;

  insert into  XX_PER_ARREAR_UPLOAD_STG (person_id) values(l_pk1_value);

  

  --fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

   SELECT fnd_documents_s.NEXTVAL

     INTO l_document_id

     FROM DUAL;

   SELECT fnd_attached_documents_s.NEXTVAL

     INTO l_attached_document_id

     FROM DUAL;

   SELECT NVL (MAX (seq_num), 0) + 10

     INTO l_seq_num

     FROM fnd_attached_documents

    WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;

-- Select User_id

   SELECT user_id

     INTO l_fnd_user_id

     FROM apps.fnd_user

    WHERE user_name = 'TECHHARDS';                        -- <user_name>;

-- Get Data type id for Short Text types of attachments

   SELECT datatype_id

     INTO l_short_datatype_id

     FROM apps.fnd_document_datatypes

    WHERE NAME = 'FILE';

-- Select Category id for Attachments

   --FATEMP

   SELECT category_id

     INTO l_category_id

     FROM apps.fnd_document_categories_vl

    WHERE user_name = l_category_name;

-- Select nexvalues of document id, attached document id and

-- l_media_id

   SELECT apps.fnd_documents_s.NEXTVAL, apps.fnd_attached_documents_s.NEXTVAL

--apps.fnd_documents_long_text_s.NEXTVAL

   INTO   l_document_id, l_attached_document_id

--l_media_id

   FROM   DUAL;

   SELECT MAX (file_id) + 1

     INTO l_media_id

     FROM fnd_lobs;

   fils := BFILENAME (l_oracle_directory, l_filename);

   --dbms_output.put_line('fils:'||fils);

-- Obtain the size of the blob file

   DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

   blob_length := DBMS_LOB.getlength (fils);

   DBMS_LOB.fileclose (fils);

  

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

   INSERT INTO fnd_lobs

               (file_id, file_name, file_content_type, upload_date,

                expiration_date, program_name, program_tag, file_data,

                LANGUAGE, oracle_charset, file_format

               )

        VALUES (l_media_id, l_filename, /*'text/plain',*/ l_content_type,

                                                        --'application/pdf',--

                                                                            SYSDATE,

                NULL, 'FNDATTCH', NULL, EMPTY_BLOB ()

                ,'US', /*'UTF8'*/

                                /* 'AR8MSWIN1256' */ 'WE8MSWIN1252',--'AR8ISO8859P6',

                                 'binary'

                                                                    /*'text'*/

               )

     RETURNING file_data

          INTO x_blob;

-- Load the file into the database as a BLOB

   DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

   DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

   DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

   DBMS_LOB.CLOSE (x_blob);

   DBMS_LOB.CLOSE (fils);

   DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

   COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

   fnd_documents_pkg.insert_row

      (x_rowid                  => l_rowid,

       x_document_id            => l_document_id,

       x_creation_date          => SYSDATE,

       x_created_by             => l_fnd_user_id,

                                               -- fnd_profile.value('USER_ID')

       x_last_update_date       => SYSDATE,

       x_last_updated_by        => l_fnd_user_id,

                                               -- fnd_profile.value('USER_ID')

       x_last_update_login      => -1,

       x_datatype_id            => l_short_datatype_id,                -- FILE

       x_security_id            => NULL,

          --<security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

       x_publish_flag           => /*'N'*/ 'Y',

              --This flag allow the file to share across multiple organization

       x_category_id            => l_category_id,

       x_security_type          => /*1*/ 4,

       x_usage_type             => /*'S'*/ 'O',

       x_language               => 'US',

       x_description            => l_filename,           

       x_file_name              => l_filename,

       x_media_id               => l_media_id

      );

     COMMIT;

     fnd_attached_documents_pkg.insert_row

      (x_rowid                         => l_rowid,

       x_attached_document_id          => l_attached_document_id,

       x_document_id                   => l_document_id,

       x_creation_date                 => SYSDATE,

       x_created_by                    => l_fnd_user_id,

                                               --fnd_profile.VALUE('USER_ID'),

       x_last_update_date              => SYSDATE,

       x_last_updated_by               => l_fnd_user_id,

                                               --fnd_profile.VALUE('USER_ID'),

       x_last_update_login             => -1,

       x_seq_num                       => l_seq_num,

       x_entity_name                   => l_entity_name,

       x_column1                       => NULL,

       x_pk1_value                     => l_pk1_value,

       x_pk2_value                     => NULL,

       x_pk3_value                     => NULL,

       x_pk4_value                     => NULL,

       x_pk5_value                     => NULL,

       x_automatically_added_flag      => 'N',

       x_datatype_id                   => 6,

       x_category_id                   => l_category_id,

       x_security_type                 => /*1*/ 4,

       x_security_id                   => NULL,

          --<security ID defined in your Attchmentsssss, SOB ID/ORG_ID..>,

       x_publish_flag                  => 'Y',

       x_language                      => 'US',

       x_description                   => l_filename,         --l_description,

       x_file_name                     => l_filename,

       x_media_id                      => l_media_id

      );

      update XX_PER_ARREAR_UPLOAD_STG

   set media_id = l_media_id,

   file_name = l_filename,

   message='SUCCESSFUL'

   where person_id =l_pk1_value;    

   COMMIT;

   DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

Exception when others then

l_message := SQLERRM;

update XX_PER_ARREAR_UPLOAD_STG

     set media_id = l_media_id,

     file_name = l_filename,message = l_message

     where person_id =l_pk1_value;  

    END;

--=========================================================================

   # OAF , # OAF Personalization , # Add Attachment Link through Personalization , # Oracle Apps , # EBS

SQL: How to Write Insert Statement using Rowtype

SQL: How to Write Insert Statement using Rowtype

  r_mtl_lot_rec := NULL;

               g_l_transaction_id := l_transaction_id;

               r_mtl_lot_rec.transaction_interface_id := l_transaction_id;

               r_mtl_lot_rec.source_code := p_transaction_type;

               r_mtl_lot_rec.source_line_id := l_transaction_id;

               r_mtl_lot_rec.last_update_date := SYSDATE;

               r_mtl_lot_rec.last_updated_by :=gv_user_id;

               r_mtl_lot_rec.creation_date := SYSDATE;

               r_mtl_lot_rec.created_by := gv_user_id;

               r_mtl_lot_rec.last_update_login := fnd_global.login_id;

               r_mtl_lot_rec.lot_number := p_lot_no;

               r_mtl_lot_rec.attribute1 := p_batch_no;

               r_mtl_lot_rec.lot_expiration_date := l_expiration_date;

               r_mtl_lot_rec.transaction_quantity := p_quantity;

               r_mtl_lot_rec.primary_quantity := p_quantity;

               r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;

               DBMS_OUTPUT.put_line ('STEP5');

               INSERT INTO mtl_transaction_lots_interface

                    VALUES r_mtl_lot_rec;

 

WEBADI Tables and Query

 List of WEBADI Tables

1) Integrator

BNE_INTEGRATORS_B

BNE_INTEGRATORS_VL

BNE_INTEGRATORS_TL

Layout

BNE_LAYOUTS_B

BNE_LAYOUTS_VL

BNE_LAYOUTS_TL

Layout Columns

BNE_LAYOUT_COLS

BNE_LAYOUT_COLS_V

Mapping

BNE_MAPPINGS_B

BNE_MAPPINGS_TL

BNE_MAPPINGS_VL

Content

BNE_CONTENTS_B

BNE_CONTENTS_TL

BNE_CONTENTS_VL

Interface

BNE_INTERFACES_B

BNE_INTERFACES_TL

BNE_INTERFACES_VL

--------------SQL Query to get  View and PAckage Name used in integrator--------------

SELECT   xxbits..user_name web_adi, ba.attribute2 "PACKAGE", xxbct.user_name "VIEW"

    FROM bne_integrators_tl xxbits., bne_attributes ba, bne_contents_tl xxbct

   WHERE    SUBSTR (xxbits..integrator_code, 1, LENGTH (xxbits..integrator_code) - 5)

         || '_ATT' = ba.attribute_code

     AND xxbits..application_id = xxbct.application_id

     AND xxbits..LANGUAGE = 'US'

     AND xxbct.LANGUAGE = 'US'

     AND ba.attribute1 = 'PROCEDURE'

     AND xxbits..application_id = ba.application_id

     AND xxbct.content_code =

               SUBSTR (xxbits..integrator_code, 1,

                       LENGTH (xxbits..integrator_code) - 5)

            || '_CNT'

     AND xxbits..user_name LIKE 'XX%'

ORDER BY xxbits..user_name;

Word Meaning

• Conundrum -> Difficult Question
• Fleet -> Lasted for a short period of Time
• Enduring -> Last for a long period of time
• Apprise-> Inform or Tell(Someone)