Query to get Requestion Approval rules (DOA)

 with por_lookup_list as ( 

    select lookup_type, 

           lookup_code, 

           meaning

    from   fnd_lookups 

    where  enabled_flag='Y'

    and    sysdate between nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) and nvl(end_date_active,to_date('31/12/4712','DD/MM/YYYY'))

    and    lookup_type in ('POR_AMX_ACTION','POR_AMX_ROUTE_TO','POR_AMX_SINGLE_APPROVER','YES_NO'))

select tl.meaning TASK,

       r.rule_id, 

       r.display_rule_name RULE,

      (select meaning 

       from   por_lookup_list

       where  r.active_flag=lookup_code

       and    lookup_type ='YES_NO') isactive,

       sl.meaning STAGE, 

       decode(ALWAYS_APPLY_FLAG,'Y','Always Apply', r.conditions_string) CONDITION,

       pl.meaning PARTICIPANT,

      (select meaning 

       from   por_lookup_list

       where  a.action_code=lookup_code

       and    lookup_type ='POR_AMX_ACTION')action_type,

      (select meaning 

       from   por_lookup_list

       where  a.ROUTE_USING_CODE=lookup_code

       and    lookup_type ='POR_AMX_ROUTE_TO' ) Route_Using,

      (select meaning 

       from   por_lookup_list

       where  a.user_type_code=lookup_code

       and    lookup_type ='POR_AMX_SINGLE_APPROVER' )User_Type,

       paag.APPROVAL_GROUP_NAME Approval_Group,

      (select listagg(fwags.member,',') as mem

       from   fa_fusion_soainfra.wfapprovalgroups  fwag,

              fa_fusion_soainfra.wfapprovalgroupmembers fwags

       where  fwag.approvalgroupid=fwags.approvalgroupid

       and    fwag.approvalgroupname=paag.approval_group_name

       group by fwag.approvalgroupid,

                fwag.approvalgroupname) members

from   por_amx_rules r, 

       por_amx_tasks t,

       fnd_lookups tl, 

       por_amx_participants p, 

       fnd_lookups pl, 

       por_amx_stages s, 

       fnd_lookups sl,

       por_amx_actions a,

       por_amx_approval_groups paag

where  1=1

and   r.sandbox_flag='Y' 

and   r.task_id=t.task_id

and   tl.lookup_type=t.task_lookup 

and   tl.lookup_code=t.lookup_code 

and   r.participant_id=p.participant_id

and   t.participant_lookup=pl.lookup_type 

and   pl.lookup_code=p.lookup_code 

and   s.stage_id=p.stage_id

and   t.stage_lookup=sl.lookup_type

and   s.lookup_code=sl.lookup_code

and   a.rule_id=r.rule_id

and   r.active_flag ='Y'

and   a.approval_group_name = paag.APPROVAL_GROUP_NAME

--and   paag.APPROVAL_GROUP_NAME='DOA_ITA_L2_03'

--and   t.task_key=:bindTaskKey 

and  (t.task_lookup in (:p_task_name) or coalesce(:p_task_name, null) is null)

and  (r.active_flag in (:p_active) or coalesce(:p_active, null) is null)

and   r.creation_date between nvl(:p_dt_from,r.creation_date) and nvl(:p_dt_to,r.creation_date)

order by sl.meaning,

         pl.meaning desc

Get the column or Table information in oracle fusion erp

 select * from all_tab_cols where upper(column_name) like 'OFFSET_TAX_FLAG'

SELECT * FROM all_tab_modifications;

<?choose:?><?when: P_INVOICENUM=’’?>All<?end when?><?otherwise:?><?P_INVOICENUM?><?end otherwise?><?end choose?>

<fo:basic-link external-destination="{INVOICE_URL}" color="blue" text-decoration="underline">
<?INVOICE_NUMBER?>
</fo:basic-link>

Print Parameters In Otbi:

[b]Legal Employer               : @{PV_LE}[/b][br/]
[b]Department                      : @{PV_DEPT_NAME}[/b][br/]
[b]Employee Number            : @{PV_EMP_NUMBER}[/b][br/]
[b]Start Date                          : @{PV_ST_DT}[/b][br/]
[b]End Date                            : @{PV_END_DT}[/b][br/]
[b]Leave Type                           : @{PV_ABS_TYPE}[/b][br/]
[b]Absence Status                        : @{PV_ABS_STA}[/b][br/]

Convert String into Arabic xml tag for RTF:

<?xdoxslt:toCheckNumber('AR-KW',SALARY_AMOUNT,0)?>

IF condition in RTF xml Tag:

1) <?if:(GRADE='8 A') or 

(GRADE='8 B') or (GRADE='9 A') or (GRADE='9 B') or 

(GRADE='10 A') or (GRADE='10 B') or (GRADE='11 A') or 

(GRADE='11 B') or (GRADE='12')?>

2)<?xdofx:if (GRADE ='8 A' or GRADE ='8 B')

Then '2000'

Else If (GRADE ='9 A' or GRADE ='9 B')

Then '2500'

Else If (GRADE ='10 A' or GRADE ='10 B')

Then '2800'

Else If (GRADE ='11 A' or GRADE ='11 B')

Then '3500'

Else If (GRADE ='12')

Then '5000'

END if?>

Call Subtemplate:

In Rtf Template:
<?call:Logo?>
<?import:xdoxsl:///Custom/Custom/Financials/Payables/Reports/Logo Template.xsb(Report Path)?>

In Fusion Sub Template:
<?template_Logo?> insert your Logo <?end template?>

Query for Credit Hold Report Oracle Erp Cloud

 SELECT DISTINCT DOFL.SOURCE_ORDER_NUMBER

                ,DOFL.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_ORDER_FLINES_V     DOFL

   , DOO_HEADERS_ALL        DHA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_FLINES_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DOFL.FULFILL_LINE_ID   

AND DOFL.HEADER_ID                = DHA.HEADER_ID

AND DOFL.INVENTORY_ITEM_ID        = EGB.INVENTORY_ITEM_ID

AND DOFL.INVENTORY_ORGANIZATION_ID = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID         = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND hou.name                      = :P_Business_Unit

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

UNION ALL

SELECT DISTINCT DHA.SOURCE_ORDER_NUMBER

                ,DLA.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_HEADERS_ALL        DHA

   , DOO_LINES_ALL          DLA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_HEADERS_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DHA.HEADER_ID   

AND DHA.HEADER_ID                  =  DLA.HEADER_ID

AND DLA.INVENTORY_ITEM_ID          = EGB.INVENTORY_ITEM_ID

