Workflow Queries to debug the Errors

Execute Below query to identify the errors why the workflow is struck 

SELECT

   workflowitemeo.item_type,

   workflowitemeo.item_key,

   workflowitemeo.begin_date,

   workflowitemeo.end_date,

   workflowitemtypeeo.display_name,

     ias.error_name error_name,

   ias.error_message error_message,

   ias.error_stack error_stack 

FROM

   wf_items workflowitemeo,

   wf_item_types_vl workflowitemtypeeo,

   wf_activities_vl activityeo,

   wf_item_activity_statuses ias 

WHERE

   ias.activity_status = 'ERROR' 

   AND ias.item_type = workflowitemeo.item_type 

   and workflowitemeo.item_type ='OEOL'

   AND ias.item_key = workflowitemeo.item_key 

   AND workflowitemeo.item_type = workflowitemtypeeo.name 

   AND activityeo.item_type = workflowitemeo.item_type 

   AND activityeo.name = workflowitemeo.root_activity 

   AND activityeo.version = workflowitemeo.root_activity_version 

 --  AND workflowitemeo.begin_date > SYSDATE - 1 

   AND workflowitemeo.item_type <> 'WFERROR'

 --  and workflowitemeo.item_key='485787'

 order by begin_date desc

FNDLOAD to download the FND attachments defined in application developer

•  FNDLOAD <uname>/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct ABC.ldt FND_ATTACHMENT_FUNCTIONS FUNCTION_NAME="ARXTWMAI"

• FNDLOAD <uname>/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct TTTTT.ldt FND_ATTACHMENT_FUNCTIONS FUNCTION_NAME="ARXTWMAI"

• FNDLOAD <uname>/<pwd> O Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct test1.ldt FND_ATTACHMENT_FUNCTIONS APPLICATION_SHORT_NAME="AR"

• FNDLOAD <uname>/<pwd> O Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct test6.ldt FND_ATTACHMENT_FUNCTIONS APPLICATION_SHORT_NAME="AR" FUNCTION_NAME="ARXTWMAI"  FND_DOCUMENT_ENTITIES ="RA_CUST_TRX" FND_ATTACHMENT_BLOCK_ENTITIES ="RA_CUST_TRX"

 How to direct the outputfile of the concurrent request to unix server path using XMLP Bursting  ORACLE APPS.

Step 1: In the DATA XML definition you need to define an element which contains the unix server path where you want to redirect the outputfile of the BI Publisher concurrent program output to .

Example: Define an element CF_PATH='/usr/tmp/kalyani'

Step 2: Define the Bursing control file as follows which contains the element CF_PATH.

In the below TESTFINV is the module name of the report or the main group, you need to find out the group from the data xml output from  Diagnostics->View XML of the BIP concurrent program.

<?xml version="1.0" encoding="ISO-8859-1" ?> 

<xapi:requestset type="bursting" xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

<xapi:request select="/TESTFINV/LIST_G_ORDER_BY/G_ORDER_BY">

<xapi:delivery>

<xapi:filesystem id="FILE_DELIVERY" output="${CF_PATH}"/>

</xapi:delivery>

<xapi:document output="${CF_PATH}" delivery="FILE_DELIVERY" output-type="pdf">

<xapi:template type="rtf" filter="" location="xdo://CAR.TESTFINV_NEW.en.US?getSource=true"/>

</xapi:document>

</xapi:request>

</xapi:requestset>

Views of Women in the Society --Women Vs Women

                                 Indian Women and their mentalities--Women Vs Women

I am here to say few words, which are being told by a women to women 

1.If a wife is multitasking and lives with in laws, they say that she's got  in-laws support,

2.If wife and husband are jovial, friendly and cheerful and light hearted ,then also they claim you have got a supportive husband.

So, finally what I don't understand is , dont you think women has no role to play for the happiness that she is getting? That case every women in the world should be happy provided she just gets henpecked husband, no matter how she is,but you see that's not how it is in society.

Always a happy and high spirited family, or happy couple has equal share in making things work, no one is MORE and no one is LESS.

For instance,  Most of the women does not have time to spend with their spouses or families, they give importance to their own priorities.

You remember that Bharathiyudu movie, where kamal hasan wife packs her bags with just the glance of her husband, if both of them have that understanding and able to spend time with each other irrespective of excuses then everyone can be an ideal couple.

People don't understand all these things and just blabber that you got a supportive husband or supportive in-laws or supportive kids

Queries related to concurrent programs, request sets and programs attached to responsibilities

SQL Query to find Request Set for the given concurrent program

SELECT

   DISTINCT  user_request_set_name

