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.

 

How can a couple Enjoy the life along with handling family responsibilities

As we know there is a saying, "when a rose which has only 1 day to live is so beautifully made , then human life which has 100 years should be made more beautiful and joyful and that is absolutely in our hands.

Let us see how we can plan our life .

My Father used to tell me to chose whether you want to work hard for 25 years and enjoy the rest of your life or the other way round. Of course ,I chose the first path and settled well in life. But as the years passed I realized that hard work and fun have no limits, you can enjoy all the 100 years of life, only if you plan a little.

If one is planning for having kids then complete it by 30.

When the kids are toddlers, you could plan your weekends at your in-laws' house or your parents' house. That would be a more comfortable place for kids and as there are people around to take care of your kids, you will have time for yourself.

When the kids are in primary school then you should start training the kids , so that they can start eating their meal at home and make them learn how to follow few etiquette's , this helps you to enjoy restaurants along with kids and not feeling tired taking care of them .

When the kids in upper primary school you can go for long weekends as you have already trained them going out , they will definitely know how to behave and this helps the couple also make epic moments with their kids

When the kids are in high school, you can make international trips, this might help them with their academic knowledge too and at the same time they get to savor the foreign culture.

Now, when your kids grow up to teenage , you can visit resorts, plan for a farm house and try to involve them in farming.

So you have enjoyed all the stages of your child's life along with your life.

This is how a wonderful journey of a family looks like.

So, this was the plan we have been making and it is really splendid

Time is like a tide, it waits for none.

So do not wait for time to make your life beautiful, you must make the best usage of the time which is in your hands and make it beautiful. 

Data not visible in _VL AND _TL tables ORACLE APPS

            HOW TO SELECT DATA FROM _VL TABLES


Some times we will not be able to see the data in _vl tables in TOAD or SQL DEVELOPER when others are able to see the same data.

The reason for this is the USERENV('LANG') is set to 'GB'

When you run query select USERENV('LANG') FROM DUAL you get 'GB'

Actually _vl tables gets the data from _tl tables with condition USERENV('LANG')='US'


Solution:

We can change the NLS Lang parameters.

SQL Developer: Go to SQL Developer -> Tools -> Preferences..>Database ->NLS
Set the Language ='AMERICAN', Territory ='AMERICA' , Date Lanugage ='AMERICAN'

Now you can select data from _vl table


TOAD: Go to Toad -> Database ->Administer->NLS Parameters
Set the NLS_LANGUAGE='AMERICAN' ,NLS_DATE_LANGUAGE ='AMERICAN'

Now you can select data from _vl table

credit card tables in oracle apps

AP_CARD_PROGRAMS_ALL :-This is the Parent Table for the Credit Card tables

AP_CARDS_ALL :-This is the  table of the Credit Cards Holder

AP_CREDIT_CARD_TRXNS_ALL :-This table contains all the credit card transactions information's coming from the banks for the Credit cards under the Credit Card programs all.

How to Define Global Table Types in Forms to be accessed across different triggers

If you want to populate the Table Type variables and access the table types in other triggers then you have to define the Global Table Type in Program units Packages of the form.

(PKG)Package Specification definition:

TYPE gtyp_max_grp_data IS TABLE OF NUMBER  INDEX BY VARCHAR2 ( 100 );

(PKG)Package Body Definition:

CURSOR C1 IS select max(emp_id) from EMP where deptno=100;

BEGIN
   FOR REC1 IN C1 LOOP

--REC1 .emp_name||REC1.EMP_ID-- it is the unique index (i) of the array variable like 
    gt_cache_max_grp_data(i)
     gt_cache_max_grp_data( REC1 .emp_name||REC1.EMP_ID) := REC1.emp_id;

END LOOP;

END;

How to access this table type in another trigger?

l_max_emp_id  :=  PKG.gt_cache_max_grp_data ( :blk_emp.emp_name|| PKG.g_cahched_value_delimiter || :blk_emp.emp_id );


Now the variable l_max_emp_id contains the maximum group id that was populated in the program unit package and now that value we are able to access in when-checkbox-changed trigger or any other trigger

The Unix Host Program is ending in ERROR with file permissions issue