AND DLA.INVENTORY_ORGANIZATION_ID  = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID          = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

ORDER BY 1,6

Changing the Purchase Order Layout in Oracle Fusion Applications

Often times there is a requirement to change the Purchase Order PDF report during implementation to add a company's logo or branding, or add or remove information from the layout itself.

Below are the steps for you to modify a Purchase Order Report:

1. Upload a custom Purchase Order Template to BI Publisher. (see the link for the steps)
2. Navigate to Setup and Maintenance
3. Click on the Task Pane then click on Search

For More Information Please follow below link.

 https://lifeofanoracleprodigy.blogspot.com/2018/09/changing-purchase-order-layout.html

Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))

Generate Certificate from Oracle Fusion Erp Cloud

 Step 1: click Instance url

https://<Instance url>/FndManageImportExportFilesService?wsdl

Step 2: 

You'll see certificate data in this tag : dsig:X509Certificate

Step 3: 

Your certificate should be in this format : 

-----BEGIN CERTIFICATE-----

Copy data from Step 2

-----END CERTIFICATE-----

open a notepad and copy those header and footer and insert data in between

Step 4 :

Save the file as <Filename.cer>

Employee with all element entries Query in Oracle HCM Cloud

 SELECT 

       ldg.name                         ldg_name,

       pect1.classification_name        primary_classification,

       (SELECT pect2.classification_name

       FROM pay_ele_classifications_tl pect2

       WHERE pect2.classification_id          = pet.secondary_classification_id

       AND pect2.LANGUAGE                     = USERENV('LANG')

       )                                secondary_classification,

       pldf.attribute1                  region,

       ftl.territory_short_name         country_name,

       ppslv.person_number              person_number,

       ppnf.full_name                   employee_name,

       pldft.location_name              location,

       houft.name                       department,

       (SELECT ffvv.description

        FROM gl_code_combinations   gcc,

             fnd_flex_values_vl     ffvv,

             fnd_id_flex_segments   fifs

        WHERE gcc.code_combination_id   = paam.default_code_comb_id

        AND gcc.segment5                = ffvv.flex_value

        AND ffvv.flex_value_set_id      = fifs.flex_value_set_id

        AND fifs.SEGMENT_NAME           = 'Team'

        AND fifs.id_flex_code           = 'GL#'

       ) team,

       pet.reporting_name                  reporting_name,

       to_char(pee.creation_date, 'MM-DD-YYYY') entry_date,

       to_char(pee.effective_start_date, 'MM-DD-YYYY') effective_start_date,

       to_char(pee.effective_end_date, 'MM-DD-YYYY') effective_end_date,

       pet.element_name                    element_name,

       pet.base_element_name               element_code,

       pet.description                     element_description,

       DECODE(pet.processing_type, 'R', 'Yes', 'N', 'No', 'No') recurring_entry,

       ldg.default_currency_code           currency,

       peevf.screen_entry_value            amount,

       ( CASE

WHEN ldg.default_currency_code = 'USD'

THEN to_number(peevf.screen_entry_value)

            ELSE

            ROUND((to_number(peevf.screen_entry_value)) * gdr.conversion_rate,2)

         END

) amount_usd,

       pgft.name                        job_grade,

       pjf.job_code                     job_code,

       pelf.attribute1                  bonus_eligible,

       pelf.attribute2                  merit_eligible,

       pelf.attribute3                  pension_eligible

  FROM pay_legislative_data_groups      ldg,

       pay_element_types_vl             pet,

   (select gdr1.conversion_rate, gdr1.from_currency

FROM gl_daily_rates gdr1

WHERE  gdr1.to_currency = 'USD'

AND gdr1.conversion_type = 'Corporate'

AND gdr1.conversion_date = (SELECT MAX(gdr2.conversion_date)

FROM  gl_daily_rates gdr2

WHERE gdr2.to_currency = 'USD'

AND gdr2.conversion_type = 'Corporate'

AND gdr2.from_currency=gdr1.from_currency

AND gdr2.conversion_date <= sysdate)

)                    gdr,

       pay_element_links_f              pelf,

       pay_ele_classifications_tl       pect1,

       pay_element_entries_f            pee,

       pay_element_entry_values_f       peevf,

       pay_input_values_f               piv,

       per_person_names_f               ppnf,

       per_person_secured_list_v        ppslv,

       per_all_assignments_m            paam,

       hr_organization_units_f_tl       houft,

       per_location_details_f           pldf,

       per_location_details_f_tl        pldft,

       per_grades_f_tl                  pgft,

       per_jobs_f                       pjf,

       per_locations                    pl,

       fnd_territories_tl               ftl

WHERE (( coalesce(NULL, :p_ldg_name) IS NULL )

          OR ( ldg.name IN (:p_ldg_name) ) )

   AND ( ( coalesce(NULL, :p_reporting_name) IS NULL )

          OR ( pet.reporting_name IN ( :p_reporting_name ) ) )

   AND ( ( coalesce(NULL, :p_element_name) IS NULL )

          OR ( pet.element_type_id IN ( :p_element_name ) ) )

   AND ( ( coalesce(NULL, :p_processing_type) IS NULL )

          OR ( pet.processing_type IN ( :p_processing_type ) ) )

   AND ldg.legislative_data_group_id  = pet.legislative_data_group_id

   AND ldg.default_currency_code      = gdr.from_currency(+)

   AND pet.element_type_id            = pee.element_type_id

   AND pet.element_type_id            = pelf.element_type_id

   AND pet.classification_id          = pect1.classification_id

   AND UPPER(pet.element_name) NOT LIKE '%SALARY%'

   AND pect1.LANGUAGE                 = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN pelf.effective_start_date AND pelf.effective_end_date

   AND pee.person_id                  = ppnf.person_id

   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

   AND pee.element_entry_id           = peevf.element_entry_id

   AND pee.last_update_date           = peevf.last_update_date

   AND pee.effective_start_date       = peevf.effective_start_date

   AND peevf.input_value_id           = piv.input_value_id

   AND piv.element_type_id            = pet.element_type_id

   AND piv.base_name                  = 'Amount'

   AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date

   AND ppnf.person_id                 = ppslv.person_id

   AND TRUNC(SYSDATE) BETWEEN ppslv.effective_start_date AND ppslv.effective_end_date

   AND ppnf.person_id                 = paam.person_id

   AND ppnf.name_type                 = 'GLOBAL'

   AND paam.organization_id           = houft.organization_id

   AND houft.LANGUAGE                 = USERENV('LANG')

   AND paam.effective_latest_change   = 'Y'

   AND paam.assignment_type           = 'E'

   AND paam.assignment_status_type    = 'ACTIVE'

   AND paam.primary_assignment_flag   = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND paam.location_id               = pldf.location_id

   AND pldf.location_details_id       = pldft.location_details_id

   AND pldft.LANGUAGE                 = USERENV('LANG')

   AND paam.grade_id                  = pgft.grade_id

   AND pgft.LANGUAGE                  = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date

   AND paam.job_id                    = pjf.job_id

   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date

   AND paam.location_id               = pl.location_id

   AND pl.country                     = ftl.TERRITORY_CODE

   AND ftl.LANGUAGE                   = USERENV('LANG')

   AND (( coalesce(NULL, :p_full_name) IS NULL )

         OR ( ppnf.full_name IN (:p_full_name) ) )

   AND pee.effective_start_date <= :p_end_date

   AND pee.effective_end_date >= :p_start_date 

   --AND :p_start_date <= :P_end_date