FROM

    fnd_request_sets_tl rs,

    fnd_request_set_programs rsp,

    fnd_concurrent_programs_tl cp

WHERE

    rsp.concurrent_program_id = cp.concurrent_program_id

    AND   rs.request_set_id = rsp.request_set_id

    AND   upper(user_concurrent_program_name) = upper('Program name') 

Query to find the list of responsibilities to which the concurrent program is attached 

SELECT frt.responsibility_name, frg.request_group_name,frgu.request_unit_type,frgu.request_unit_id,

    fcpt.user_concurrent_program_name

    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,

    fnd_request_groups frg, fnd_request_group_units frgu,

    fnd_concurrent_programs_tl fcpt

    WHERE frt.responsibility_id = fr.responsibility_id

    AND frg.request_group_id = fr.request_group_id

    AND frgu.request_group_id = frg.request_group_id

    AND fcpt.concurrent_program_id = frgu.request_unit_id

    AND frt.LANGUAGE = USERENV('LANG')

    AND fcpt.LANGUAGE = USERENV('LANG')

    AND fcpt.user_concurrent_program_name = :conc_prg_name

    ORDER BY 1,2,3,4

Query to find Request set attached to responsibility in oracle apps

SELECT DISTINCT frt.responsibility_name,

   frg.request_group_name,

   frsv.user_request_set_name,

   frsv.request_set_name

         FROM fnd_responsibility fr,

              fnd_responsibility_tl frt,

              fnd_request_groups frg,

              fnd_request_group_units frgu,

              fnd_request_sets_vl frsv

        WHERE fr.request_group_id = frg.request_group_id

          AND fr.responsibility_id = frt.responsibility_id

          AND frg.request_group_id = frgu.request_group_id

          AND frgu.request_unit_id = frsv.request_set_id

          AND frsv.request_set_name=:P_REQUEST_SET_NAME

How to Upgrade or Migrate from Forms and Reports 6i to 12c

 Migrate forms and Reports 6i to 12c in Linux/Unix Environment

1. Ensure the DB and Unix Server are upgraded to 12c version is 12.2.1.19.0

 OS: Oracle Linux Server release 7.9

Database: 19c

2. Once the Environment is set, identify the list of 6i forms to be migrated.

3.First you need to install the forms 10g and Forms 12c,the patch which is available to install Forms and Reports 12c are 

DLP:

Oracle Forms and Reports 12.2.1.19.0 ( Oracle Forms and Reports )

You can download from https://edelivery.oracle.com/osdc/faces/SoftwareDelivery

4.Once you have downloaded and installed you will have frmbld which is the form builder

Forms Migration:

1. First open forms 10g builder and open the 6i form and just save it, without this step if you open directly the 6i form in 12c it will error out and does not allow you to open.

2. Now once the form is saved from 10g builder you can open the form directly in 12c forms builder

3. You need to set the FORMS_PATH just like you set it for forms 10g where all the pll's,olb's and other library files are copied.

4. You need to regenerate or re compile all the 6i plls in 12c and just copy the olb's to the FORMS_PATH

5.All the icons should be copied in icons.jar/frmall.jar file and mention the path of this jar file in the FORMS_PATH in the Linux Server to reflect the icons/images for the buttons.

6.Attach the required pll's in Attached Libraries in the form builder 12c when the form is opened, open the OLB file also if its reading from OLB and then save and compile and ensure that fmx is generated.

7.To Run the form you need to have the URL 

 http://hostname:port/forms/frmservlet?config=webutil_webstart

8.If you have any login form which has to appear initially then in the URL you need not give the form name ,but you can do the setup 

8.When you run this in the browser it will open the form 

To access Web Configuration page: ---> https://docs.oracle.com/middleware/1221/formsandreports/deploy-forms/configure.htm#FSDEP171 ----> Image is displayed

Access Fusion Middleware Control.

From the Fusion Middleware Control main page, click the Target Navigation link to expose the navigation side panel.

In the tree, expand the Forms node and click on the instance you want to configure.

From the Forms page and in the Forms Deployments table, click on Web Configuration and right corner you will have "Lock and Edit option and click on it"

Click on webutil_webstart and down you will have ADD button. Click on it :

Add the following parameters and it's values :

parameter : form

value: S016

next

parameter : userid

value: <give your userid/password@dbconnect string>

Apply and in right top corner you will have "Activate Changes" --Click on it.

Now access your forms URL just until ---> http://hostname:port/forms/frmservlet?config=webutil_webstart

This should solve this issue

9. If you want to download or upload the files from local machine from your forms then you should use CLIENT_TEXT_IO and CLIENT_GET_FILE_NAME builtin functions in 12c forms, for that webutil.pll and webutil.olb is must.If the webutil is not working for some reasons then you can follow the document How to Configure Webutil in Forms 12c (Doc ID 2070183.1)

