APPS.JA_CN_AR_VALIDATION_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 91

SELECT DISTINCT
      jeh.je_header_id         Je_Header_Id,
      aeh.ae_header_id         Ae_Header_Id,
      part.party_id            Party_Id,
      gps.start_date           Start_Date,
      gps.period_name          Period_Name,
      part.party_number        Customer_Number,
      part.party_name          Customer_Name,
      rct.trx_number           Transaction_Number,
      JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(jcc.code_combination_id) Bal_Seg_Value
FROM  GL_JE_HEADERS            jeh,
      GL_JE_LINES              jel,
      GL_IMPORT_REFERENCES     gir,
      XLA_AE_LINES             ael,
      XLA_AE_HEADERS           aeh,
      JA_CN_CODE_COMBINATION_V jcc,
      HZ_CUST_ACCOUNTS         cust,
      HZ_PARTIES               part,
      GL_PERIOD_STATUSES       gps,
      Xla_Transaction_Entities ent,
      RA_CUSTOMER_TRX_ALL      rct
WHERE jeh.je_header_id = jel.je_header_id
  AND jeh.je_header_id = gir.je_header_id
  AND jel.je_line_num = gir.je_line_num
  AND gir.gl_sl_link_id = ael.gl_sl_link_id
  AND gir.gl_sl_link_table = ael.gl_sl_link_table
  AND ael.ae_header_id = aeh.ae_header_id
  AND jcc.code_combination_id = jel.code_combination_id
  AND jcc.ledger_id = jeh.ledger_id
  AND cust.cust_account_id(+) = ael.party_id
  AND cust.party_id = part.party_id(+)
  AND jeh.period_name = gps.period_name
  AND jeh.ledger_id = gps.ledger_id
  AND jeh.status = 'P'  -- AR posted to GL
  AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
  --BSV Condition
  AND rct.legal_entity_id = pn_legal_entity_id
  AND NOT EXISTS (SELECT llbg.Bal_Seg_Value
                FROM ja_cn_ledger_le_bsv_gt llbg
               WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
                 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
                 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
  --ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
  AND gps.application_id = 101
  AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
       (gps.end_date BETWEEN ld_start_date AND ld_end_date))
  AND jeh.ledger_id = pn_ledger_id  --parameter: pn_ledger_id
  -- Transaction condition
  AND ent.Application_Id = 222
  AND rct.Customer_Trx_Id = ent.Source_Id_Int_1
  AND ent.Entity_Code = 'TRANSACTIONS'
  AND ent.Entity_Id = aeh.Entity_Id
  AND Aeh.Ledger_Id =jeh.ledger_id
  ORDER BY gps.start_date, part.party_id,rct.trx_number;

Line: 151

SELECT DISTINCT
      jeh.je_header_id         Je_Header_Id,
      aeh.ae_header_id         Ae_Header_Id,
      part.party_id            Party_Id,
      gps.start_date           Start_Date,
      gps.period_name          Period_Name,
      part.party_number        Customer_Number,
      part.party_name          Customer_Name,
      jeh.name                 Journal_Name,
      ''                       Trx_Receipt_Number
FROM  GL_JE_HEADERS            jeh,
      GL_JE_LINES              jel,
      GL_IMPORT_REFERENCES     gir,
      XLA_AE_LINES             ael,
      XLA_AE_HEADERS           aeh,
      JA_CN_CODE_COMBINATION_V jcc,
      HZ_CUST_ACCOUNTS         cust,
      HZ_PARTIES               part,
      GL_PERIOD_STATUSES       gps
WHERE jeh.je_header_id = jel.je_header_id
  AND jeh.je_header_id = gir.je_header_id
  AND jel.je_line_num = gir.je_line_num
  AND gir.gl_sl_link_id = ael.gl_sl_link_id
  AND gir.gl_sl_link_table = ael.gl_sl_link_table
  AND ael.ae_header_id = aeh.ae_header_id
  AND jcc.code_combination_id = jel.code_combination_id
  AND jcc.ledger_id = jeh.ledger_id
  AND cust.cust_account_id(+) = ael.party_id
  AND cust.party_id = part.party_id(+)
  AND jeh.period_name = gps.period_name
  AND jeh.ledger_id = gps.ledger_id
  AND jeh.status = 'P'  -- AR posted to GL
  AND NVL(Jel.Global_Attribute7,'*') <> 'P' --not itemized
  AND ael.accounting_class_code = 'RECEIVABLE'
  --BSV Condition
   AND EXISTS (SELECT llbg.Bal_Seg_Value
                    FROM ja_cn_ledger_le_bsv_gt llbg
                   WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
                     AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
                     AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
  --ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
  AND gps.application_id = 101
  AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
       (gps.end_date BETWEEN ld_start_date AND ld_end_date))
  AND jeh.ledger_id = pn_ledger_id
  ORDER BY gps.start_date, part.party_id;

