How to divide huge data into batches

Here is my answer on the subject above.

Using an Analytical function – NTILE, I was able to divide big volume of data into multiple batches.

After dividing data into multiple batches, it’s easy to implement multi-threading, without missing any record.

Following query divides Items(for the Org: 489 ) data in to 5 equal batches, and returns MIN and MAX Item ids from each batch.

 select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

     count(*) batch_count, batch batch_num

    from ( select inventory_item_id, NTILE(5) over (order by inventory_item_id) batch

               from mtl_system_items_b

               where organization_id  = 679 -- 048

            )    group by batch

Query Output:

MIN_ITEM_ID	MAX_ITEM_ID	BATCH_COUNT	BATCH_NUM
158281	299596	7092	1
299598	400344	7092	2
509671	589229	7091	4
589230	961093	7091	5
400345	509659	7092	3

I used this batching concept to process my data into multi-threading. Sample code below.

Sample Code:

---

for rec in (select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

count(*) batch_count, batch batch_num

from ( select inventory_item_id, ntile(5) over (order by inventory_item_id) batch

                      from mtl_system_items_b

                      where organization_id  = <489>)         group by batch

) loop

               print.log_message ('batch_num:'||rec.batch_num

                              ||':min_item_id:' ||rec.min_item_id

                              ||':max_item_id:'||rec.max_item_id

                              ||':batch_count:'||rec.batch_count);

               ln_request_id := 0;

begin

               -- submit programs in batchhes..

      ln_request_id :=

               fnd_request.submit_request (

                   application   => 'XXCUST',

                   program       => 'concp_short_name',

                   argument1     => xv_item_code,

                   argument2     => rec.min_item_id,

                   argument3     => rec.max_item_id,            

                   argument4     => xn_org_id          

                   );

              

commit;

end loop ;

--

BI Publisher Bursting - Delivering/sending emails conditionally

Here is the sample code to send emails conditionally from BI/XML Publisher bursting feature.

Problem Statement:

Generate payslips for all the employees at one shot and email the payslips to respective employee.

1. Alter EMP table and added a column called email to hold emails of each individual employees.

And then update email column to add email Ids where you want your emails to be delivered.

2. Create a Data template Sample code available here

3. Default package Sample code available here

4.Define a concurrent program and assign the concurrent program to a responsibility as per your convenience.

5. Design a simple RTF template and register the same. Sample template available here

6. Register data template, created in #1 above

7. Register control file for bursting. Sample code available here

Package Sample code here.

8. Submit the concurrent program defined in #4. If everything goes fine,
you should be able to see emails delivered at the email ids provided.

Sample Data in XML format.

9. Sample output below..

Extensible Atrributes

Here is the query to list out all extensible attribute group names, attribute names and its data types.

SELECT   *
    FROM (SELECT --egoattributeeo.attr_id,
                 egoattributeeo.application_id,
                 egoattributeeo.attr_group_type,
                 egoattributeeo.attr_group_name,
                  egoattributeeo.attr_name,
                 egoattributeeo.attr_display_name,
                 decode(egoattributeeo.data_type_code,'C','CHAR','N','NUM','X','DATE') data_type_code,
                 egoattributeeo.DEFAULT_VALUE,
                 egoattributeeo.value_set_name,
                 egoattributeeo.maximum_size,
                  egoattributeeo.enabled_flag,
                  egoattributeeo.required_flag,
                 egoattributeeo.database_column,
                 egoattributeeo.read_only_flag
            FROM ego_attrs_v egoattributeeo, ego_fnd_dsc_flx_ctx_ext ext
           WHERE egoattributeeo.application_id = ext.application_id
             AND egoattributeeo.attr_group_type =
                                                ext.descriptive_flexfield_name
             AND egoattributeeo.attr_group_name =
                                             ext.descriptive_flex_context_code) qrslt
   WHERE (application_id = AND attr_group_type like '%'
        )
ORDER BY attr_group_type, attr_group_name