How to select the minimum row or the first row num or row with minimum value in an SQL query

 select 'person_id:'||ppx.person_id

from per_people_x                   ppx

,    fnd_lookup_values_vl           flv

,    gl_code_combinations           gcc

,    ap_invoice_lines_all           aila    

,    ap_invoice_distributions_all   aid

where 1=1

and aid.distribution_line_number=1

and   ppx.person_id                = TO_NUMBER(flv.attribute1)

and   flv.lookup_type              = 'TEST'

and   flv.lookup_code              = gcc.segment3

and   aila.invoice_id              = aid.invoice_id

and   gcc.code_combination_id      = aid.dist_code_combination_id  

and   aid.line_type_lookup_code    = 'ITEM'

and   aila.discarded_flag           = 'N'

and   flv.ENABLED_FLAG             = 'Y'

and   aila.invoice_id               = :transactionId

AND aid.INVOICE_LINE_NUMBER = aila.LINE_NUMBER

order by aila.line_number asc FETCH FIRST 1 ROW ONLY

Can Women be still successful even if there is no understanding husband behind her

             Behind every successful Women there is an understanding Husband

We have been hearing a lot about Samantha and Naga Chaitanya's divorce and each one of us judge this issue in our own way, so do you think Naga Chaitanya is not understanding Husband? do you think So Samantha cannot be successful ??

Below is my article about what do we actually mean by understanding Husband or Wife.

We hear popular quotes from famous personalities that "Always behind every successful women there is an understanding husband" I say its absolutely true for Indians,  because this is how is our culture and this is how we are being brought up and developed by the society.

Unfortunately I have to say that it is not Women's capability or intelligence that will make her successful, but its just THE " " understanding husband or understanding in-laws who can make their life.

For instance, if a women tries to take care of in-laws in every possible aspect and tries to keep them comfortable, strain free and happy by taking care of even the minutest needs of them ,respects them and sees that they are not burdened at all to stay with their son and daughter in law, then the society unabashed says that aah see how understanding in-laws she got!!. 

But believe me the reverse never happens ,where one can hear that they got supporting daughter in law. Not to blame any one!!!, because society sees the major chunk of people and decides that only a supporting in-laws are possible but can never be vice versa. 

Even though the entire family are supporting and understanding to each other, but still the word goes like this "she has got a supporting in-laws at home and hence she is successful".

If the husband is taking care of the family very well , spends time with the kids and wife and is a jocular person, then the society again credits the women that she has got an understanding and supporting husband. No one confesses that without a women supporting her man with peaceful environment and supporting him hand in hand and taking care of other small things at home and make a lively and joyful space for him, it is not possible to become a supporting husband. Also I say that we are all part of family and hence its everyone's responsibility to take care of each other, spend time for each other and at the end of the day you get happiness, but then what is that extra qualification we are giving to man by saying supporting husband "". 

Why don't we say the same to women that someone has got supporting wife??. Because we in the society feel that, wife being supporting is not added qualification to her, infact its her duty, responsibility and she is not doing any big deal by being supporting to the family .

But the same is an added qualification for Man in society.

I can say that it is very difficult to find pragmatic society anywhere.

If we think good, do good and chose right path and be moral in our decisions then I hope that every husband will become an understanding husband and every in-laws will become understanding in-laws and at the end every women become successful.

Said and Done all these points, finally I feel that there is nothing called as understanding or supporting Husband or understanding and support Wife to make a Man successful or Women Successful, Its ONLY because of mutual supporting and understanding and more than that being very flexible for each other is that a Man or Women or a husband and wife can become Successful, it can never ever be only one way .

Of course at the end of day we see different kind of people with different mentalities, the statement I pass might not suit to everyone, but if we take in general this is what is being observed everywhere and that is what I tried to present here in this article.

How to Convert Oracle Discoverer reports to BI Publisher reports

 Below are the details about how we can convert the oracle discoverer reports to BI Publisher reports.

1. Login to Oracle Discoverer with the corresponding user details and request the Administrator to assign the required responsibilities needed to view the reports.

2.After the login page you will be directed to select responsibility page where you need to select the responsibility and click on continue button.

3.Click on browse button below to search the report

4.You will find the list of all the reports ,select any report and click on open button

5.You will find the parameter list if the report has parameters, select the values in the parameters and the report will run and produce output

6.Now click on Tools Menu -> Show SQL to get the query written behind the report.

7.Copy the SQL Query , for additional conditions or calculations there are tab pages under available items on top left, you can verify for all those conditions.