Line: 202

SELECT xep.Name Legal_Entity
  FROM Xle_Entity_Profiles xep
      ,Gl_Ledger_Norm_Seg_Vals glnsv
 WHERE glnsv.ledger_id = pn_ledger_id --parameter: pn_ledger_id
   AND glnsv.legal_entity_id = xep.legal_entity_id
   AND glnsv.Segment_Value = lv_bal_seg_value --variable: lv_bal_seg_value
   AND glnsv.Segment_Type_Code = 'B'
   AND Nvl(glnsv.Status_Code, 'I') <> 'D';

Line: 215

SELECT Rct.Trx_Number               Trx_Receipt_Number
  FROM RA_CUSTOMER_TRX_ALL          Rct,
       Xla_Transaction_Entities     Ent,
       Xla_Ae_Headers               Aeh
 WHERE Ent.Application_Id = 222
   AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
   AND Ent.Entity_Code = 'TRANSACTIONS'
   AND Ent.Entity_Id = Aeh.Entity_Id
   AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
   AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
UNION
SELECT Acr.Receipt_Number           Trx_Receipt_Number
  FROM AR_CASH_RECEIPTS_ALL         Acr,
       Xla_Transaction_Entities     Ent,
       Xla_Ae_Headers               Aeh
 WHERE Ent.Application_Id = 222
   AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
   AND Ent.Entity_Code = 'RECEIPTS'
   AND Ent.Entity_Id = Aeh.Entity_Id
   AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
   AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
UNION
SELECT Rct.Trx_Number               Trx_Receipt_Number
  FROM RA_CUSTOMER_TRX_ALL          Rct,
       AR_ADJUSTMENTS_ALL           Adj,
       Xla_Transaction_Entities     Ent,
       Xla_Ae_Headers               Aeh
 WHERE Adj.Customer_Trx_Id = Rct.Customer_Trx_Id
   AND Ent.Application_Id = 222
   AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
   AND Ent.Entity_Code = 'ADJUSTMENTS'
   AND Ent.Entity_Id = Aeh.Entity_Id
   AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
   AND Aeh.Ledger_Id = pn_ledger_id; --parameter: pn_ledger_id

Line: 278

  DELETE FROM JA_CN_LEDGER_LE_BSV_GT;

Line: 289

    SELECT start_date
      INTO ld_start_date
      FROM GL_PERIOD_STATUSES
     WHERE ledger_id = pn_ledger_id
       AND application_id = 101
       AND period_name = pv_period_from
       AND to_char(period_year) = pv_accounting_year;

Line: 302

    SELECT end_date
      INTO ld_end_date
      FROM GL_PERIOD_STATUSES
     WHERE ledger_id = pn_ledger_id
       AND application_id = 101
       AND period_name = pv_period_to
       AND to_char(period_year) = pv_accounting_year;

Line: 328

  SELECT name
    INTO lv_le_name
    FROM XLE_ENTITY_PROFILES
   WHERE legal_entity_id = pn_legal_entity_id;

Line: 334

  SELECT name
    INTO lv_ledger_name
    FROM gl_ledgers
   WHERE ledger_id = pn_ledger_id;

Line: 343

  SELECT XMLELEMENT("PV_PERIOD_FROM",
                    pv_period_from)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 349

  SELECT XMLELEMENT("PV_PERIOD_TO",
                    pv_period_to)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 353

  SELECT XMLCONCAT(L_XML_PARAMETER,
                   L_XML_ITEM)
    INTO L_XML_PARAMETER
    FROM DUAL;

Line: 358

  SELECT XMLELEMENT("PV_ACCOUNTING_YEAR",
                    pv_accounting_year)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 362

  SELECT XMLCONCAT(L_XML_PARAMETER,
                   L_XML_ITEM)
    INTO L_XML_PARAMETER
    FROM DUAL;