ORDER BY ldg.name, ppnf.full_name, pet.element_name,pee.effective_start_date DESC

Query to get DFF and Segment Values

 SELECT ffv.descriptive_flexfield_name “DFF Name”,

ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”

FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘Give Title Name’
AND ffc.descriptive_flex_context_code like ‘Give Context Code Value’

ORDER BY att.column_seq_num

Oracle GL Subledger Drill Down Query

 WITH 

entity_sec_hrchy as(

select regexp_substr (val, '[^|]+', 1, 1)   level0

      ,regexp_substr (val, '[^|]+', 1, 2)   level1

  from (select ltrim(sys_connect_by_path(pk1_start_value, '|'), '|') val

          from (select ftn.pk1_start_value

                      ,ftn.parent_pk1_value

                  from fnd_tree_node        ftn

                      ,fnd_tree_version_tl  ftvt

                 where 1                         = 1

                   and ftn.tree_version_id       = ftvt.tree_version_id

                   and ftvt.tree_version_name    = 'XX_ENTITY_SECURITY_HIERARCHY'

                   and ftvt.tree_code            = 'XX_ENTITY_SECURITY_HIERARCHY'

                   and ftvt.tree_structure_code  = 'GL_ACCT_FLEX'

                   and ftvt.language             = 'US')

         where connect_by_isleaf  = 1

    start with parent_pk1_value is null

    connect by nocycle parent_pk1_value = prior pk1_start_value)

),

data_acc_set as(

select gllv.ledger_id

      ,gllv.ledger_name

      ,entity_sec_hrchy.level0

      ,entity_sec_hrchy.level1      flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,null                         ledger_set_name

  from entity_sec_hrchy

      ,gl_legal_entities_bsvs         gleb

      ,gl_ledger_le_v                 gllv

      ,fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasna

 where 1 = 1

   and gllv.legal_entity_id       = gleb.legal_entity_id

   and gleb.flex_segment_value    = entity_sec_hrchy.level1

   and furda.user_guid            = FND_GLOBAL.USER_GUID

   and gasna.access_set_id        = furda.access_set_id

   and furda.active_flag          = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasna.segment_value        = entity_sec_hrchy.level0

union all

select distinct gl.ledger_id

      ,gllv.ledger_name

      ,null

      ,gleb.flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,null                         ledger_set_name

  from fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasa

      ,gl_ledgers                     gl

      ,gl_ledger_le_v                 gllv

      ,gl_legal_entities_bsvs         gleb

      ,gl_access_sets                 gas

 where furda.user_guid            = FND_GLOBAL.USER_GUID

   and gasa.access_set_id         = furda.access_set_id

   and furda.active_flag          = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasa.ledger_id             = gl.ledger_id

   and gl.object_type_code        = 'L'

   and gllv.legal_entity_id       = gleb.legal_entity_id

   and gasa.access_set_id         = gas.access_set_id

   and gas.security_segment_code  = 'F'

   and gleb.legal_entity_id       = gllv.legal_entity_id

   and gllv.ledger_id             = gasa.ledger_id

union all

select distinct gls1.ledger_id

      ,gls1.name

      ,null

      ,gleb.flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,gl.name                     ledger_set_name

  from fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasa

      ,gl_ledgers                     gl

      ,gl_ledger_set_assignments      glsa

      ,gl_ledgers                     gls1

      ,gl_legal_entities_bsvs         gleb

      ,gl_access_sets                 gas

      ,gl_ledger_le_v                 gllv

 where furda.user_guid             = FND_GLOBAL.USER_GUID

   and gasa.access_set_id          = furda.access_set_id

   and furda.active_flag           = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasa.ledger_id              = gl.ledger_id

   and gasa.all_segment_value_flag = 'Y'

   and gl.ledger_id                = glsa.ledger_set_id

   and gls1.ledger_id              = glsa.ledger_id

   and gasa.access_set_id          = gas.access_set_id

   and gas.security_segment_code   = 'F'

   and gleb.legal_entity_id        = gllv.legal_entity_id

   and gllv.ledger_id              = gls1.ledger_id

),

DYN_GL_XX_DATA AS

  (SELECT glg.NAME LEDGER,

    gjh.period_name PERIOD,

    gjh.je_source,

    gjh.je_category,

    gjh.doc_sequence_value DOC_SEQ,

    TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,

    TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,

data_acc_set.flex_segment_value accounting_entity,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value) accounting_entity_desc,

    gcc.segment2 cost_centre,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2) cost_centre_desc,

    gcc.segment3 nominal_account,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3) nominal_account_desc,

    gcc.segment4 sub_analysis,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4) sub_analysis_desc,

    gcc.segment5 intercompany,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5) intercompany_desc,

    gcc.segment6 location,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6) location_desc,

    gcc.segment7 project,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7) project_desc,

    gcc.segment8 flow,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8) flow_desc,

gcc.segment9 spare1,

gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9) spare1_desc,

gcc.segment10 spare2,

gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10) spare2_desc,

gcc.code_combination_id,

    gjl.currency_code ENT_CUR,

    gjl.entered_dr ENT_DR,

    gjl.entered_cr ENT_CR,

    NVL(gjl.entered_dr,0)-NVL(gjl.entered_cr,0) ENT_NET,

    gjl.accounted_dr ACCTD_DR,

    gjl.accounted_cr ACCTD_CR,

    NVL(gjl.accounted_dr,0)-NVL(gjl.accounted_cr,0) ACCTD_NET,

    xal.accounting_class_code ACTCLS_CODE,

    xal.entered_dr XENTERED_DR,

    xal.entered_cr XENTERED_CR,

    xal.accounted_dr XACCOUNTED_DR,

    xal.accounted_cr XACCOUNTED_CR,

    nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,

    TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,

    nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,

    gjb.name BATCH,

    gjb.description BATCH_DESC,

    gjh.name JOUR_NAME,

    gjh.description JOUR_DESC,

    gjl.description JOURLINE_DESC,

    gjh.created_by JOUR_PREP,