8.Now create data xml with the same parameter list as you have seen above and use the query to create select statement in your data xml for BI Publisher report. Add the additional conditions noted in above steps to this select statement and also you can add the conditions.

9.As the Oracle discoverers are excel outputs, you can either build excel layouts or PDF layouts based on the requirement.

Query to get the All Supplier, Site and Bank Details at Site Level with or without bank setup at site level

Below query is used to retrieve the supplier, site and bank details of all the suppliers  belonging to particular org_id, also to retrieve the supplier and site information if no bank account is setup for a particular site. 

If a site does not have any bank account setup then only supplier and site details are displayed.

SELECT * FROM (select  distinct 

    asp.vendor_name "VENDOR NAME",

    asp.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE" ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    NULL "ACCOUNT NUMBER",

    NULL "ACCOUNT NAME",

    NULL "Bank Account Type",

    NULL ext_bank_account_id,

    NULL bank_account_num,

    NULL  ext_bank_account_iban_number,

    NULL bank_account_name,

    NULL bank_account_name_alt,

    NULL bank_account_num_electronic,

    NULL  bank_account_end_date,

   NULL bank_branch_name,

   NULL bank_branch_name_alt,

   NULL branch_number,

   NULL branch_party_id,

   NULL bank_name_alt,

   NULL bank_name,

   NULL bank_number

FROM ap_supplier_sites_all assa,

  apps.ap_suppliers asp

 WHERE assa.vendor_id              = asp.vendor_id

and asp.segment1 = NVL(:P_VENDOR_NUMBER,aps.segment1)

and assa.org_id=:P_ORG_ID

MINUS

SELECT

/*Supplier Information*/  

distinct 

    aps.vendor_name "VENDOR NAME",

    aps.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE"   ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    ieb.bank_account_num "ACCOUNT NUMBER",

    ieb.bank_account_name "ACCOUNT NAME",

    ieb.bank_account_type "Bank Account Type",

    ieb.ext_bank_account_id,

    ieb.bank_account_num,

    ieb.iban ext_bank_account_iban_number,

    ieb.bank_account_name,

    ieb.bank_account_name_alt,

    ieb.bank_account_num_electronic,

    ieb.end_date bank_account_end_date,

    ext_branch.bank_branch_name,

    ext_branch.bank_branch_name_alt,

    ext_branch.branch_number,

    ext_branch.branch_party_id,

    ext_bank.bank_name_alt,

    ext_bank.bank_name,

    ext_bank.bank_number

FROM

    apps.hz_parties hzp,

    apps.ap_suppliers aps,

    apps.hz_party_sites site_supp,

    apps.ap_supplier_sites_all assa,

    apps.iby_external_payees_all iep,

    apps.iby_pmt_instr_uses_all ipi,

    apps.iby_ext_bank_accounts ieb,

    iby_ext_banks_v ext_bank,

    iby_ext_bank_branches_v ext_branch

WHERE

        hzp.party_id = aps.party_id

    AND        hzp.party_id = site_supp.party_id

    AND        site_supp.party_site_id = assa.party_site_id

    AND        assa.vendor_id = aps.vendor_id

    AND        aps.enabled_flag ='Y'

    AND      ( aps.end_date_active IS NULL OR aps.end_date_active > SYSDATE)

    AND        iep.payee_party_id = hzp.party_id

    AND        iep.party_site_id = site_supp.party_site_id

    AND        iep.supplier_site_id = assa.vendor_site_id

    AND        ieb.bank_id = ext_bank.bank_party_id

    AND        ieb.branch_id = ext_branch.branch_party_id

   AND        ipi.instrument_id = ieb.ext_bank_account_id

   AND        iep.ext_payee_id = ipi.ext_pmt_party_id  

   and       (assa.inactive_date is null OR assa.inactive_date >SYSDATE  )                             

    AND        assa.org_id = :P_ORG_ID

   AND    aps.segment1= NVL(:P_VENDOR_NUMBER,aps.segment1)

  ) order by 1

 

            Trekkenture Camp @Mathapalli 

                      Siddipet Telangana 

Share in Whatsapp 

On Saturday 13^th November we went to a Trekking camp at Mathappalli village near Siddipet Telangana district which is 80 km from Hyderabad organized by Trekkenture.

Excited for the camp we started from Kukatpally at 11:00 A.M. and ORR, we passed through the famous shamirpet Maisamma temple and reached Mathapalli Village at 1:00 P.M. 

Our Trekking camp is hosted at the large forest kind of space. The Trekking hosts Ajit Sir and Bharathi Mam welcomed us with tilak and flowers wishing for a nice stay over the weekend. Surprise for the kids they got to blast lot of balloons to cheer up the kids, followed by a casual introduction with each other. We all were super hungry after travel of almost 1 and half hour, the lunch provided was more than my expectation as generally in the camp we are more inclined to physical activities than munching on food, but to my surprise the lunch was simply superb with all the delicious items in the menu like paneer, Butter naan, rice, other items and yummy gulab jamun as dessert.

Now its turn to choose our tents which were laid in the lush green meadows and they made choosing tent easy by asking kids to select their tent. We dumped our luggage’s in the tents and they asked us to take a break of 1 hour or so and get ready for trek. We took all the required things like torch, water bottles, hand towels and whatever we need to feel comfortable for almost 3 hours to walk in the woods.

While walking we came across the rocks which looked just like burger with patty in between, kids just loved that burger rock and then we moved on to see some ancient beautifully intricate rock paintings which depicted their hunting scene. Even though this painting was done centuries back but still the pictures were intact. Next we headed deep into the thick trees and meadows to reach the beautiful trekking place, we started to hike the steep hill, kids and adults were like Oh My God can we do this, but we supported each other, had lot of fun while climbing, it was both adventurous and fun to climb with kids. Finally, we made it to the hill top. All the efforts we put in has gone just like a jiffy when we saw the breath taking view from top of the hill. This hill is surrounded by river body with cloudy winters added to this the reddish rays during sunset covered the sky and reflecting in the water made the scene magnificent. It was a breathtaking view for all of us, hold on your breath when I describe you next scene, in this weather what would it be if we crave for a cup of hot tea and snacks to munch on and make the scene unforgettable, don’t you think we are expecting too much, yes it too much but we are so lucky that the Trekkenture people arranged us with nice tea and snacks, kids were overjoyed to enjoy the snacks on hill top there are no words to describe their excitement.

 We returned back to camp from there freshened up thinking for a quite evening, but we did not knew that we had more exciting things to look for, any clues what it is , even we had no clue that we are going to get to see wonderful stargazing in the dark nights .The Trekkenture people got telescope for us , we got to see the Saturn , no wonder when we saw the rings of the Saturn through telescope it was wow moment for us  and for kids it was amazing to see the planets with their eyes and map it with what they saw in books all the time, next planet we got to see was our favourite moon , it looked so different from telescope and the moon was looking spectacular, magnificent and all the words which we can add to describe it, the view of the holes and close look at the moon just made it an experience of astronaut landing on the moon , I am not exaggerating but this is what happened to all of us.

So, we had some fun games where parents and kids were involved we enjoyed those new type of games a lot and then we had an impressive and lavish dinner again out of my expectations, which included roti’s, fries (to enjoy nice chilly weather) , to add to this weather nice rasam made the meal complete followed by double ka meetha sweet. After the dinner we just enjoyed gathering sharing our hobbies , likes with each other at the camp fire and the returned to tent for a good night sleep.

 Next day we woke up at 6:00 A.M and enjoyed the cold misty mornings with dew drops on the plants. We had nice tea, boost and biscuits and surprisingly kids are ready before us for morning walk in the nature, they were more excited than us, so we started off and this time our walk was through nice ponds, water bodies, lakes. When we were passing through greenery we saw many birds like cuckoo, lapwing, ibis, red mantled sallebag, hycleus, wasp, lady birds and many more birds, the Trekkenture hosts had good knowledge about all these birds and we also learnt about different types of trees and their uses from them, it was like nature and biology lessons to kids with fun. We also saw weavers bird nests there were many to amaze us. We also went deep into the woods to find an ancient laksmi narasimha swamy temple , lot of monkey to watch for. Kids enjoyed the morning trek a lot looking at blooming buds, flowers, honey bees , I can say that they were very close and in the lap of nature ,This experience is just rare sight for most of us living in concrete jungles but we are lucky to gather all this memories with us . 

By this time all the kids became good friends of each other and we are back to camp for morning breakfast . It was always exceeded expectations from Trekkenture camp .Good amount of walk through nature increased the appetitie of kids, generally this generation kids when coming to eating they are very choosy and reluctant, but this time it was surprising to all the parents that they had more than what they generally they eat for breakfast. The yummy spread also tickled their taste buds and we enjoyed it like a brunch. 

Finally to conclude we were loaded with good memories, friends and nature experiences, taking back with us for life time from this Trekkenture.

Please follow below link for more details to contact Trekkenture 

 https://telanganatoday.com/promise-of-weekend-adventure-for-both-kids-and-their-parents.

 

How to Verify WEBADI Setup and How to Modify the existing WEBADI setup

                                

     Navigation and Path to verify the custom/core WEBADI setup.

                 1. Navigate to responsibility "Desktop Integration Manager" ->Manage Integrators

                   2. When you click on Manage Integrators and search window open up, Provide  the Integrator name and click on Search in the Integrator Name field

                 3. Click on the pencil Update button for the integrator you wish to see or modify the setup, click on Next button until you see the Interface Attributes for the integrator you selected.

               4. For each field or attribute that you see there are "Default Type" and Default Value".

              5. If you want to make changes to the attribute click on pencil update icon and you will see Interface Attribute Definition page which shows more fields that you an update according to your requirement.

              6.You can attach new LOV, change prompt name, validation type and other fields.

             7.After making the changes click on save button and this saves the Integrator definition and its attribute definitions.

Query to select the Integrator related packages.

SELECT BIT.USER_NAME WEB_ADI, BA.ATTRIBUTE2 "PACKAGE", BCT.USER_NAME "VIEW"
  FROM BNE_INTEGRATORS_TL BIT,
       BNE_ATTRIBUTES BA,
       BNE_CONTENTS_TL BCT
 WHERE --substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_P0_ATT' = BA.ATTRIBUTE_CODE
    BIT.APPLICATION_ID = BCT.APPLICATION_ID
   AND BIT.LANGUAGE = 'US'
   AND BCT.LANGUAGE = 'US'
   AND BA.ATTRIBUTE1 = 'PROCEDURE'
   AND BIT.APPLICATION_ID = BA.APPLICATION_ID
  -- AND BCT.CONTENT_CODE = substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_CNT'
  -- AND BIT.USER_NAME LIKE 'XX Test Create'
  and bit.integrator_code='XX Integrator'
   order by BIT.USER_NAME;

Related Tables:

BNE_INTEGRATORS_B

BNE_INTEGRATORS_TL

BNE_ATTRIBUTES

BNE_CONTENTS_TL

BNE_LAYOUTS_B

 

How to display the OA Framework Page attachment image stored in FND_LOBS table in the BI Publisher report output

 Problem: 

The attachments in the form images that are uploaded from OA Framework Pages will be stored in FND_LOBS tables, How to display these images in the BI Publisher report output.

Solution: 

Step 1: First we need to identify the record in fnd_lobs table .

        select FILE_ID,FILE_NAME ,FILE_DATA    from fnd_lobs where file_id = 695482.

        The file_data column actually contains the image content.

Step 2: Now the data that is stored in file_data column of fnd_lobs table is of type BLOB and we cannot display the BLOB data type directly in BI Publisher reports and we need to convert them to CLOB.

Step 3: create function to convert blob to clob

CREATE OR REPLACE FUNCTION APPS.BLOB_CLOB_TEST(p_source BLOB)
RETURN CLOB IS v_result CLOB;
BEGIN 

DBMS_LOB.createtemporary(lob_loc=>v_result,CACHE=>FALSE,dur=>0);
Wf_Mail_Util.EncodeBLOB(p_source,v_result);
RETURN (V_RESULT);
END BLOB_CLOB_TEST; 

Step 4: Use this function in the below SQL query to get the CLOB data type for column file_data.

            select FILE_ID,FILE_NAME ,BLOB_CLOB_TEST(FILE_DATA) IMAGE_FILE

    from fnd_lobs where file_id = 695482

Step 5: Use the query written in Step 4 in BI Publisher data xml.

Step 6: Display the element IMAGE_FILE in the RTF with below syntax in the field.

        <fo:instream-foreign-object content-type="image/jpg" height="1.25 in" width="1.5                 in">  <xsl:value-of select=".//IMAGE_FILE"/>  

Step 7: Run BI Publisher program and the output will show the image.

Concurrent program end in ERROR with "Program was terminated by signal 11'

  Issue: The concurrent program ends in error and the error message in the log file is "

Program was terminated by signal 11"

Cause of the Issues: This issue is occurred mostly in custom programs or the core programs.

custom programs: The reason for the error in custom programs is if you are calling core API's or submitting the core standard concurrent programs and those programs are upgraded to include additional parameters and if you are not including those parameters in your package or program then you get the error.

Core/Standard Programs: The reason for the error in standard programs is, if you have upgraded only the core programs and not packages, then either the program or the package which is not addressing the additional parameters exists then you get this error.

Solution: Identify the backend programs/packages or scripts which are calling with missing parameters added during changes or upgrade by Oracle and then add those parameters and retest the issue. The issue should be resolved.

Query to find the Operating Unit Name for the Journal Ledger created in Oracle Apps General Ledger

 Below is the query which gives details about legal entity name, ledger name and operating unit name 

SELECT * FROM XLE_LE_OU_LEDGER_V where ledger_id in (select ledger_id from gl_je_headers where name ='Journal Name' and description like 'Journal%Import%230808858%')

How to develop BI Publisher reports with mulitple spread sheets Excel Layout Outputs

       BI Publisher reports  Multiple spread sheet Excel Layout Outputs

Below are the steps to develop the Excel Layouts for BI Publisher report Excel outputs.

1. First open a new Excel sheet.

2. Design the Data XML with Groups and data elements as you create for any other BI Publisher outputs.

3. In the Excel sheet, Name the first spread sheet as 'Customers',the second spread sheet as 'Sites','Site Uses' and Contacts.

4. Navigate to Tool Bar of Excel sheet and click on the Menu Formulas -> Name Manager. Click on New and provide the Name field as 

"XDO_?ACCOUNT_NUMBER?.The name of the element in the data xml should be exactly same as the Name field you give.

In the example: The DATA XML element should be

<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER" />

In Excel sheet field it should be "XDO_?ACCOUNT_NUMBER?

The "Refers to" field will point to the field in the excel sheet where you want the data to appear. You can manually change it to refer to any field in excel sheet.

Note: You should provide a dummy name in the field where you are expecting data to be populated .

5. Once you define all the elements in the group in Excel, now you should define which group all the elements defined in the excel belongs to by clicking Name Manager in tools menu -> New -> Name Field ='XDO_GROUP_?G_CUST_ACCT?' and the Refers to field should define the all the fields from first field to last field.

In the below screenshot it shows that all the elements defined from cell A18 to AM18 belongs to group G_CUST_ACCOUNT.

6. Similarly create another spread sheet called as "Sites", repeat steps 4 and 5 to define new site elements and group which belong to sites.  

Note: The group names and element names should always be same as data xml element names and group names.

7.Finally by default you should create one spreadhseet called as "XDO_METADATA"

7.Save the file as Microsoft Excel 97-2003 Worksheet (.xls)

How to Resolve SQL Injection /Second Order Injection Attack in PL/SQL

When the PL/SQL Code is detected with SQL Injection Error, How to Resolve?

SQL  Injection is simple type of attack and can be avoided with basic coding practices.

Below are some of the ways of best coding practices.

Avoid Dynamic SQL's rewrite them into static SQL.

If you must use dynamic SQL, try not to construct it through concatenation of input values. Instead, use bind arguments.

If you cannot avoid input concatenation, you must validate input values

1. Execute Immediate: This should not be used as it is vulnerable for hacker and hence should be replaced with DBMS_SQL for dynamic execution of the sql queries.

2. Parameters: If the concurrent program is using a procedure which has  a parameter that can be hacked by the user, then you need to sanitize the parameter using .

You can filter the input values using DBMS_ASSERT Functions.

DBMS_ASSERT .QUALIFIED_SQL_NAME is used to verify that the string is a simple SQL name.

DBMS_ASSERT .ENQUOTE_LITERAL should be used if you want to pass the character variable values in quotes. This is used to enclose the string literal within single quotation marks

How to Maintain an Easy Life at Villa Just Like Apartment Life

 

                      HOW TO MAKE LIFE AT EASE @ VILLA

Have you ever experienced the conversation where someone buys a new villa and was excited to share the news with friends or families and the reaction you often see from others is Congratulations!!!!!, We also planned to buy a villa but turns out maintaining a villa was not so easy as we thought it was.

It will be fascinating to buy and become a proud owner of a big house. If you also plan to  manage the big independent home, then it will surely be a magnificent home to live a happy and a healthy life.

When you wish for something then you own it and have a will to find ways to Enjoy.

Below are few easy things that you can plan everyday to  reduce strain

1. Reduce the trips to first floor which needs climbing stairs frequently.

2. Take Bath once a week , in each washroom , this way you can maintain all the washrooms without the need of any maid or help.

3. Have a Basket with rope , this helps to pass on the needed things to upper floors.

4. Book Urban clap for home cleaning every 6 months as a package, this helps to restrain yourself from too much of cleaning ,which might have impact on your health in future.

5. Have a used clothes bin near every wash room.

6. Spend at least 5 minutes of time for each bedroom in the morning which includes quick make over of the bed, arranging misplaced items .

7. Do not use the age old cloth curtains for windows they need lot of maintenance efforts of washing .You can go for latest trend of roller blinds, which only needs dusting and at the same time gives chic look to home with customizations and is also lot maintenance free.

That's all it takes ,bingo!! you are done for the day.

BI Publisher report in R12 Oracle Apps is not calling BeforeReport or AfterReport triggers

            Problem: The BI Publisher report when run is not calling the beforeReport or afterReport triggers                                   Eventhough the data xml contains call of these procedures