Line: 367

  SELECT XMLELEMENT("PV_LEDGER_NAME",
                    lv_ledger_name)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 371

  SELECT XMLCONCAT(L_XML_PARAMETER,
                   L_XML_ITEM)
    INTO L_XML_PARAMETER
    FROM DUAL;

Line: 376

  SELECT XMLELEMENT("PV_LEGAL_ENTITY",
                    lv_le_name)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 380

  SELECT XMLCONCAT(L_XML_PARAMETER,
                   L_XML_ITEM)
    INTO L_XML_PARAMETER
    FROM DUAL;

Line: 385

  SELECT XMLCONCAT(L_XML_PARAMETER,
                   L_XML_REPORT)
    INTO L_XML_REPORT
    FROM DUAL;

Line: 395

  SELECT XMLELEMENT("PV_BSV_ROW_NAME",
                    Fnd_Message.Get)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 399

  SELECT XMLCONCAT(L_XML_REPORT,
                   L_XML_ITEM)
    INTO L_XML_REPORT
    FROM DUAL;

Line: 414

    SELECT XMLELEMENT("PV_PERIOD_NAME",
                      v_invalid_trx_row.Period_Name)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 420

    SELECT XMLELEMENT("PV_CUSTOMER_NAME",
                      v_invalid_trx_row.Customer_Name)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 424

    SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                     L_XML_ITEM)
      INTO L_XML_ROW_ITEMS
      FROM DUAL;

Line: 429

    SELECT XMLELEMENT("PV_TRANSACTION_NUMBER",
                      v_invalid_trx_row.Transaction_Number)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 433

    SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                     L_XML_ITEM)
      INTO L_XML_ROW_ITEMS
      FROM DUAL;

Line: 440

        SELECT XMLELEMENT("PV_LEGAL_ENTITY",
                          v_legal_entity_row.Legal_Entity)
          INTO L_XML_ITEM
          FROM DUAL;

Line: 444

        SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                         L_XML_ITEM)
          INTO L_XML_ROW_ITEMS
          FROM DUAL;

Line: 451

    SELECT XMLELEMENT("ROW_BSV",
                      L_XML_ROW_ITEMS)
      INTO L_XML_ROW
      FROM DUAL;

Line: 455

    SELECT XMLCONCAT(L_XML_REPORT,
                     L_XML_ROW)
      INTO L_XML_REPORT
      FROM DUAL;

Line: 467

  SELECT XMLELEMENT("PV_UNITEMIZED_ROW_NAME",
                    Fnd_Message.Get)
    INTO L_XML_ITEM
    FROM DUAL;

Line: 471

  SELECT XMLCONCAT(L_XML_REPORT,
                   L_XML_ITEM)
    INTO L_XML_REPORT
    FROM DUAL;

Line: 486

    SELECT XMLELEMENT("PV_PERIOD_NAME",
                      v_unitemized_trx_row.Period_Name)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 492

    SELECT XMLELEMENT("PV_JOURNAL_NAME",
                      v_unitemized_trx_row.Journal_Name)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 496

    SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                     L_XML_ITEM)
      INTO L_XML_ROW_ITEMS
      FROM DUAL;

Line: 501

    SELECT XMLELEMENT("PV_CUSTOMER_NAME",
                      v_unitemized_trx_row.Customer_Name)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 505

    SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                     L_XML_ITEM)
      INTO L_XML_ROW_ITEMS
      FROM DUAL;

Line: 512

      SELECT XMLELEMENT("PV_TRX_RECEIPT_NUMBER",
                        v_trx_receipt_num_row.TRX_RECEIPT_NUMBER)
        INTO L_XML_ITEM
        FROM DUAL;

Line: 516

      SELECT XMLCONCAT(L_XML_ROW_ITEMS,
                       L_XML_ITEM)
        INTO L_XML_ROW_ITEMS
        FROM DUAL;

Line: 523

    SELECT XMLELEMENT("ROW_UNITEMIZED",
                      L_XML_ROW_ITEMS)
      INTO L_XML_ROW
      FROM DUAL;

Line: 527

    SELECT XMLCONCAT(L_XML_REPORT,
                     L_XML_ROW)
      INTO L_XML_REPORT
      FROM DUAL;

Line: 534

  SELECT XMLELEMENT("REPORT",
                  L_XML_REPORT)
  INTO L_XML_ROOT
  FROM DUAL;