Cause of the Problem:

The reason for the Unix Host Program ending in ERROR, even after providing the permissions to the file is because of the control+M characters added to the unix file when you open it in notepad++.

Solution

1.  Navigate to the Edit menu -> EOL Conversion -> Windows.
2. Save the file .
3. Move the file and run the program , the issue will be resolved

BI Publisher report Number formatting for Numbers like 10.000,00

If you want to print the Numbers in the BI Publisher report output with numbers having ".," format and if that is constant formatting then you should give below syntax to enable the format in RTF for number columns.

where the column "PRICE"  changes according to your column names in the RTF.

ORACLE FINANCIAL TECHNO FUNCTIONAL INTERVIEW QUESTIONS FOR EXPERIENCED

TECHNICAL QUESTIONS
-------------------------------------


1. What is the difference between custom.pll and form personalization.

2. How do you create data links in XML Publish reports in Data definition XML.

3. If you want to find duplicate records in forms without saving data how do you do it?

4. What is bulk collect and bulk exceptions

5. What are all the triggers in the forms

6. What is the significance of  application name in AOL Executable

7.What happens when you freeze and unfreeze flexfields.

8. How do you create notifications using workflow builder.

9. How do you removed empty pages that are appearing in the XML publisher report ouput.

10.What are the available triggers in report builder.

11. Explain the steps for creating the the master details layout using oracle report builder.

12. Name few API's for import programs in AR and AP modules.




FUNCTIONAL QUESTIONS
------------------------------------------


1.what are 3 way matching in p2p cycle.

2.What are the accounting entries at each stage of p2p cycle.

3.What are different types of accruals in GL.

4.Can you create invoice without creating manually ,when you have PO.

5. How do you close a receivable transaction.

6.Can we run create accounting with draft multiple times, how does entries get affected in XLA tables , will the data get deleted or updated.

7.Explain the highlevel structure of multi org.

THE DEADLY CORONA VIRUS

                                            

  HOW VIRUSES AFFECT OUR BODY

ABOUT VIRUS

 Viruses are neither living nor non-living, they lie in the intermediate state of life. They cannot be classified as life or non-life, hence they have a special class dedicated to them; VIRUS.

 Viruses cannot be grouped since they can do practically nothing unless they enter a host cell. They completely manipulate the functioning of the cell and turn them into virus-making machines. 

  Currently, there is a CORONAVIRUS pandemic which is claiming  the lives of thousands of people across the globe and is inspiring fear among many. 

It has gripped the attention of the entire world. The reason why coronavirus is so dangerous is that it has an incubation period of 14 days, and people unknowingly spread the virus through physical contact during this period.

WILL ANTIBIOTICS WORK?

 Additionally there is no known cure for the virus, no antibiotics would work since antibiotics are meant to kill only bacteria and the body would be completely unaware of the virus attack until symptoms start showing up. 

Fortunately the mortality rate is a mere 3.8 percent, and your body must gradually build resistance to the virus.

PRECAUTIONS

 Although there is no known cure for the virus, we can take precautions such as 

• Washing hands frequently.
• Avoiding physical contact.
• Maintaining 2-3 feet distance while people sneeze and prevent mingling with people with any symptoms of COVID19.
•  The virus is extremely deadly and it is so efficient in taking complete control over the body.

MYTHS

•  Face masks will do very less, if anything to protect us. 
•  We cannot always conclude a mild fever to any virus infection.
•  Sitting under sun would not kill any Virus.
•  As mentioned Antibiotics will not to do any good for Virus.

If the precautionary measures mentioned are followed and a hygienic life is pursued you will be safe from the not only corona virus but any virus.

BULK COLLECT EXCEPTION HANDLING

As we all know how to use BULK COLLECT to handle performance issues while working with large data.
In this BLOG I help you understand how to handle exceptions while using BULK COLLECT.


DECLARE
lt_projects_tbl  CURSOR_PROJ%ROWTYPE;
BEGIN



OPEN  cursor_PROJ;

FETCH CURSOR_PROJ BULK COLLECT INTO lt_projects_tbl ;



IF lt_projects_tbl.COUNT<>0

