ECC Receivables, AR Closing, SQL2

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Type, Period Name, Period Year, Closing Status, Ledger Id, Ledger Name, Ledger Currency, Org Id, Set Of Books Id ...
Imported from Enterprise Command Center
Dataset Key: ar-period-close
Query Procedure: AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
select
x.*
from
(
select * from (SELECT  ECC_SPEC_ID,
 RECORD_TYPE,
 PERIOD_NAME,
 PERIOD_YEAR,
 CLOSING_STATUS,
 ledger_id,
 ledger_name,
 ledger_currency,
 org_id,
operating_unit,
 set_of_books_id,
 customer_trx_id,
 amount,
 accounted_amount,
 transaction_number,
 transaction_date,
 transaction_currency,
 transaction_type,
 invoicing_rule_id,
 invoicing_rule,
 XLA_ACC_CODE ,
 XLA_ACC_DESCRIPTION,
 CUST_TRX_LINE_GL_DIST_ID,
 bill_to_customer_id,
 account_number,
 bill_to_customer,
 bill_to_location,
 payment_schedule_id,
 gl_date,
 event_id,
 event_number,
 event_date,
 EVENT_STATUS_CODE,
 EVENT_STATUS_DESC,
 PROCESS_STATUS_CODE,
 PROCESS_STATUS_DESC,
 ACCOUNTING_LINE_CODE,
 ACCOUNTING_LINE_DESC,
 SOURCE_DISTRIBUTION_TYPE,
 EVENT_CLASS_CODE,
 EVENT_CLASS_DESC,
 LINE_DEFINITION_CODE,
 LINE_DEFINITION_DESC,
 EVENT_TYPE_CODE,
 EVENT_TYPE_DESC,
 AE_HEADER_ID,
 GL_TRANSFER_STATUS_CODE,
 GL_TRANSFER_STATUS_DESC,
 ACC_ENTRY_STATUS_CODE,
 ACC_ENTRY_STATUS_DESC,
 ACCOUNTING_ENTRY_TYPE_code,
 ACCOUNTING_ENTRY_TYPE_DESC,
 JE_CATEGORY_NAME,
 JE_CATEGORY_NAME_DESC,
 HEADER_DESCRIPTION,
 BALANCE_TYPE_CODE,
 BALANCE_TYPE_DESC,
 gl_period_name,
 ACCOUNTING_DATE,
 AE_LINE_NUM,
 DISPLAYED_LINE_NUMBER,
 ACCOUNTING_CLASS_CODE,
 ACCOUNTING_CLASS_DESC,
 BUSINESS_CLASS_CODE,
 BUSINESS_CLASS_DESC,
 CODE_COMBINATION_ID,
 CURRENCY_CODE,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 LINES_DESCRIPTION,
 ENTITY_ID,
 ENTITY_CODE,
 SOURCE_ID_INT_1,
 ecc_last_update_date,
 language
 FROM ( select   /*+ leading ( xah xdl xal ragd rct xe) push_pred ( xdl )  */
       rct.customer_trx_id||'-'||ragd.CUST_TRX_LINE_GL_DIST_ID||'-'||xe.EVENT_ID||'-'||xe.EVENT_NUMBER||'-'||XAL.AE_HEADER_ID||'-'||XAL.AE_LINE_NUM as ECC_SPEC_ID,
       'TRX' RECORD_TYPE,
       ps.PERIOD_NAME,
       ps.PERIOD_YEAR,
       ps.CLOSING_STATUS,
       led.ledger_id,
       led.name ledger_name,
       led.currency_code ledger_currency,
       rct.org_id,
       org.name operating_unit,
       rct.set_of_books_id,
       rct.customer_trx_id,
       arp.AMOUNT_DUE_ORIGINAL amount,
       arp.amount_due_original*nvl(arp.exchange_rate,1) ACCOUNTED_AMOUNT,
       rct.TRX_NUMBER transaction_number,
       rct.TRX_DATE transaction_date,
       rctt.name transaction_type,
       rct.INVOICE_CURRENCY_CODE transaction_currency,
       rct.INVOICING_RULE_ID,
	   rr.NAME invoicing_rule,
      rct.bill_to_customer_id,
      b_bill.account_number account_number,
      b_bill_party.party_name bill_to_customer,
      u_bill.location bill_to_location,
      arp.PAYMENT_SCHEDULE_ID,
    DECODE (xal.CODE_COMBINATION_ID, NULL, NULL,-1,NULL,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1
                (P_LEXICAL_NAME => 'ACCOUNT CODE'
                , P_APPLICATION_SHORT_NAME => 'SQLGL'
                , P_ID_FLEX_CODE => 'GL#'
                , P_ID_FLEX_NUM => led.CHART_OF_ACCOUNTS_ID
                , P_DATA_SET => led.CHART_OF_ACCOUNTS_ID
                , P_CCID => xal.CODE_COMBINATION_ID
                , P_SEGMENTS => 'GL_ACCOUNT'
                , P_SHOW_PARENT_SEGMENTS => 'Y'
                , P_OUTPUT_TYPE=>'VALUE')) AS XLA_ACC_CODE ,
   DECODE(xal.CODE_COMBINATION_ID, NULL, NULL,-1,NULL,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1
                (P_LEXICAL_NAME => 'ACCOUNT CODE'
                , P_APPLICATION_SHORT_NAME => 'SQLGL'
                , P_ID_FLEX_CODE => 'GL#'
                , P_ID_FLEX_NUM => led.CHART_OF_ACCOUNTS_ID
                , P_DATA_SET => led.CHART_OF_ACCOUNTS_ID
                , P_CCID => xal.CODE_COMBINATION_ID
                , P_SEGMENTS => 'GL_ACCOUNT'
                , P_SHOW_PARENT_SEGMENTS => 'Y'
                , P_OUTPUT_TYPE=>'DESCRIPTION')) AS XLA_ACC_DESCRIPTION,
      ragd.CUST_TRX_LINE_GL_DIST_ID,
      arp.gl_date,
      xe.event_id,
    xe.EVENT_NUMBER,
    xe.EVENT_DATE,
    xe.EVENT_STATUS_CODE,
      (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_EVENT_STATUS'  and language=org.language and lookup_code = xe.EVENT_STATUS_CODE)   EVENT_STATUS_DESC,
    DECODE(xe.PROCESS_STATUS_CODE,'Z','I',xe.PROCESS_STATUS_CODE) PROCESS_STATUS_CODE,
      (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_EVENT_PROCESS_STATUS'  and language=org.language and lookup_code = DECODE(xe.PROCESS_STATUS_CODE,'Z','I',xe.PROCESS_STATUS_CODE))
    	  PROCESS_STATUS_DESC ,
    xah.EVENT_TYPE_CODE,
      (select NAME from XLA_EVENT_TYPES_TL ett where application_id =222 and language=org.language AND xah.EVENT_TYPE_CODE = ett. EVENT_TYPE_CODE AND xte.ENTITY_CODE = ett.ENTITY_CODE)   EVENT_TYPE_DESC,
    xte.ENTITY_ID,
    xte.ENTITY_CODE,
    xte.SOURCE_ID_INT_1,
    xdl.ACCOUNTING_LINE_CODE,
	  (select NAME from XLA_ACCT_LINE_TYPES_TL alt where application_id =222 and alt.AMB_CONTEXT_CODE = 'DEFAULT' and language=org.language AND xdl.ACCOUNTING_LINE_CODE = alt.ACCOUNTING_LINE_CODE)   ACCOUNTING_LINE_DESC,
      xdl.SOURCE_DISTRIBUTION_TYPE,
    xdl.EVENT_CLASS_CODE,
	  (select NAME from XLA_EVENT_CLASSES_TL ect where application_id =222 and language=org.language AND xdl.EVENT_CLASS_CODE = ect.EVENT_CLASS_CODE  AND xte.ENTITY_CODE = ect.ENTITY_CODE)   EVENT_CLASS_DESC,
    xdl.LINE_DEFINITION_CODE,
 (select NAME from XLA_LINE_DEFINITIONS_TL ldt where application_id =222 and ldt.AMB_CONTEXT_CODE = 'DEFAULT' and language=org.language AND xdl.EVENT_CLASS_CODE = ldt.EVENT_CLASS_CODE  AND xdl.LINE_DEFINITION_CODE = ldt.LINE_DEFINITION_CODE)
     LINE_DEFINITION_DESC,
    xah.AE_HEADER_ID,
    xah.GL_TRANSFER_STATUS_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'GL_TRANSFER_FLAG' and language=org.language and lookup_code = xah.GL_TRANSFER_STATUS_CODE) GL_TRANSFER_STATUS_DESC,
    xah.ACCOUNTING_ENTRY_STATUS_CODE ACC_ENTRY_STATUS_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS' and language=org.language and lookup_code = xah.ACCOUNTING_ENTRY_STATUS_CODE) ACC_ENTRY_STATUS_DESC,
    xah.ACCOUNTING_ENTRY_TYPE_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_ENTRY_TYPE' and language=org.language and lookup_code = xah.ACCOUNTING_ENTRY_TYPE_CODE) ACCOUNTING_ENTRY_TYPE_DESC,
    xah.JE_CATEGORY_NAME,
       (select DESCRIPTION from GL_JE_CATEGORIES_TL jct where language=org.language and xah.JE_CATEGORY_NAME = jct.JE_CATEGORY_NAME) JE_CATEGORY_NAME_DESC,
    xah.DESCRIPTION HEADER_DESCRIPTION,
    xah.BALANCE_TYPE_CODE,
       (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_BALANCE_TYPE'   and language=org.language and lookup_code = xah.BALANCE_TYPE_CODE) BALANCE_TYPE_DESC,
    xah.PERIOD_NAME gl_period_name,
    xal.ACCOUNTING_DATE,
    xal.AE_LINE_NUM,
    xal.DISPLAYED_LINE_NUMBER,
    xal.ACCOUNTING_CLASS_CODE,
        (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_ACCOUNTING_CLASS'  and language=org.language and lookup_code = xal.ACCOUNTING_CLASS_CODE) ACCOUNTING_CLASS_DESC,
    xal.BUSINESS_CLASS_CODE,
        (select meaning from fnd_lookup_values where view_application_id =602 and lookup_type = 'XLA_BUSINESS_FLOW_CLASS' and language=org.language and lookup_code = xal.BUSINESS_CLASS_CODE) BUSINESS_CLASS_DESC,
    xal.CODE_COMBINATION_ID,
    xal.CURRENCY_CODE,
    xal.ENTERED_DR,
    xal.ENTERED_CR,
    xal.ACCOUNTED_DR,
    xal.ACCOUNTED_CR,
    xal.DESCRIPTION LINES_DESCRIPTION,
    xe.last_update_date ecc_last_update_date,
    org.language
from
    gl_period_statuses ps,
    ra_customer_trx_all rct,
    ra_cust_trx_line_gl_dist_all ragd,
	ra_cust_trx_types_all rctt,
    AR_PAYMENT_SCHEDULES_ALL arp,
    xla_transaction_entities xte,
    xla_events xe,
  ( SELECT /*+ push_pred */ *  FROM  xla_distribution_links xdl0  WHERE
         xdl0.ref_ae_header_id || xdl0.temp_line_num = (  SELECT /*+ push_subq  */  xdl.ref_ae_header_id  || xdl.temp_line_num
                              FROM  xla_distribution_links xdl  WHERE application_id = 222 AND xdl.application_id = xdl0.application_id
                               AND   xdl.ae_header_id = xdl0.ae_header_id
                               AND   xdl.ae_line_num = xdl0.ae_line_num
                               AND   ROWNUM = 1  ) ) xdl,
    xla_ae_headers xah,
    xla_ae_lines xal,
    GL_CODE_COMBINATIONS cc,
    gl_ledgers led,
    hr_all_organization_units_tl org,
    RA_RULES rr  ,
    hz_cust_accounts   b_bill,
    hz_parties         b_bill_party ,
    HZ_CUST_SITE_USES_ALL    u_bill,
	( SELECT ccid.code_combination_id,  sav.id_flex_num chart_of_accounts_id
                      FROM
                            fnd_id_flex_segments s,
                            fnd_segment_attribute_values sav,
                            gl_code_combinations ccid
                      WHERE s.application_id = 101
                      AND   s.id_flex_code = 'GL#'
                      AND   s.enabled_flag = 'Y'
                      AND   sav.application_column_name = s.application_column_name
                      AND   sav.application_id = 101
                      AND   sav.id_flex_code = s.id_flex_code
                      AND   sav.id_flex_num = s.id_flex_num
                      AND   sav.attribute_value = 'Y'
                      AND   sav.segment_attribute_type = 'GL_ACCOUNT'
                      AND   ccid.chart_of_accounts_id = s.id_flex_num  ) account_segments
where
    ps.application_id=222
    AND ps.SET_OF_BOOKS_ID = rct.SET_OF_BOOKS_ID
    AND ragd.GL_DATE between ps.START_DATE and ps.END_DATE
    and rct.CUSTOMER_TRX_ID = ragd.CUSTOMER_TRX_ID
    AND ps.CLOSING_STATUS = 'O'
    AND rct.CUSTOMER_TRX_ID = xte.SOURCE_ID_INT_1
	and rr.RULE_ID (+)= rct.INVOICING_RULE_ID
	AND rct.cust_trx_type_id = rctt.cust_trx_type_id
    and rct.CUSTOMER_TRX_ID = arp.CUSTOMER_TRX_ID
    AND rct.org_id = rctt.org_id
    and xe.application_id = 222
	AND xe.PROCESS_STATUS_CODE <> 'U'
    AND xte.ENTITY_ID = xe.ENTITY_ID
    and xe.application_id = xte.application_id
    AND ragd.CUST_TRX_LINE_GL_DIST_ID = xdl.SOURCE_DISTRIBUTION_ID_NUM_1
    AND xdl.EVENT_ID = ragd.EVENT_ID
    AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
    AND xdl.application_id = XAH.APPLICATION_ID
	AND xdl.application_id = xal.APPLICATION_ID
    AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
    AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
    AND xah.ZERO_AMOUNT_FLAG = 'N'
    AND xah.GL_TRANSFER_STATUS_CODE <> 'Y'
	and xah.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'
    and xal.DISPLAYED_LINE_NUMBER >=0
	AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = xah.application_id
    AND XE.EVENT_ID = XAH.EVENT_ID
    AND XAH.APPLICATION_ID = XE.APPLICATION_ID
    AND Led.Ledger_Id  = rct.Set_Of_Books_Id
    and rct.org_id =org.Organization_Id
    AND rct.bill_to_customer_id = b_bill.cust_account_id
    AND b_bill.party_id = b_bill_party.party_id
    AND rct.bill_to_site_use_id = u_bill.site_use_id
    AND rct.org_id = u_bill.org_id
    AND cc.code_combination_id (+) = xal.code_combination_id
    AND account_segments.code_combination_id (+) = cc.code_combination_id   )  acc_trx where acc_trx.language in ('US')  )
    PIVOT(max(OPERATING_UNIT) as OPERATING_UNIT, max(EVENT_STATUS_DESC) as EVENT_STATUS_DESC, max(PROCESS_STATUS_DESC) as PROCESS_STATUS_DESC,
   max(ACCOUNTING_LINE_DESC) as ACCOUNTING_LINE_DESC, max(EVENT_CLASS_DESC) as EVENT_CLASS_DESC, max(LINE_DEFINITION_DESC) as LINE_DEFINITION_DESC,
   max(EVENT_TYPE_DESC) as EVENT_TYPE_DESC, max(GL_TRANSFER_STATUS_DESC) as GL_TRANSFER_STATUS_DESC, max(ACC_ENTRY_STATUS_DESC) as ACC_ENTRY_STATUS_DESC,
   max(ACCOUNTING_ENTRY_TYPE_DESC) as ACCOUNTING_ENTRY_TYPE_DESC, max(JE_CATEGORY_NAME_DESC) as JE_CATEGORY_NAME_DESC, max(BALANCE_TYPE_DESC) as BALANCE_TYPE_DESC,
   max(ACCOUNTING_CLASS_DESC) as ACCOUNTING_CLASS_DESC, max(BUSINESS_CLASS_DESC) as BUSINESS_CLASS_DESC
  for LANGUAGE in ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: