OPM : Query to get Item wise cost in OPM

 OPM : Query to get Item wise cost in OPM

SELECT   ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group,

         SUM (NVL (cmpnt_cost, 0)) costs

    --ccm.cost_cmpntcls_code,

FROM     cm_cmpt_dtl_vw ccdv,

         cm_cldr_mst_v ccmv,

         cm_cmpt_mst ccm,

         mtl_parameters mp,

         org_organization_definitions ood,

         gl_code_combinations gcc,

         gmf_fiscal_policies gfp

   WHERE 1 = 1

     AND ccdv.period_id = ccmv.period_id

     AND TRUNC (ccmv.start_date) >= :p_start_date  

     AND TRUNC (ccmv.end_date) <= :p_end_date   

     AND ccmv.calendar_code = :p_calendar_code

     AND ccdv.cost_cmpntcls_id = ccm.cost_cmpntcls_id

     AND ccmv.cost_type_id = gfp.cost_type_id

     AND ccdv.organization_id = mp.organization_id

     AND mp.organization_id = ood.organization_id

     AND mp.material_account = gcc.code_combination_id

     AND gcc.segment1 BETWEEN :p_child_low AND :p_child_high

     AND (:p_business_unit IS NULL OR gcc.segment2 = :p_business_unit)

     AND ood.legal_entity = gfp.legal_entity_id

GROUP BY ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group

OAF : Migration Command

 OAF : Migration Command

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxshp/oracle/apps/xx/transaction/webui/ReqCreate.xml -rootdir $JAVA_TOP -userId 1 -username $APPS_LOGIN -password $APPS_PWD -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host =$HOST_NAME)(port = $ORACLE_PORT)))(connect_data = (sid =$ORACLE_SID)))"

How to export OAF Page java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/fnd/wf/worklist/webui/NotifReassignPG -rootdir /a01/appldev/apps/apps_st/appl -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.1.198)(PORT=1500))(CONNECT_DATA=(SID=DEV)))"

TAR File : How to Create TAR FILE

RTF Template Function and XML method TAG

RTF Template Function and XML method TAG

1) How to use NVL function in RTF template (XML Publisher)

<?xdofx:nvl(RISF_VC_LY,0)-nvl(RISF_VC_AC,0)?>

OPM : API : How to upload GMD Specification validity for Customer

How to upload GMD Specification validity for Customer

DECLARE

    --l_customer_spec_vrs      GMD_CUSTOMER_SPEC_VRS%ROWTYPE;

    l_customer_spec_vrs_tbl  GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;

    --l_customer_spec_vrs_out  GMD_CUSTOMER_SPEC_VRS%ROWTYPE;

    x_customer_spec_vrs_tbl  GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;

    l_return_status           CHAR(1);

    l_msg_count               NUMBER;

    l_msg_data                VARCHAR2(2000);

    

BEGIN

    fnd_profile.initialize (2820);

    fnd_global.apps_initialize(user_id =>1142,resp_id =>23805 ,resp_appl_id =>552 );

    fnd_msg_pub.initialize;

    gme_common_pvt.g_error_count := 0;

    gme_common_pvt.set_timestamp;

    gme_common_pvt.g_move_to_temp := fnd_api.g_false; 

    

  

    --l_customer_spec_vrs.spec_vr_id                      := NULL;

    l_customer_spec_vrs_tbl(1).spec_id                         :=12416;--- 12420;

   -- l_customer_spec_vrs.ORG_ID                          := 110;

    l_customer_spec_vrs_tbl(1).cust_id                         := 10988;

    l_customer_spec_vrs_tbl(1).ship_to_site_id                 := NULL;   

    l_customer_spec_vrs_tbl(1).spec_vr_status                  := 100;--700

    l_customer_spec_vrs_tbl(1).delete_mark                     := 0;

    l_customer_spec_vrs_tbl(1).start_date                      := SYSDATE;    

  -- l_customer_spec_vrner_id                        := q.USER_ID;   

    l_customer_spec_vrs_tbl(1).creation_date                   := SYSDATE;

    l_customer_spec_vrs_tbl(1).created_by                      := 1133;

    l_customer_spec_vrs_tbl(1).last_updated_by                 := 1133;

    l_customer_spec_vrs_tbl(1).last_update_date                := SYSDATE;

    l_customer_spec_vrs_tbl(1).last_update_login               := 1133;

   

   apps.GMD_SPEC_VRS_PUB.CREATE_CUSTOMER_SPEC_VRS

                                    ( p_api_version                            => '1.0'

                                    , p_init_msg_list                          => apps.FND_API.G_FALSE

                                    , p_commit                                 => apps.FND_API.G_TRUE

                                    , p_validation_level                       => apps.fnd_api.g_valid_level_full 

                                    , p_customer_spec_vrs_tbl                  => l_customer_spec_vrs_tbl

                                    , p_user_name                              => 'ALAM'

                                    , x_customer_spec_vrs_tbl                  => x_customer_spec_vrs_tbl

                                    , x_return_status                          => l_return_status

                                    , x_msg_count                              => l_msg_count

                                    , x_msg_data                               => l_msg_data

                                    ); 

                                                                       

        COMMIT;   

        

      dbms_output.put_line( l_customer_spec_vrs_tbl(1).spec_id );

        

dbms_output.put_line('return_status:-----'||l_return_status);

dbms_output.put_line('Msg_Count:--------'|| l_msg_count||','||l_msg_data);

--dbms_output.put_line('Msg_Count:--------'|| l_msg_count);

--dbms_output.put_line('msg_data ---------'||x_msg_data);

--dbms_output.put_line('spec_id-----------'||x_spec);

--dbms_output.put_line('spec_test_id------'||x_spec_tests_tbl);

 

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

  THEN     

    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

      l_msg_data := FND_MSG_PUB.get( p_msg_index => i,

                                p_encoded   => FND_API.G_FALSE

                              );   

       dbms_output.put_line('The API call failed with error '||l_msg_data);

    END LOOP;

  ELSE

    dbms_output.put_line('The API call ended with SUCESSS status');

  END IF;                             

END;

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;