-- All the attribute groups --
SELECT FL_CTX_EXT.ATTR_GROUP_ID ATTR_GROUP_ID ,
FL_CTX.APPLICATION_ID APPLICATION_ID ,
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME ATTR_GROUP_TYPE ,
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME ,
TL.DESCRIPTIVE_FLEX_CONTEXT_NAME ATTR_GROUP_DISP_NAME ,
TL.DESCRIPTION DESCRIPTION ,
FL_CTX.ENABLED_FLAG ENABLED_CODE ,
L1.MEANING ENABLED_MEANING ,
FL_CTX_EXT.MULTI_ROW MULTI_ROW_CODE ,L2.MEANING MULTI_ROW_MEANING ,
FL_CTX_EXT.VIEW_PRIVILEGE_ID VIEW_PRIVILEGE ,
FUNC_VIEW_TL.USER_FUNCTION_NAME VIEW_PRIVILEGE_NAME ,
FL_CTX_EXT.EDIT_PRIVILEGE_ID EDIT_PRIVILEGE ,
FUNC_EDIT_TL.USER_FUNCTION_NAME EDIT_PRIVILEGE_NAME ,
FL_CTX_EXT.AGV_NAME AGV_NAME ,
FL_CTX_EXT.REGION_CODE REGION_CODE ,
FL_CTX_EXT.BUSINESS_EVENT_FLAG BUSINESS_EVENT_FLAG ,
L3.MEANING BUSINESS_EVENT_MEANING ,
'N' IS_EDITABLE ,
FL_TL.TITLE AGT_DISP_NAME ,
FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG PRE_BUSINESS_EVENT_FLAG ,
L4.MEANING PRE_BUSINESS_EVENT_MEANING ,'N' IS_DELETEABLE
FROM FND_DESCR_FLEX_CONTEXTS FL_CTX ,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT ,
FND_DESCR_FLEX_CONTEXTS_TL TL ,
FND_DESCRIPTIVE_FLEXS_TL FL_TL ,
FND_LOOKUP_VALUES L1 ,
FND_LOOKUP_VALUES L2 ,
FND_LOOKUP_VALUES L3 ,
FND_LOOKUP_VALUES L4 ,
FND_FORM_FUNCTIONS_TL FUNC_VIEW_TL ,
FND_FORM_FUNCTIONS_TL FUNC_EDIT_TL
WHERE FL_CTX.APPLICATION_ID = FL_CTX_EXT.APPLICATION_ID AND
FL_CTX.APPLICATION_ID = TL.APPLICATION_ID AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
TL.LANGUAGE = USERENV('LANG') AND FL_TL.LANGUAGE = USERENV('LANG') AND
L1.LOOKUP_TYPE = 'YES_NO' AND
L1.LOOKUP_CODE = FL_CTX.ENABLED_FLAG AND
L1.LANGUAGE = USERENV('LANG') AND
L1.VIEW_APPLICATION_ID = 0 AND
L2.LOOKUP_TYPE = 'YES_NO' AND
L2.LOOKUP_CODE = FL_CTX_EXT.MULTI_ROW AND
L2.LANGUAGE = USERENV('LANG') AND
L2.VIEW_APPLICATION_ID = 0 AND
L3.LOOKUP_TYPE(+) = 'YES_NO' AND
L3.LOOKUP_CODE (+)= FL_CTX_EXT.BUSINESS_EVENT_FLAG AND
L3.LANGUAGE (+)= USERENV('LANG') AND
L3.VIEW_APPLICATION_ID(+) = 0 AND
L4.LOOKUP_TYPE(+) = 'YES_NO' AND
L4.LOOKUP_CODE (+)= FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG AND
L4.LANGUAGE (+)= USERENV('LANG') AND
L4.VIEW_APPLICATION_ID(+) = 0 AND
FL_CTX_EXT.VIEW_PRIVILEGE_ID = FUNC_VIEW_TL.FUNCTION_ID(+) AND
FUNC_VIEW_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.EDIT_PRIVILEGE_ID = FUNC_EDIT_TL.FUNCTION_ID(+) AND
FUNC_EDIT_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE NOT IN ('ItemDetailImage', 'ItemDetailDesc')
--and FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'PIM_CLF'