THEN --IF OF PROJECT LOOP


   BEGIN



   FORALL i IN 1..lt_projects_tbl.COUNT SAVE EXCEPTIONS

  DELETE FROM custom_table_tb

WHERE  unique_id =lt_projects_tbl(i).unique_id;



EXCEPTION

  WHEN ge_ex_bulk_errors

  THEN



  gn_bulk_error_count  :=  SQL%BULK_EXCEPTIONS.COUNT;

  gc_error_message  :=  SUBSTR ( TO_CHAR ( gn_bulk_error_count ) || ' Errors while deleting custom table.' , 1, 2000 );


  FOR l_projects_stg_ex_bulk_indx IN 1 .. gn_bulk_error_count

  LOOP



-- Resetting for the next iteration of the loop

gn_bulk_error_code           :=  NULL;

gn_bulk_error_index          :=  NULL;

l_bulk_error_project_stg_id  :=  NULL;

gc_bulk_error_messsage       :=  NULL;



gn_bulk_error_code            :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_code;

gn_bulk_error_index           :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_index;

l_bulk_error_project_stg_id   :=   lt_projects_tbl( gn_bulk_error_index ).project_stg_id;

gc_bulk_error_messsage        :=  'Unable to delete data from projects staging table Error: '|| SQLERRM ( -gn_bulk_error_code );




UPDATE custom_table_tb  XPS

   SET XPS.status                  =  'ERROR'

, XPS.status_description      =  SUBSTR ( XPS.status_description || CHR(10)  || gc_bulk_error_messsage, 1, 4000 )

     , XPS.program_update_date     =  SYSDATE

, XPS.last_update_date        =  SYSDATE

WHERE unique_id                    =  l_bulk_error_unique_id;



  END LOOP; 



END ;

CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT

CONNECT_BY_PRIOR CLAUSE

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

As we know we that the CONNECT_BY_PRIOR clause is used to get all the employees working under particular manager.

START WITH clause is used to start with all the managers.

Now we will learn about how to use CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT.

This commands are especially used in the hierarchical trees where a child can be parent for another child. We have multiple parent child relations ships .

If we take the Project Accounting module.

The projects will have tasks and these tasks can be Hierarchical.

A child task can have a parent where the child task in turn is the parent for another child task.

Example:

We have a project - 'Test Project'

We have tasks 

10000 Task Reference 1                        -Top Root Task and Parent Task

  10000 Task Reference 2                      --Child task and parent task

     Test Subtask                                     -- child task and parent task

       Subtask2                                         --only child task

  10000 Task Reference 3                     --only child task

Over Head Allocations                          --task

In the above Tree structure , we have multiple parents like '10000 Task Reference 1' and '10000 Task Reference 2 ', but the top parent for all the sub tasks is '10000 Task Reference 1'.

In order to identfy the top task we should use CONNECT_BY_ROOT and in the above structure it returns value '10000 Task Reference 1'.

If we want find all the multiple child tasks which are leaf nodes, that means they do not have any further children then we use CONNECT_BY_ISLEAF.

In the above query if we use CONNECT_BY_ISLEAF it returns value 1 if the current row is leaf node else it returns value 0.

That means the query will return value 1 for nodes like "Subtask2" and "10000 Task Reference 3" and "Over HEad Allocations"as they are leaf nodes they do not have further children.

For rest of the tasks it returns value 0.

Query :

SELECT child_task
       ,parent_task
       ,CONNECT_BY_ISLEAF IS_LOWEST_LEVEL 
       ,CONNECT_BY_ROOT (parent_task)

 FROM

(SELECT pt_child.task_name child_task,

     pt_parent.task_name parent_task

FROM pa_tasks pt_parent,

      pa_tasks pt_child,

        pa_projects_all     PPA

 WHERE pt_parent.project_id(+) = pt_child.project_id

 AND   ppa.project_id =pt_child.project_id

 AND   PPA.project_id =

 AND pt_parent.task_id(+)=pt_child.parent_task_id) inner_query

 CONNECT BY PRIOR inner_query.child_Task=inner_query.parent_task


 START WITH inner_query.parent_Task IS NULL