APPS.JA_CN_AP_VALIDATION_PKG SQL Statements

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

Line: 64

SELECT DISTINCT part.party_name Customer_Name
  FROM HZ_CUST_ACCOUNTS cust,
       HZ_PARTIES       part,
       AP_INVOICES_ALL  aia
 WHERE cust.cust_account_id = aia.party_id
   AND cust.party_id = part.party_id
   AND aia.Invoice_Id = ln_invoice_id; --variable: ln_invoice_id

Line: 74

SELECT  Aia.Invoice_Id               Invoice_Id
 FROM Ap_Invoices_All              Aia,
      Xla_Transaction_Entities     Ent,
      Xla_Ae_Headers               Aeh
WHERE Ent.Application_Id = 200
  AND Aia.Invoice_Id = Ent.Source_Id_Int_1
  AND Ent.Entity_Code = 'AP_INVOICES'
  AND Ent.Entity_Id = Aeh.Entity_Id
  AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
  AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
UNION
SELECT  Aia.Invoice_Id             Invoice_Id
 FROM Ap_Invoices_All              Aia,
      Ap_Checks_All                Ach,
      Ap_Invoice_Payments_All      Aip,
      Xla_Transaction_Entities     Ent,
      Xla_Ae_Headers               Aeh
WHERE Ent.Application_Id = 200
  AND Aia.Invoice_Id = Aip.Invoice_Id
  AND Ach.Check_Id = Aip.Check_Id
  AND Ach.Check_Id = Ent.Source_Id_Int_1
  AND Ent.Entity_Code = 'AP_PAYMENTS'
  AND Ent.Entity_Id = Aeh.Entity_Id
  AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
  AND Aeh.Ledger_Id = pn_ledger_id; --parameter: pn_ledger_id

Line: 194