When updating Supplier Address, oracle.apps.ap.supplier.event was not raised

Problem: Business event "oracle.apps.ap.supplier.event" may not be raised when you updating Supplier Address at site level.

Solution:  You need to check the checkbox 'Update to all sites using this address'.

This business event "oracle.apps.ap.supplier.event" will be raised when you do following actions on suppliers/vendors
- Create Vendor
- Update Vendor
- Create Vendor Site
- Update Vendor Site
- Create Vendor Contact
- Update Vendor Contact
- Update Address Assignments

Critical Tasks in Project Management - typically ERP Implementation

Hi All,

I have put this list together from my past and previous experience and exposure as Project Lead and Manager. Many of these may or may not be applicable depending Project type, Rollout or upgrade, Implemetation. But ideally these points mentioned below are usually missed and or not under consideration until the situation arises. But project management practise says that one should always be prepared for the worst even before it shows up at the door step. I have seen on several occasions when we as a team had to go back and review the strategy to mitigate the risk of missing the timeline

Project plan should include the below activities

1 Check for prerequisite version of 11i needed for R12 upgrade

2 DB upgrade for supporting/as needed to 10g or 11g

3 Server capacity ( RAM, HDD) increase as needed to support 10g or 11g accordingly

4 Necessary pre-requisite patches to be applied for R12 as given for the corresponding version and research on the new patches released for that version and modules related.

5 Data Conversion/Migration planning

6 Instance planning and availability plan

7 Requirement gathering

8 Requirement signoff

9 Module Related Patches Verification

10 Cut Over Activities

11 Seeded Functional Setup steps.

12 Custom components functional design documents

13 Data Conversion/Migration Functional Design

14 Customization Technical design documents

15 Data Conversion/Migration Technical Design

16 Code Build

17 Unit Testing

18 System Integration Testing

19 User acceptance testing

20 Regression Testing

21 Volume Testing

22 Pre Production activities

23 Cut Over Activities

To top it all these should have bookmarks in the Risk Management document. Ideally each step can be flagged off red depending on the stage or the current status of the project affairs.

Finding Guarantors Case Folder for a Parent case folder

Below is the query to find Guarantors/Child case folder for a Parent/Dealer case folder along credit analyst assigned to it.



select child_cf.case_folder_number child_case_folder, parent_cf.case_folder_number  parent_case_folder,  res.source_name
from
ar_cmgt_credit_requests re,
ar_cmgt_case_folders child_cf,
ar_cmgt_case_folders parent_cf,
jtf_rs_resource_extns res
where
parent_credit_request_id = parent_cf.credit_request_id
--and parent_cf.case_folder_number = '14225'
and res.source_name = 'Credit Analyst Name Here'
and parent_cf.review_type is not null
and res.resource_id = parent_cf.credit_analyst_id
and child_cf.credit_request_id = re.credit_request_id
and child_cf.review_type is not null
order by 2 desc

Finding Credit Analyst Name in Oracle Credit Management

-- To find Credit analyst name for the given case folder name. ------

select source_name credit_analyst_ name, source_job_title Job_Title, case_folder_number, usr.user_name User, usr.description
from jtf_rs_resource_extns res ,
ar_cmgt_case_folders cf,
fnd_user usr
where
res.resource_id = cf.credit_analyst_id
and case_folder_number = '' 
and res.user_id = usr.user_id


  -- ====== to find out credit analyst  assigned to Rule Name -----

select rule_name, result_value , usr.user_name credit_analyst_name , res.resource_id
from fun_rule_details  fun, jtf_rs_resource_extns res, fnd_user usr
where rule_name = '' and
fun.result_value = res.resource_id
and res.user_id = usr.user_id