(SELECT user_id FROM gl_je_action_log WHERE je_batch_id = gjb.je_batch_id AND action_code = 'POSTED') POSTED_BY,

fsv_acc_seq.header_name      ACC_SEQUENCE_NAME,

    gjh.posting_acct_seq_value   ACC_SEQUENCE_NUMBER,

    fsv_rep_seq.header_name      REP_SEQUENCE_NAME,

    gjh.close_acct_seq_value     REP_SEQUENCE_NUMBER,

gjl.je_line_num              JOURNAL_LINE_NUMBER,

(SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,

    --DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,

decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,

    TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american') REV_DATE,

    xev.entity_id,

    xah.ae_header_id,

gjl.REFERENCE_1 GL_REF1,

xal.accounting_class_code,

    xal.ae_line_num

  FROM xla_events xev,

    xla_ae_headers xah,

    xla_ae_lines xal,

    gl_import_references gir,

    gl_je_headers gjh,

fun_seq_versions fsv_acc_seq,

    fun_seq_versions fsv_rep_seq,

    gl_je_lines gjl,

    gl_ledgers glg,

    gl_je_batches gjb,

gl_code_combinations gcc,

gl_je_sources gjss,

gl_je_categories gjcc,

data_acc_set

  WHERE 1                        =1

  AND xah.event_id               = xev.event_id

  AND xah.ae_header_id           = xal.ae_header_id

  AND xah.gl_transfer_status_code= 'Y'

  AND xal.gl_sl_link_id          =gir.gl_sl_link_id

  AND gir.gl_sl_link_table       = xal.gl_sl_link_table

  AND gjl.je_header_id           =gjh.je_header_id

  AND gjh.je_header_id           =gir.je_header_id

  AND gjl.je_header_id           =gir.je_header_id

  AND gir.je_line_num            =gjl.je_line_num

  AND glg.ledger_id              = gjh.ledger_id

  AND gjh.je_batch_id            = gjb.je_batch_id

  --AND GCC.SEGMENT3 = '5002002'

  --and gjb.je_batch_id            = gjal_ct.je_batch_id

  AND gcc.code_combination_id    =xal.code_combination_id

  AND gcc.code_combination_id    =gjl.code_combination_id

  and gjh.posting_acct_seq_version_id  = fsv_acc_seq.seq_version_id(+)

  and gjh.close_acct_seq_version_id    = fsv_rep_seq.seq_version_id(+)

  AND gjss.je_source_name = gjh.je_source

  AND gjcc.je_category_name = gjh.je_category

  AND gjss.LANGUAGE = USERENV('LANG')

  AND gjcc.LANGUAGE = USERENV('LANG')

  AND gjh.status                 ='P'

  AND gjh.actual_flag            ='A'

  AND gjh.je_source IN ('Payables','Receivables','Cash Management','Assets')

  --AND glg.NAME                 = :P_LEDGER

   and (data_acc_set.ledger_name = :p_ledger

    or data_acc_set.ledger_set_name = :p_ledger)

   and glg.ledger_id             = data_acc_set.ledger_id

and ((coalesce(null, :p_acct_entity) is null)

    or (data_acc_set.flex_segment_value in (:p_acct_entity)))

  and ((coalesce(null, :p_cost_centre) is null)

    or (gcc.segment2 in (:p_cost_centre)))

  and ((coalesce(null, :p_account) is null)

    or (gcc.segment3 in (:p_account)))

  and ((coalesce(null, :p_sub_analysis) is null)

    or (gcc.segment4 in (:p_sub_analysis)))

  and ((coalesce(null, :p_intercmpny) is null)

    or (gcc.segment5 in (:p_intercmpny)))

  and ((coalesce(null, :p_location) is null)

    or (gcc.segment6 in (:p_location)))

  and ((coalesce(null, :p_project) is null)

    or (gcc.segment7 in (:p_project)))

  and ((coalesce(null, :p_flow) is null)

    or (gcc.segment8 in (:p_flow)))

  and ((coalesce(null, :p_spare1) is null)

    or (gcc.segment9 in (:p_spare1)))

  and ((coalesce(null, :p_spare2) is null)

    or (gcc.segment10 in (:p_spare2)))

 and ((coalesce(null, :P_JE_SOURCE) is null)

    or (gjss.user_je_source_name in (:P_JE_SOURCE)))

 and ((coalesce(null, :P_JE_CATEGORY) is null)

    or (gjcc.user_je_category_name in (:P_JE_CATEGORY)))

and gjh.period_name in (select period_name

                             from gl_periods

                            where start_date     >= (select start_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_FROM

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and end_date       <= (select end_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_TO

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and period_set_name = 'XX_GLOBAL_CAL')  

 

  ),

  DYN_INVOICE_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='INVOICE TYPE'

  AND LANGUAGE     =USERENV('LANG')

  ),

  DYN_VENDOR_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='POZ_VENDOR_TYPE'

  AND LANGUAGE     =USERENV('LANG')

  ),

  DYN_INVOICE_LINE_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='INVOICE LINE TYPE'

  AND LANGUAGE     =USERENV('LANG')

  )

