Approval Management Engine in EBS

Approval Management Engine in EBS: Points to Ponder:

1.       About AME:

Oracle Approvals Management (AME) enables you to define business rules governing the process for approving transactions in Oracle applications that have integrated AME.

2.       Integration of AME with Oracle E-Business Suite Application

INTEGRATING FROM APPLICATION	INTEGRATING TO APPLICATION
Service Work in Process	Quoting Enterprise Asset Management
Service Contracts	Payables
Field Service	Inventory
Partner Management	Receivables
Purchase Requisition	Receivables
Payroll	Work In Process

3.       Business Flow

4.       Structure of AME

AME is a framework of well-defined approval rules constructed using the following 5 components for a given transaction-type:

1.       Transaction Type:

A transaction type describes the type of transaction for which business rules and approval routings will be based. Examples of transaction types are:

• Purchase Requisition Approval (Purchasing)

• Requester Change Order Approval (Purchasing)

• Service contract Approval (service contract)

• Work Order Approval (EAM)

A Transaction type is a combination of Rules, Attributes, Conditions and Approval Groups.

Please see the screenshot below:  

2.       Attributes

Attributes within AME are business variables that represent the value of a data element of a given transaction.

• Attributes in AME can be created as being static or they can be dynamic in nature

• Attributes can be defined at 3 different levels – Header, Line Item and Cost Center level.

• Examples of attributes are:

·         Contract_Amount(service contracts)

·         Request_Severity(service Request)

·         Item_Number(Purchasing)

3.       Conditions

Conditions are used to evaluate the value of attributes in a particular transaction

• The “Condition” component tells AME engine to trigger an AME rule if the result of the     Condition is TRUE

• One or more attributes are used to define a condition.

4.       Actions

·         Actions describe what should be done in AME if a particular condition is satisfied by the transaction

·         Action Type is a collection of actions having similar functionality. Every action belongs to an action type.

Action Types
Job Based	Absolute Job Level
	Final Approver Only
	Manager then Final Approver
	Relative Job Level
	Supervisory Level
HR Position Based	HR Position
	HR Position Level
Approver Group Based	Pre-Chain-of-authority approvals
	Post-chain-of-authority approvals
	Approval-group chain of authority

5.       Approver Groups

Approver Group is used to fetch approvers from Oracle Applications (HRMS).

• Static or Dynamic in nature

• The voting method determines the order in which the Group Members are notified and also how the decision of the group’s approval.

6. Rules

Transforms the business rules into approval rules to specify approvers in the transaction’s approval list

• Rules can also be categorized as “FYI” or “Approval”.

• A rule is constructed using the following components:

1. Rule Type

2. Item Class

3. Category

4. Conditions

5. Actions

Script to get Oracle iExpense Line attachments

SELECT fl.*
  FROM apps.fnd_documents_tl        fdtl,
       apps.fnd_documents           fd,
       apps.fnd_attached_documents  fad,
       apps.fnd_lobs                fl
 WHERE     fdtl.document_id = fd.document_id
       AND fd.document_id = fad.document_id
       AND fad.entity_name = 'OIE_LINE_ATTACHMENTS'
       AND fad.pk1_value = ':p_report_line_id'  -- line_id from expense line
       AND fl.file_id = fd.media_id
       and fdtl.language='US';

Personalization in Fusion - Look and Feel

Personalization In Fusion – Part II

MODIFYING THE LOOK AND FEEL OF FUSION APPLICATION:

