Currency Conversion Calculation for Different Currency Codes.

-

Currency Conversion Calculation for Different Currency Codes.  


SELECT NAME
      ,PERIOD_NAME
      ,DEPT
      ,ACCOUNT
      ,CASE
          WHEN CLOSING_STATUS = 'O'
          THEN
             GL_CURRENCY_API.CONVERT_AMOUNT(CURRENCY_CODE
                                           ,'USD'
                                           ,   -- Give the Conversion Date
                                           ,'Corporate'
                                           ,PTD)
          WHEN CLOSING_STATUS = 'C'
          THEN
             GL_CURRENCY_API.CONVERT_AMOUNT(CURRENCY_CODE
                                           ,'USD'
                                           ,     -- Give the Conversion Date
                                           ,'Corporate'
                                           ,PTD)
       END
          AMOUNT
      ,CASE
          WHEN CLOSING_STATUS = 'O'
          THEN
             GL_CURRENCY_API.GET_RATE(CURRENCY_CODE
                                     ,'USD'
                                     -- Give the Conversion Date
                                     ,'Corporate')
          WHEN CLOSING_STATUS = 'C'
          THEN
             GL_CURRENCY_API.GET_RATE(CURRENCY_CODE
                                     ,'USD'
                                     ,-- Give the Conversion Date
                                     ,'Corporate')
       END
          CONV_RATE
      ,PTD
  FROM (  SELECT GLD.NAME
                ,BAL.PERIOD_NAME
                ,GCC.SEGMENT4 DEPT
                ,GCC.SEGMENT5 ACCOUNT
                ,   GCC.SEGMENT5
                 || '-'
                 || GCC.SEGMENT4
                    "ACCOUNT-DEPT"
                ,BAL.CURRENCY_CODE
                ,NVL(SUM(BAL.PERIOD_NET_DR), 0)
                 - NVL(SUM(BAL.PERIOD_NET_CR), 0)
                    PTD
                ,GPS.START_DATE
                ,GPS.END_DATE
                ,GPS.CLOSING_STATUS
            FROM GL_BALANCES BAL
                ,GL_CODE_COMBINATIONS GCC
                ,GL_LEDGERS GLD
                ,GL_PERIOD_STATUSES GPS
           WHERE GCC.CODE_COMBINATION_ID = BAL.CODE_COMBINATION_ID
                 AND BAL.ACTUAL_FLAG = 'A'
                 AND BAL.LEDGER_ID = GLD.LEDGER_ID
                 AND GLD.CURRENCY_CODE = BAL.CURRENCY_CODE
                 AND GCC.SEGMENT5 = '' -- Give Account Number
                 AND GCC.SEGMENT4 = ''  -- Give Det Number
                 AND BAL.PERIOD_NAME = '' -- Give Period Name
                 AND GPS.APPLICATION_ID = 101
                 AND GPS.PERIOD_NAME = BAL.PERIOD_NAME
                 AND GPS.SET_OF_BOOKS_ID = BAL.LEDGER_ID
        GROUP BY BAL.PERIOD_NAME
                ,GCC.SEGMENT4
                ,GCC.SEGMENT5
                ,BAL.CURRENCY_CODE
                ,GLD.NAME
                ,GPS.START_DATE
                ,GPS.END_DATE
                ,GPS.CLOSING_STATUS)
 WHERE 1 = 1

Comments

Post a Comment

Popular posts from this blog

Deleting the Sourcing Rule Assignments API

-
Deleting the Sourcing Rule Assignments API DECLARE        l_return_status VARCHAR2(200);        l_msg_count  NUMBER;        l_msg_data VARCHAR2(2000);        l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;        l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;        l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;        l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;        o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;        o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;        o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;        o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type; ...
Read more

BarCode with XML Publisher

-
Image
Barcode Steps Code 128 Barcode with XML Publisher Step:1 XML Tags and template Create or use existing oracle reports concurrent program. Change the output format under concurrent program definition to XML, run the concurrent program and get the XML tags. Map the RTF based on the XML tags generated. Step:2 Getting barcode fonts For barcode I have used Code 128 from IDAutomation (you can download from their site) After installing the font on local machine assign one of the IDAutomation font to the field in RTF template (for example an order number and font can be eg: IDAutomationSC128M) Step:3 Barcode font reading by handheld or scanner To make the barcode to be read by scanner, the encoder java code provided by vendor has to be compiled and convert it into a java class file. A.       Below is the class code save the code in a notepad and rename it as BarcodeUtil.java B.       Move the class file with EBSO fil...
Read more
-
 Active project Managers Query SELECT DISTINCT hou.name org_name,        ppa.name project_name,        ppa.segment1 project_number,        ppa.project_type,    pe.full_name Project_Manager,    amount_limit Purchase_Requisition_approval,    pe.email_address,        ppa.start_date,        to_char(ppa.completion_date,'DD-MON-YYYY')completion_date ,        ppa.closed_date,        ppa.enabled_flag   FROM apps.pa_project_parties ppp,    apps.pa_projects_all ppa,    apps.pa_project_role_types pprt,    apps.per_all_people_f pe,    apps.pa_project_assignments pa,    apps.fnd_user u,        apps.hr_operating_units hou,   (SELECT paf.person_id,   paf.assignment_type,       pcr.amount_limit   ...
Read more