dataTemplate name="MAILORD_REP" defaultPackage="EMAIL_PKG" version="1.0">

<properties>

<property name="xml_tag_case" value="upper" />

<property name="debug_mode" value="on" />

</properties>

<dataQuery>

<sqlStatement name="S_ORDERS">

<![CDATA[

SELECT cwp.order_number, oe.orig_sys_document_ref 

,cwp.currency_code,cwp.amount,cwp.date_sent

FROM   oe_order_headers_all oe

WHERE 1 = 1

AND   oe.org_id =745

AND   oe.order_number = cwp.order_number

AND cwp.status = 'ERROR'

AND TRUNC(DATE_SENT) = TRUNC(SYSDATE -1)

ORDER BY cwp.date_sent DESC

]]>

</sqlStatement>

</dataQuery>

<dataTrigger name="beforeReport" source="EMAIL_PKG.beforereport" />

<dataStructure>

<group name="G_ORDERS" source="S_ORDERS">

<element name="ORDER_NUMBER"                     value="ORDER_NUMBER"/>

<element name="ORIG_SYS_DOCUMENT_REF"            value="ORIG_SYS_DOCUMENT_REF"/>

<element name="CURRENCY_CODE"                    value="CURRENCY_CODE"/>

<element name="AMOUNT"                           value="AMOUNT"/>

<element name="DATE_SENT"                     value="DATE_SENT"/>

</group>

</dataStructure>

<dataTrigger name="afterReport" source="EMAIL.afterReport" />

</dataTemplate>

The log file of the concurrent program does not show any log messages added in beforereport and afterreport triggers.

The program is ending normal and the log file is not showing any errors also.

Solution: The reason for not calling any report triggers are because of the missing parameter declaration in the package specification.

You should define all the parameters defined for the concurrent program and token names with the same name in the package specification .

Issue will be resolved

How can one make best out of pandemic situations like COVID

                 MAKING THE BEST OUT OF PANDEMIC(COVID)

Every hundred years or so, a disease such as the Spanish flu, the current corona, etc spreads within humankind and it helps in attaining balance. 

Whether you feel yourself fortunate or unfortunate during such PANDEMICs entirely depends on how you convert this difficult situation for something good.

There are so many issues one needs to balance to make the best out of this. Let us discuss about these issues 

HEALTH ISSUES

       MENTAL ISSUES:  We have to go through lot of mental trauma during such conditions though we are physically healthy.

As we are always at home , we might feel lonely, depressed, saddened or worried about our family members and relatives.

 Rather than sitting worried at home, make most of this time by spending time with kids, parents, speaking to your friends and relatives. Spending time with family releases dopamine which makes you feel happy.        

  Try to use your cognitive thinking for something productive rather than becoming too emotional.

  Engage yourself and pamper yourself, as this is the best time, you have lot of free time to enjoy to the fullest.

         PHYSICAL ISSUES AND RESOLUTIONS

  As we are bound to be at home, we might end up overeating, this leads to health  issues.

 We feel lethargic and do not feel like exercising at home.

  Lot of strain at home especially for women as they keep helping the entire family round the clock. 

 Hold on and breathe, we have a way out of these and it depends on us what you chose to follow, points above or points below.

  

 Change your morning routine for something healthy, practice yoga with family if you are feeling lazy, you can do pranayam, stretching exercises and slowly make a habit of stretching more and this keeps you very healthy.

 Share the household works among all the family members this makes every one understand each other and care for each other.

 Savor nice healthy food at home, this also improves your immunity. Immunity can cure any disease.

        ECONOMY ISSUES AND RESOLUTIONS

 For those who have flexibility of remote working, this situation might not be a big deal. But for others it is bit difficult to gain economically during this time.

    The following tips can help you use the limitless time you now have

1. This is the best time to acquire properties and invest on real estate where you cannot anticipate immediate results but would like to retain for your next generation.
2.  If it is difficult for your employer to give you employment, then work only 3 days a week and think that you are getting time for your life time bank upon that and enjoy, because this time might never come, try to invest your time to improve your own passion. You can earn money but not time, so you should think this as a productive time for yourself.
3. Find out if you can continue your work through online classes/trainings 
4. Its not a big deal if you don't earn money now, as long as you are healthy and able to eat 3 meals a day during such situations . 

        SOCIAL ISSUES AND RESOLUTIONS : 

       The social media does more bad than good, by sharing information that agitates the public.

       Use the media legitimately, only to know the current situation. Make your own decisions accordingly and not just completely blown out of what is being shown by media.            

   Finally I would say that not only this, but any difficult situation in your life. It is up to you how you turn it up to be favorable situation to you.

  Difficult situations always make you more stronger and will eventually lead to a commendable life.