If you want to learn to personalize your Fusion outlay, you’re at the right place. From changing colors to integrating logos, this blog post will help you make the platform more authentic. Reshape the fusion platform in the image of your business for the professional appeal. Fabricate an immersive environment with the fusion.  Some of the salient points justifying such a move are: a.     Company-Specific Logo on Welcome Page: Each company prefers to have its own logo on the Welcome Page instead of the seeded one delivered by Oracle. While on one hand, it does help individuals to identify the Environment that they are working on, on the other hand, it also does a bit on the advertisement part. No harm displaying the company logo on the ERP application being used by the company. b.    Adhering to a specific color: While Oracle has tried its level best to present the application in the most pleasant way carefully choosing the background and foreground colors, some companies might decide to have a different shade on their environment. The application provides flexibility and enables users to do so. c.     Uniquely identify a particular instance (POD / Fusion Environment: For a typical organization using Fusion Applications or for that matter any other ERP system there are multiple environments (Also referred to as Instances / Pods) like Development, UAT, SIT, Pre-Production and Production. Since all of the above looks similar at times it becomes difficult to identify the Environment on which a specific task is being carried out. A change supposed to be done on the Development environment may be performed on UAT or vice versa and this might cause issues. One smart way of avoiding such mistakes would be using smart the color-coding scheme on the environment (maybe as displayed below):

Environment Name	Color
Development	Green
UAT	Yellow
SIT	Blue
Pre-Production	Gold
Production	Orange

There could be many more reasons but for now, we would restrict ourselves to the above three and continue with our example.

Example:

In this example, we would change the logo from the seeded one (VISION as displayed in the first screenshot) and replace the same with a new logo (customer-specific one).

As a first step we have to log in to the application (specific Fusion POD where we intend to make the changes) and click on the ‘Appearance’ (under Tools menu) hyper-link.

This takes us to the below screen:

Once you click on the Edit button you would be asked to activate a sandbox (this feature ensures that all the changes are made in a specific customized area ensuring that in case something does not go well the changes can be reverted)

We already have a sandbox “ApplcoreLongSB_01” and we will use the same here instead of creating new sandbox.

Once the same is set as Active you would notice the same displayed on the topmost region (snap-shot below)         

 N bz z  

We need to click on the ‘Update’ Button and point to our logo/image (in this example we have used an image which is stored in a local machine but this could very well be an online image too).

Next we should click on ‘Ok’ button followed by the ‘Apply’ button.

This change will appear on all the pages and the same could be verified too by navigating to different Fusion pages. In this example, we will verify the same on the homepage to get confirmation.

An important point to note here is that all these changes did not require downtime and was on the go which is a big plus and very good feature to have.

We are on the homepage now and it would be hard to miss that the logo has been changed to the new one

Although we have already made the change in the logo the change is too small and so to make the change simple and clearly, visible let-we try to change the background color too. We would change the same to ED6A24 (for this example).

And once we Click on ‘Apply’ we are able to view the change:

And finally a look at the Homepage:

So we saw how the skin, theme and various colors of the appearance of a Fusion environment can be changed. Anyone can hopefully get the desired results by following the same process.

Personalization in Fusion - SandBox

Personalization In Fusion – Part I

Introduction:

Acknowledge the potential of the customization options in the Fusion Cloud Platform. Explore the elaborate details. Learn the importance of customization and how one can utilize it to enjoy the benefits of the platform. Utilize the various sets of layers available for customization on a particular task, instance, or users.

Before we create customizations, we should select the layer in which we want to customize. Most of the customization tools provide a dialog box for selecting the layer for your customizations.

Built-In Customization Layers:

The customization layers available to an application depend on its application family. However, all applications have the following customization layers:

1.     Site layer: Customizations made at the site layer affect all users.

2.     User layer: All personalizations are made at the user layer. Users don't have to explicitly select this layer as it's automatically applied while personalizing the application.

Layer Hierarchy:

The layers are applied in a hierarchy, and the highest layer in that hierarchy in the current context is considered the top layer. With the default customization layers, the user layer is the top layer. An object may be customized more than once but in different layers. At run time, the top-layer customizations take precedence.

For example, say you customize in the site layer. You use Page Composer to add a region on a page. A user personalizes the same page to hide the region. In such a case, the user-layer customization takes precedence for that user at run time.

Storage of Customizations and Layer Information:

Customizations aren't saved to the base standard artifact. Instead, they're saved in Extensible Markup Language (XML) files for each layer. These files are stored in an Oracle Metadata Services (MDS) repository. The XML file acts as a list of instructions that determines how the artifact looks or behaves in the application, based on the customization layer. The customization engine in MDS manages this process.

When you apply an application patch or upgrade, it updates the base artifacts, but it doesn't touch the customizations stored in XML files. The base artifact is replaced. Hence, when you run the application after the patch or upgrade, the XML files are layered on top of the new version. You don't need to redo your customizations.

Example:

For example, the Sales application has a layer for a job role. When you customize an artifact, you can choose to make that customization available only to users with a specific role, for example, a sales representative.

We would not be making any changes to the pages but rather try to understand the effect customization layer has on a specific application page from a conceptual point of view.

Let’s say for this example, we want to remove the Quick Create panel from the Sales home page, and customize this page only for users with the Sales Representative role.

The perquisites for this are as follows:

      i.         Availability of an Active Sandbox

We would need to activate a sandbox (shown below).

Login to Application with appropriate credentials (HCM_IMPL in this case)

Click on ‘Customize Pages’ link 

A popup message box will appear stating that a Sandbox must be activated to perform customizations.

Next, we need to click on the ‘Activate Sandbox’ button, choose one of the available sandboxes (which would appear on a new popup window and choose the ‘Active’ button) or you can create your own sandbox by Clicking “Manage Sand Box” option available as shown below.

The sandbox will be activated (horizontal strip would appear on top of the screen with the name of the sandbox mentioned)

ii.        Appropriate Job Role

When you customize a page for a specific job role, that job role must be assigned to you for you to test   the customization in the sandbox. Your security administrator can either assign the job role to you directly or make the job role self-requestable for you to add it yourself from the resource directory. 

iii.        Selection of Customization Layer

Select the layer in which you want to make your customization. In this case, select the role layer with the value, Sales Representative. While customizing, when you remove the panel from the page, an XML file is generated. This file contains instructions to remove the panel, but only for the role layer, and only when the value is Sales Representative.

So these three are the perquisites for this example.

The customization engine in MDS then stores the XML file in an MDS repository.

When someone signs in and requests an artifact, the customization engine in MDS checks the repository for XML files matching the artifact and the given context. On matching, the customization engine layers the instructions on top of the base artifact.

In this example, whenever someone:

With the role of Sales Representative (the context) requests the Sales home page (the artifact), before the page is rendered, the customization engine in MDS:

a.             Pulls the corresponding XML file from the repository

b.            Layers it on top of the standard Sales home page

c.             Removes the panel

Without the role of Sales Representative signs in, the customization engine doesn't layer the XML file on top of the standard Sales home page. So, the Quick Create panel is displayed on the page.

Personalization:

All users of the application can use the Personalization menu items to personalize certain pages.

For example, you can:

a.             Move elements around on a page

b.            Hide elements

c.             Add available elements to a page

While you personalize a page, the customization engine in MDS creates an XML file specific to a user (in this case, you), for the user layer.

For example, say User 1 (with the role of Sales Representative) personalizes the Sales home page. An XML file will have the changes that the user made, is stored in the repository.

When User 1 signs in, the customization engine in MDS:

·         Pulls the XML file with the sales representative customizations from the repository and layers the file on top of the standard Sales home page.

Pulls the XML file with User 1 personalization’s, thus enabling the user to see the personalization changes along with the Sales Representative Changes.

Exiting SandBox:

Users can Click on SandBox name and then click on Exit Sandbox to exit from SandBox as shown in the below screen.

India AR GST tax report

  WITH Parameter AS
    (SELECT :Transaction_Start_Date AS BV_Transaction_Start_Date ,
        :Transaction_End_Date AS BV_Transaction_End_Date,
:GL_Start_Date AS BV_GL_Start_Date ,
        :GL_End_Date AS BV_GL_End_Date,
:Trx_Number_from AS BV_Trx_Number_From,
:Trx_Number_To AS BV_Trx_Number_To FROM DUAL)
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
    (select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id)  Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'CGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'SGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
'CGST' Tax_Type_1,
'SGCT' Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'CGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'SGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
    AND
        j.trx_id = trx.customer_trx_id
    AND
        j.trx_line_id = cl.customer_trx_line_id
    AND
        j.tax_category_name like 'Intrastate %'
    AND
        bill.org_id  = :org_id -- India
    AND
        trx.org_id  = :org_id-- India
    AND
    trx.complete_flag = 'Y'
AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where   ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id)  Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'IGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'XXXX',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
'IGST' Tax_Type_1,
'' Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'IGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'XXXX', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
    AND
        j.trx_id = trx.customer_trx_id
    AND
        j.trx_line_id = cl.customer_trx_line_id
    AND
     j.tax_category_name like 'Interstate %'
    AND
        bill.org_id  = :org_id -- India
    AND
        trx.org_id  = :org_id -- India
AND
    trx.complete_flag = 'Y'
    AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where   ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id) Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'CGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,
'IGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'SGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
(SELECT MIN(jrav.reporting_code)
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Type_1,
(SELECT MAX(jrav.reporting_code)
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
AND jrav.reporting_code NOT IN ('CGST','IGST')
group by  jrav.reporting_code) Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,
'IGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'CGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9,
 'IGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'SGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( (cl.quantity_invoiced is not null or cl.quantity_credited is not null) or 
     ( cl.extended_amount is not null and rt.name = :Trx_source --'IN CM TDS')
)
    AND
        j.trx_id(+) = trx.customer_trx_id
    AND
        j.trx_line_id(+) = cl.customer_trx_line_id
    AND
        ( j.tax_category_name is null or (
      j.tax_category_name not like 'Interstate %'
  AND j.tax_category_name not like 'Intrastate %' )
)
    AND
        bill.org_id  = :org_id--379 -- India
    AND
        trx.org_id  = :org_id--379 -- India
AND
    trx.complete_flag = 'Y'
    AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)