SELECT DISTINCT
       Jeh.Je_Header_Id             Je_Header_Id,
       Aeh.Ae_Header_Id             Ae_Header_Id,
       Ael.Party_Id                 Supplier_Id,
       Gp.Start_date                Start_Date,
       Jeh.Period_Name              Period_Name,
       Sup.Vendor_Name              Supplier_Name,
       Aia.Invoice_Num              Invoice_Number,
       JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) Bal_Seg_Value
  FROM Gl_Je_Lines              Jel,
       Gl_Je_Headers            Jeh,
       Gl_Import_References     Gir,
       Ja_Cn_Code_Combination_v Jcc,
       Gl_Ledgers               Gl,
       Gl_Periods               Gp,
       Xla_Ae_Lines             Ael,
       Xla_Ae_Headers           Aeh,
       Ap_Suppliers             Sup,
       Xla_Transaction_Entities Ent,
       Ap_Invoices_All          Aia
 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
   AND Gir.Je_Header_Id = Jeh.Je_Header_Id
   AND Gir.Je_Line_Num = Jel.Je_Line_Num
   AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
   AND Jcc.Ledger_id = Jeh.Ledger_id
   AND Gl.Ledger_Id = Jcc.ledger_id
   AND Jeh.Status = 'P' --only collecting posted payable
   --Period condition
   AND Gl.period_set_name = Gp.period_set_name
   AND Jeh.Period_Name = Gp.Period_Name
   AND Gl.accounted_period_type = Gp.period_type
   AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
   AND Gp.start_date BETWEEN
       (SELECT Gp1.Start_Date
          FROM Gl_Periods Gp1
         WHERE Gl.period_set_name = Gp1.period_set_name
           AND Gl.accounted_period_type = Gp1.period_type
           AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
   AND (SELECT Gp2.Start_Date
          FROM Gl_Periods Gp2
         WHERE Gl.period_set_name = Gp2.period_set_name
           AND Gl.accounted_period_type = Gp2.period_type
           AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
   --SLA condition
   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.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
   AND Ael.Party_Id = Sup.Vendor_Id(+)
   AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <>  'EMPLOYEE' --not considering employee supplier
   AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
   ---Invoice level condition
   AND Ent.Application_Id = 200
   AND Aia.Invoice_Id = Ent.Source_Id_Int_1
   AND Ent.Entity_Code = 'AP_INVOICES'
   AND Ent.Entity_Id = Aeh.Entity_Id
   --BSV condition
   AND Aia.Legal_Entity_Id = pn_legal_entity_id --parameter: pn_legal_entity_id  --Updated for fixing bug# 9763810
   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
   ORDER BY Gp.start_date, Ael.Party_Id;

Line: 263

SELECT DISTINCT
       Jeh.Je_Header_Id             Je_Header_Id,
       Aeh.Ae_Header_Id             Ae_Header_Id,
       Ael.Party_Id                 Supplier_Id,
       Gp.Start_date                Start_Date,
       Jeh.Period_Name              Period_Name,
       Sup.Vendor_Name              Supplier_Name,
       Jeh.Name                     Journal_Name,
       ''                           Inv_Pay_Number
  FROM Gl_Je_Lines              Jel,
       Gl_Je_Headers            Jeh,
       Gl_Import_References     Gir,
       Ja_Cn_Code_Combination_v Jcc,
       Gl_Ledgers               Gl,
       Gl_Periods               Gp,
       Xla_Ae_Lines             Ael,
       Xla_Ae_Headers           Aeh,
       Ap_Suppliers             Sup
 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
   AND Gir.Je_Header_Id = Jeh.Je_Header_Id
   AND Gir.Je_Line_Num = Jel.Je_Line_Num
   AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
   AND Jcc.Ledger_id = Jeh.Ledger_id
   AND Gl.Ledger_Id = Jcc.ledger_id
   AND Jeh.Status = 'P' --only collecting posted payable
   AND NVL(Jel.Global_Attribute7,'U') <> 'P' --picking up those unitemzied invoices
   --Period condition
   AND Gl.period_set_name = Gp.period_set_name
   AND Jeh.Period_Name = Gp.Period_Name
   AND Gl.accounted_period_type = Gp.period_type
   AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
   AND Gp.start_date BETWEEN
       (SELECT Gp1.Start_Date
          FROM Gl_Periods Gp1
         WHERE Gl.period_set_name = Gp1.period_set_name
           AND Gl.accounted_period_type = Gp1.period_type
           AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
   AND (SELECT Gp2.Start_Date
          FROM Gl_Periods Gp2
         WHERE Gl.period_set_name = Gp2.period_set_name
           AND Gl.accounted_period_type = Gp2.period_type
           AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
   --SLA condition
   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.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
   AND Ael.Party_Id = Sup.Vendor_Id(+)
   AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <>  'EMPLOYEE' --not considering employee supplier
   AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
   --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
   ORDER BY Gp.start_date, Ael.Party_Id;

Line: 325

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: 338

SELECT  Aia.Invoice_Num            Inv_Pay_Number
 FROM Ap_Invoices_All              Aia,
      Xla_Transaction_Entities     Ent,
      Xla_Ae_Headers               Aeh
WHERE Ent.Application_Id = 200
  AND Aia.Invoice_Id = Ent.Source_Id_Int_1
  AND Ent.Entity_Code = 'AP_INVOICES'
  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  TO_CHAR(Aca.Check_Number)  Inv_Pay_Number
 FROM Ap_Checks_All                Aca,
      Xla_Transaction_Entities     Ent,
      Xla_Ae_Headers               Aeh
WHERE Ent.Application_Id = 200
  AND Aca.Check_Id = Ent.Source_Id_Int_1
  AND Ent.Entity_Code = 'AP_PAYMENTS'
  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: 387

  DELETE
    FROM   JA_CN_LEDGER_LE_BSV_GT;

Line: 397

  SELECT name
    INTO lv_le_name
    FROM XLE_ENTITY_PROFILES
   WHERE legal_entity_id=pn_legal_entity_id;

Line: 403

   SELECT name
     INTO lv_ledger_name
     FROM gl_ledgers
    WHERE ledger_id=pn_ledger_id;

Line: 411

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

Line: 417

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

Line: 421

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

Line: 426

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

Line: 430

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

Line: 435

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

Line: 439

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

Line: 444

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

Line: 448

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

Line: 453

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

Line: 466

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

Line: 470

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

Line: 480

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

Line: 490

      SELECT XMLELEMENT("PV_SUPPLIER_NAME",
                        lv_supplier_name)
        INTO L_XML_ITEM
        FROM DUAL;

Line: 494

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

Line: 500

      SELECT XMLELEMENT("PV_INVOICE_NUMBER",
                        v_invalid_bsv_inv_row.Invoice_Number)
        INTO L_XML_ITEM
        FROM DUAL;

Line: 504

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

Line: 512

          SELECT XMLELEMENT("PV_LEGAL_ENTITY",
                            v_legal_entity_row.Legal_Entity)
            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_BSV",
                        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: 539

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

Line: 543

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

Line: 553

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

Line: 560

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

Line: 564

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

Line: 573

      SELECT XMLELEMENT("PV_SUPPLIER_NAME",
                        lv_supplier_name)
        INTO L_XML_ITEM
        FROM DUAL;

Line: 577

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

Line: 585

          SELECT XMLELEMENT("PV_INV_PAY_NUMBER",
                            v_inv_pay_num_row.Inv_Pay_Number)
            INTO L_XML_ITEM
            FROM DUAL;

Line: 589

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

Line: 596

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

Line: 600

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

Line: 606

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