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;