--Purchase Invoices

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  aia.invoice_num AP_INV_NUMBER,

  (SELECT MEANING

  FROM DYN_INVOICE_TYPE

  WHERE LOOKUP_CODE = AIA.INVOICE_TYPE_LOOKUP_CODE

  ) AP_INV_TYPE,

  aia.source AP_SOURCE,

  TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') AP_INV_DATE,

  aia.invoice_amount AP_INVOICE_AMOUNT,

  hp.party_name AP_SUP_NAME,

  aps.segment1 AP_SUP_NUM,

  poss.vendor_site_code AP_SUPSITE,

  (SELECT MEANING

  FROM DYN_VENDOR_TYPE

  WHERE LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  ) AP_SUPTYPE,

  (SELECT MAX(PU.USERNAME)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS PU 

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  )  AP_REQUESTOR,

  aia.description AP_INVDESC,

  (SELECT MAX(ail.description)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT MAX(dtf.MEANING)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

DYN_INVOICE_LINE_TYPE dtf

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and dtf.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINE_TYPE,

  (SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT MAX(prha.requisition_number)

  FROM po_distributions_all pda ,

    por_req_distributions_all prda ,

    por_requisition_lines_all prla ,

    por_requisition_headers_all prha

  WHERE aia.po_header_id         = pda.po_header_id

  AND pda.req_distribution_id    = prda.distribution_id

  AND prda.requisition_line_id   = prla.requisition_line_id

  AND prla.requisition_header_id = prha.requisition_header_id

  ) AP_POREQ,

  (SELECT LISTAGG(rsh.RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY rsh.RECEIPT_NUM)

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  ) AP_RECEIPT,

  (SELECT LISTAGG(check_number,'|') WITHIN GROUP (ORDER BY check_number)

   FROM (SELECT aca.check_number,sum(aipa.amount) AMT

                 FROM ap_checks_all aca,

                           ap_invoice_payments_all aipa

              WHERE aca.check_id  = aipa.check_id

                   AND aipa.invoice_id = aia.invoice_id

              GROUP BY aca.check_number)

 WHERE AMT!=0 -- To exclude cancelled checks

  ) AP_PMT,

  att.name  AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  to_char(aia.terms_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_invoices_all aia,

  xla_transaction_entities xte,

  poz_suppliers aps,

  ap_terms_tl  att,

  poz_supplier_sites_all_m poss,

  hz_parties hp

WHERE 1                         =1

AND aia.invoice_id              = xte.source_id_int_1

AND aps.party_id                = hp.party_id(+)

AND aia.vendor_id               =aps.vendor_id(+)

AND aia.vendor_site_id          = poss.vendor_site_id(+)

AND aps.vendor_id               = poss.vendor_id(+)

and aia.terms_id                     = att.term_id

and att.language                     = 'US'

AND DGSD.entity_id              = xte.entity_id

AND xte.entity_code             = 'AP_INVOICES'

AND DGSD.je_source              = 'Payables'

AND DGSD.je_category            = 'Purchase Invoices'

UNION ALL

--Payments

SELECT DISTINCT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  (SELECT max(aia.invoice_num)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_NUMBER,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_TYPE dff

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_TYPE,  

    (SELECT max(aia.source)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_SOURCE,

   (SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_DATE,  

  (SELECT max(aia.invoice_amount)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVOICE_AMOUNT,

  (SELECT max(hp.party_name)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

hz_parties hp,

poz_suppliers aps

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and aps.party_id = hp.party_id

  ) AP_SUP_NAME,

  (SELECT max(aps.segment1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  ) AP_SUP_NUM,

  (SELECT max(poss.vendor_site_code)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

poz_supplier_sites_all_m poss

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and poss.vendor_id = aps.vendor_id

  AND poss.vendor_site_id  = aia.vendor_site_id

  )  AP_SUPSITE,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

DYN_VENDOR_TYPE dff

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  )  AP_SUPTYPE,

  (SELECT max(pu.username)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS pu

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  ) AP_REQUESTOR,

   (SELECT max(aia.description)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVDESC,

  (SELECT max(ail.description)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT max(dff.MEANING)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_LINE_TYPE dff

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  )  AP_INVLINE_TYPE,

  (SELECT max(pha.SEGMENT1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

             PO_HEADERS_ALL pha

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and pha.PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT max(prha.requisition_number)

    FROM po_headers_all poh, 

    po_distributions_all pda , 

    por_req_distributions_all prda , 

    por_requisition_lines_all prla , 

    por_requisition_headers_all prha ,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

    WHERE poh.po_header_id = pda.po_header_id 

    AND poh.po_header_id = aia.po_header_id

    AND pda.req_distribution_id = prda.distribution_id 

    AND prda.requisition_line_id = prla.requisition_line_id 

    AND prla.requisition_header_id = prha.requisition_header_id

and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_POREQ,

  (SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY RECEIPT_NUM)

  FROM

  (SELECT rsh.RECEIPT_NUM

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  GROUP BY rsh.RECEIPT_NUM)

  )  AP_RECEIPT,

  TO_CHAR(aca.check_number) AP_PMT,

  NULL AP_PAYMENT_TERMS,

  --to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american')  AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,

  flv.meaning  AP_PAYMENT_STATUS,

  ieba.bank_account_num AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_checks_all aca,

  ap_invoice_payments_all aipa,

  ap_payment_schedules_all aps,

  fnd_lookup_values flv,

  iby_ext_bank_accounts ieba,

  xla_transaction_entities xte

WHERE 1                              =1

AND aca.check_id                     = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

and aipa.check_id                    = aca.check_id(+)

and aps.invoice_id  = aipa.invoice_id

and aca.status_lookup_code           = flv.lookup_code(+)

and aca.external_bank_account_id     = ieba.ext_bank_account_id(+)

and flv.lookup_type               (+)= 'CHECK STATE'

and flv.language                  (+)= 'US'

AND DGSD.je_source                   = 'Payables'

AND DGSD.je_category                 = 'Payments'

AND xte.entity_code                  = 'AP_PAYMENTS'

UNION ALL

--Reconciled Payments

SELECT DISTINCT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  (SELECT max(aia.invoice_num)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_NUMBER,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_TYPE dff,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_TYPE,  

    (SELECT max(aia.source)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_SOURCE,

   (SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_DATE,  

  (SELECT max(aia.invoice_amount)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVOICE_AMOUNT,

  (SELECT max(hp.party_name)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

hz_parties hp,

poz_suppliers aps,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and aps.party_id = hp.party_id

  ) AP_SUP_NAME,

  (SELECT max(aps.segment1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  ) AP_SUP_NUM,

  (SELECT max(poss.vendor_site_code)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

poz_supplier_sites_all_m poss ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and poss.vendor_id = aps.vendor_id

  AND poss.vendor_site_id  = aia.vendor_site_id

  )  AP_SUPSITE,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

DYN_VENDOR_TYPE dff ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  )  AP_SUPTYPE,

  (SELECT max(pu.username)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS pu ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  ) AP_REQUESTOR,

   (SELECT max(aia.description)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVDESC,

  (SELECT max(ail.description)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT max(dff.MEANING)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_LINE_TYPE dff ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  )  AP_INVLINE_TYPE,

  (SELECT max(pha.SEGMENT1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

             PO_HEADERS_ALL pha ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and pha.PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT max(prha.requisition_number)

    FROM po_headers_all poh, 

    po_distributions_all pda , 

    por_req_distributions_all prda , 

    por_requisition_lines_all prla , 

    por_requisition_headers_all prha ,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

    WHERE poh.po_header_id = pda.po_header_id 

    AND poh.po_header_id = aia.po_header_id

    AND pda.req_distribution_id = prda.distribution_id 

    AND prda.requisition_line_id = prla.requisition_line_id 

    AND prla.requisition_header_id = prha.requisition_header_id

and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_POREQ,

  (SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY RECEIPT_NUM)

  FROM

  (SELECT rsh.RECEIPT_NUM 

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  GROUP BY rsh.RECEIPT_NUM)

  )  AP_RECEIPT,

  TO_CHAR(aca.check_number) AP_PMT,

  NULL AP_PAYMENT_TERMS,

  --to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american')  AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,

  flv.meaning  AP_PAYMENT_STATUS,

  ieba.bank_account_num AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_checks_all aca,

  ap_invoice_payments_all aipa,

  ap_payment_schedules_all aps,

  fnd_lookup_values flv,

  iby_ext_bank_accounts ieba,

  xla_transaction_entities xte

WHERE 1                              =1

AND aca.check_id                     = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

and aipa.check_id                    = aca.check_id(+)

and aps.invoice_id  = aipa.invoice_id

and aca.status_lookup_code           = flv.lookup_code(+)

and aca.external_bank_account_id     = ieba.ext_bank_account_id(+)

and flv.lookup_type               (+)= 'CHECK STATE'

and flv.language                  (+)= 'US'

AND DGSD.je_source                   = 'Payables'

AND xte.entity_code                  = 'AP_PAYMENTS'

AND DGSD.je_category                 = 'Reconciled Payments'

UNION ALL

--Receivables Invoice Distributions

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

 DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  )AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ra_cust_trx_line_gl_dist_all rctda,

  xla_distribution_links xdl,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

AND rcta.customer_trx_id             = rctda.customer_trx_id

AND xdl.ae_header_id                 = DGSD.ae_header_id

AND xdl.ae_line_num                  = DGSD.ae_line_num

AND xdl.source_distribution_id_num_1 = rctda.cust_trx_line_gl_dist_id

AND xdl.source_distribution_type     = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

AND DGSD.je_source                   = 'Receivables'

UNION ALL 

-- Receivable Applications

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  )AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ar_distributions_all rctda,

  ar_receivable_applications_all araa,

  xla_distribution_links xdl,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

AND xdl.ae_header_id                 = DGSD.ae_header_id

AND xdl.ae_line_num                  = DGSD.ae_line_num

AND xdl.source_distribution_id_num_1 = rctda.line_id

AND araa.receivable_application_id = rctda.source_id

AND rcta.customer_trx_id = araa.customer_trx_id

AND xdl.source_distribution_type     = 'AR_DISTRIBUTIONS_ALL'

AND DGSD.je_source                   = 'Receivables'

UNION ALL 

--Receivables Adjustments

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT DISTINCT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  ) AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ar_adjustments_all ada,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = ada.customer_trx_id

AND DGSD.entity_id                   = xte.entity_id

AND xte.source_id_int_1 = ada.adjustment_id

 AND ada.customer_trx_id = rcta.customer_trx_id

AND DGSD.je_source                   = 'Receivables'

AND DGSD.je_category = 'Adjustment'

AND xte.entity_code = 'ADJUSTMENTS'

UNION ALL

--Receipts Balancing

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  (SELECT LISTAGG(trx_number,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rcta.trx_number ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

GROUP BY rcta.trx_number,rcta.customer_trx_id

  )) AR_TRXNUM,  

  (SELECT DISTINCT rctla.description

  FROM ra_customer_trx_lines_all rctla             

  WHERE 1=1

      AND ROWNUM<2

      AND exists (select 1 from ar_receivable_applications_all araa 

                    where rctla.customer_trx_id=araa.applied_customer_trx_id

                            AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP')

  ) AR_INV_LINE_DESC,

  (SELECT LISTAGG(trx_date,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american')  trx_date,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

GROUP BY rcta.trx_date,rcta.customer_trx_id

  ))  AR_TRXDATE,

  (SELECT SUM(rctla.extended_amount) extended_amount

  FROM ra_customer_trx_lines_all rctla             

  WHERE 1=1

      AND exists (select 1 from ar_receivable_applications_all araa 

                    where rctla.customer_trx_id=araa.applied_customer_trx_id

                            AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP')

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  (SELECT hcsu.location

  FROM hz_party_sites hps,

    hz_cust_acct_sites_all hcsa,

    hz_cust_site_uses_all hcsu

  WHERE 1                      =1

  AND hca.cust_account_id      = hcsa.cust_account_id

  AND acr.customer_site_use_id = hcsu.site_use_id

  AND hcsa.cust_acct_site_id   = hcsu.cust_acct_site_id

  AND hps.party_site_id        = hcsa.party_site_id

  ) AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,  

  (SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rctta.name ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa,

ra_cust_trx_types_all rctta

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id

GROUP BY rctta.name,rcta.customer_trx_id

  ))  AR_TRXTYPE,

  (SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rabs.name ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa,

ra_batch_sources_all rabs

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND rabs.batch_source_seq_id = rcta.batch_source_seq_id

AND araa.status='APP'

GROUP BY rabs.name,rcta.customer_trx_id

  )) AR_TRXSOURCE,

  acr.RECEIPT_NUMBER AR_RECEIPTNUM,

  TO_CHAR(acr.RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american') AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ar_cash_receipts_all acr,

  hz_cust_accounts hca,

  hz_parties hp

WHERE 1                            =1

AND acr.cash_receipt_id           = xte.source_id_int_1

AND DGSD.entity_id                  = xte.entity_id

AND DGSD.je_source                = 'Receivables'

AND DGSD.je_category             = 'Receipts'

AND acr.pay_from_customer     = hca.cust_account_id(+)

AND hca.party_id                      = hp.party_id(+)

UNION ALL

--Cash Management

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  cet.transaction_type CE_TRX_TYPE,

  TO_CHAR(cet.transaction_date, 'DD-MON-YYYY','nls_date_language=american') CE_TRX_DATE,

  cet.reference_text CE_REF,

  cet.description CE_DESC,

  to_char(csh.statement_number) CE_STMT_IDENTIFIER,

  TO_CHAR(csh.statement_date, 'DD-MON-YYYY','nls_date_language=american') CE_STMT_DATE,

  to_char(ieba.bank_account_num) CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ce_bank_accounts   cba,

  ce_statement_headers  csh,

  iby_ext_bank_accounts  ieba,

  ce_external_transactions cet

WHERE 1                 =1

AND xte.source_id_int_1 = cet.transaction_id

AND xte.entity_id       = DGSD.entity_id

and cet.bank_account_id              = cba.bank_account_id

and csh.bank_account_id              = cba.bank_account_id

and CET.bank_account_id              = ieba.ext_bank_account_id

AND DGSD.je_source      = 'Cash Management'

UNION ALL

--Assets Depreciation

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

  fl.segment1 FA_ASST_LOC_COUNTRY,

  fl.segment2 FA_ASST_LOC_STATE,

  fl.segment3 FA_ASST_LOC_CITY,

  fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_category_book_defaults fcb,

  fa_methods fam

WHERE 1                   =1

AND fas.asset_id          = xte.source_id_int_1

AND fasl.asset_id         = fas.asset_id

AND fab.transaction_header_id_out is null

AND fasl.language         = USERENV('LANG')

AND fab.book_type_code    = xte.source_id_char_1

AND fab.book_type_code    = fdh.book_type_code

AND fab.transaction_header_id_out is null

AND fas.asset_category_id = fac.category_id

AND fab.asset_id          = fas.asset_id

AND fcb.category_id       = fac.category_id

AND fcb.book_type_code    = fab.book_type_code

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

--AND DGSD.code_combination_id = fdh.code_combination_id

AND fcb.method_id         = fam.method_id

and fab.asset_id                      = fdh.asset_id

and fdh.location_id                  = fl.location_id

AND DGSD.entity_id        = xte.entity_id

AND DGSD.je_source        = 'Assets'

AND DGSD.je_category      = 'Depreciation'

AND xte.entity_code ='DEPRECIATION'

UNION ALL

--Assets Addition

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

   fl.segment1 FA_ASST_LOC_COUNTRY,

          fl.segment2 FA_ASST_LOC_STATE,

          fl.segment3 FA_ASST_LOC_CITY,

          fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_category_book_defaults fcb,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_transaction_headers fth,

  fa_methods fam

WHERE 1                       =1

AND fth.transaction_header_id = xte.source_id_int_1

AND fas.asset_id              = fth.asset_id

AND fas.asset_id              = fab.asset_id

AND fasl.asset_id             = fas.asset_id

AND fasl.language             = USERENV('LANG')

AND fab.book_type_code        = xte.source_id_char_1

AND fab.transaction_header_id_out is null

AND fab.book_type_code        = fth.book_type_code

AND fab.book_type_code        = fdh.book_type_code

AND fab.transaction_header_id_out is null

AND fas.asset_category_id     = fac.category_id

AND fcb.category_id           = fac.category_id

and fab.asset_id              = fdh.asset_id

and fdh.location_id           = fl.location_id

AND fcb.book_type_code        = fab.book_type_code

AND fcb.method_id             = fam.method_id

AND DGSD.entity_id            = xte.entity_id

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

AND DGSD.JE_SOURCE            = 'Assets'

AND DGSD.je_category         IN ('Transfer', 'Retirement', 'Adjustment', 'CIP Adjustment', 'Addition', 'CIP Addition')

UNION ALL

--FA LEASE

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

  fl.segment1 FA_ASST_LOC_COUNTRY,

  fl.segment2 FA_ASST_LOC_STATE,

  fl.segment3 FA_ASST_LOC_CITY,

  fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  fal.lease_number     FA_LEASE_NUMBER,

  fal.description      FA_LEASE_DESCRIPTION,

  to_char(fal.lease_start_date, 'DD-MON-YYYY','nls_date_language=american') FA_LEASE_START_DATE,

  to_char(fal.lease_end_date, 'DD-MON-YYYY','nls_date_language=american')   FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_category_book_defaults fcb,

  FA_LEASES fal,

  fa_methods fam

WHERE 1                   =1

AND fas.asset_id          = xte.source_id_int_1

AND fal.lease_id          = fab.lease_id

AND fasl.asset_id         = fas.asset_id

AND fab.transaction_header_id_out is null

AND fasl.language         = USERENV('LANG')

AND fab.book_type_code    = xte.source_id_char_1

AND fab.transaction_header_id_out is null

AND fas.asset_category_id = fac.category_id

AND fab.asset_id          = fas.asset_id

AND fcb.category_id       = fac.category_id

AND fcb.book_type_code    = fab.book_type_code

AND fab.book_type_code    = fdh.book_type_code

AND fcb.method_id         = fam.method_id

and fab.asset_id          = fdh.asset_id

and fdh.location_id       = fl.location_id

AND DGSD.entity_id        = xte.entity_id

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

AND DGSD.je_source        = 'Assets'

AND DGSD.je_category      = 'Lease Expense'

AND xte.entity_code ='LEASE_EXPENSE'

UNION ALL

--All Other Sources

SELECT 

 glg.NAME LEDGER,

 gjh.period_name PERIOD,

 TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,

 data_acc_set.flex_segment_value accounting_entity,

 gcc.segment2 cost_centre,

 gcc.segment3 nominal_account,

 gcc.segment4 sub_analysis,

 gcc.segment5 intercompany,

 gcc.segment6 location,

 gcc.segment7 project,

 gcc.segment8 flow,

 gcc.segment9 spare1,

 gcc.segment10 spare2,

 (gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10))CODE_COMBINATION_DESC,

 gjs.user_je_source_name JE_SOURCE,

 gjc.user_je_category_name JE_CATEGORY,

 gjh.doc_sequence_value DOC_SEQ, 

 TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,

 gjl.currency_code ENT_CUR,

 nvl(xal.entered_dr, gjl.entered_dr) ENT_DR,

 nvl(xal.entered_cr, gjl.entered_cr) ENT_CR,

 nvl(nvl(xal.entered_dr, gjl.entered_dr),0)-nvl(nvl(xal.entered_cr, gjl.entered_cr),0) ENT_NET,

 nvl(xal.accounted_dr, gjl.accounted_dr) ACCTD_DR,

 nvl(xal.accounted_cr, gjl.accounted_cr) ACCTD_CR,

 nvl(nvl(xal.accounted_dr, gjl.accounted_dr),0)-nvl(nvl(xal.accounted_cr, gjl.accounted_cr),0) ACCTD_NET,

 nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,

 TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,

 nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,

 gjb.name BATCH,

 gjb.description BATCH_DESC,

 gjh.name JOUR_NAME,

 gjh.description JOUR_DESC,

 gjl.description JOURLINE_DESC,

 gjh.created_by JOUR_PREP,

 (SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,

 --DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,

 decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,

TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american')  REV_DATE,

fsv_acc_seq.header_name      ACC_SEQUENCE_NAME,

gjh.posting_acct_seq_value   ACC_SEQUENCE_NUMBER,

fsv_rep_seq.header_name      REP_SEQUENCE_NAME,

gjh.close_acct_seq_value     REP_SEQUENCE_NUMBER,

gjl.je_line_num              JOURNAL_LINE_NUMBER,

NULL AP_ACCOUNTING_TYPE,

NULL AP_INV_NUMBER,

NULL AP_INV_TYPE,

NULL AP_SOURCE,

NULL AP_INV_DATE,

NULL AP_INVOICE_AMOUNT,

NULL AP_SUP_NAME,

NULL AP_SUP_NUM,

NULL AP_SUPSITE,

NULL AP_SUPTYPE,

NULL AP_REQUESTOR,

NULL AP_INVDESC,

NULL AP_INVLINEDESC,

NULL AP_INVLINE_TYPE,

NULL AP_PO,

NULL AP_POREQ,

NULL AP_RECEIPT,

NULL AP_PMT,

NULL AP_PAYMENT_TERMS,

NULL AP_PAYMENT_DUE_DATE,

NULL AP_PAYMENT_TERM_DATE,

NULL AP_PAYMENT_DATE,

NULL AP_PAYMENT_STATUS,

NULL AP_XX_BANK_ACC_NUMB,

NULL FA_ACCOUNTING_TYPE,

NULL FA_ASSET_NUM,

NULL FA_TRANS_CATE,

NULL FA_MAJ_CATE,

NULL FA_MIN_CATE,

NULL FA_ASSET_TYPE,

NULL FA_ASSET_DESC,

NULL FA_DATE_PLACED,

NULL FA_ASST_LOC_COUNTRY,

NULL FA_ASST_LOC_STATE,

NULL FA_ASST_LOC_CITY,

NULL FA_ASST_LOC_SPARE,

NULL FA_ASST_REM_LIFE,

NULL FA_ASST_DEP_MET,

NULL FA_USEFUL_LIFE,

NULL FA_BOOK_TYPE_CODE,

NULL FA_LEASE_NUMBER,

NULL FA_LEASE_DESCRIPTION,

NULL FA_LEASE_START_DATE,

NULL FA_LEASE_END_DATE,

NULL AR_ACCOUNTING_TYPE,

NULL AR_TRXNUM,

NULL AR_INV_LINE_DESC,

NULL AR_TRXDATE,

NULL AR_TRXAMT,

NULL AR_CUSTNAME,

NULL AR_CUSTSITE,

NULL AR_CUSTNUM,

NULL AR_TRXTYPE,

NULL AR_TRXSOURCE,

NULL AR_RECEIPTNUM,

NULL AR_RECEIPTDATE,

NULL CE_TRX_TYPE,

NULL CE_TRX_DATE,

NULL CE_REF,

NULL CE_DESC,

NULL CE_STMT_IDENTIFIER,

NULL CE_STMT_DATE,

NULL CE_XX_BANK_ACC,

gjl.REFERENCE_1 GL_REF1 

FROM

  gl_je_headers gjh,

  gl_je_lines  gjl,

  gl_ledgers glg,

  gl_je_batches gjb,

  gl_code_combinations gcc,

  gl_import_references gir,

  fun_seq_versions fsv_acc_seq,

  fun_seq_versions fsv_rep_seq,

  gl_je_sources gjs,

  gl_je_categories gjc,

  xla_ae_lines xal,

  data_acc_set

WHERE 1=1

 AND gjl.je_header_id=gjh.je_header_id

 AND glg.ledger_id = gjh.ledger_id

 AND gjs.je_source_name = gjh.je_source

 AND gjc.je_category_name = gjh.je_category

 AND gjs.LANGUAGE = USERENV('LANG')

 AND gjc.LANGUAGE = USERENV('LANG')

 AND gjh.je_batch_id = gjb.je_batch_id

 AND gjl.je_header_id = gir.je_header_id(+)

 AND gjl.je_line_num = gir.je_line_num(+)

 AND gir.gl_sl_link_id = xal.gl_sl_link_id(+)

 AND gir.gl_sl_link_table = xal.gl_sl_link_table(+)

 AND gcc.code_combination_id=gjl.code_combination_id

 and gjh.posting_acct_seq_version_id  = fsv_acc_seq.seq_version_id(+)

 and gjh.close_acct_seq_version_id    = fsv_rep_seq.seq_version_id(+)

 AND gjh.status='P'

 AND gjh.actual_flag='A'

 AND gjh.je_source NOT IN ('Payables','Receivables','Cash Management','Assets')

-- AND glg.NAME                 = :P_LEDGER

   and (data_acc_set.ledger_name = :p_ledger

    or data_acc_set.ledger_set_name = :p_ledger)

   and glg.ledger_id             = data_acc_set.ledger_id

and ((coalesce(null, :p_acct_entity) is null)

    or (data_acc_set.flex_segment_value in (:p_acct_entity)))

 and ((coalesce(null, :p_cost_centre) is null)

    or (gcc.segment2 in (:p_cost_centre)))

 and ((coalesce(null, :p_account) is null)

    or (gcc.segment3 in (:p_account)))

 and ((coalesce(null, :p_sub_analysis) is null)

    or (gcc.segment4 in (:p_sub_analysis)))

 and ((coalesce(null, :p_intercmpny) is null)

    or (gcc.segment5 in (:p_intercmpny)))

 and ((coalesce(null, :p_location) is null)

    or (gcc.segment6 in (:p_location)))

 and ((coalesce(null, :p_project) is null)

    or (gcc.segment7 in (:p_project)))

 and ((coalesce(null, :p_flow) is null)

    or (gcc.segment8 in (:p_flow)))

 and ((coalesce(null, :p_spare1) is null)

    or (gcc.segment9 in (:p_spare1)))

 and ((coalesce(null, :p_spare2) is null)

    or (gcc.segment10 in (:p_spare2)))

 and ((coalesce(null, :P_JE_SOURCE) is null)

    or (gjs.user_je_source_name in (:P_JE_SOURCE)))

 and ((coalesce(null, :P_JE_CATEGORY) is null)

    or (gjc.user_je_category_name in (:P_JE_CATEGORY)))

and gjh.period_name in (select period_name

                             from gl_periods

                            where start_date     >= (select start_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_FROM

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and end_date       <= (select end_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_TO

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and period_set_name = 'XX_GLOBAL_CAL')