APPS.JA_CN_CFS_REPORT_PKG SQL Statements

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

Line: 76

                              x_Func_Amount      OUT NOCOPY NUMBER_TBL, --updated for bug  6697073

                              x_Invoice_Num      OUT NOCOPY INVOICE_NUM_TBL, --updated for bug  6697073
                              -- Fix bug#6697073 end ----------------------------

                              x_Third_Party_Name OUT NOCOPY VARCHAR2,
                              x_Third_Party_Num  OUT NOCOPY varchar2) IS
    l_Invoice_Id NUMBER;

Line: 96

    SELECT Aca.Check_Number, Pv.Vendor_Name, Pv.Segment1
      FROM Ap_Checks_All            Aca,
           Xla_Transaction_Entities Ent,
           Xla_Ae_Headers           Aeh,
           Po_Vendors               Pv
     WHERE Ent.Application_Id = 200
       AND Aca.Check_Id = Ent.Source_Id_Int_1
       AND Aca.Vendor_Id = Pv.Vendor_Id(+)
       AND Ent.Entity_Code = 'AP_PAYMENTS'
       AND Ent.Entity_Id = Aeh.Entity_Id
       AND Aeh.Ae_Header_Id = p_Ae_Header_Id
       AND Aeh.Ledger_Id = p_Ledger_Id;

Line: 110

    SELECT DISTINCT Applied_To_Source_Id_Num_1,
                    DECODE(NVL(UNROUNDED_ACCOUNTED_DR, '-1'),
                           '-1',
                           UNROUNDED_ACCOUNTED_CR,
                           -1 * UNROUNDED_ACCOUNTED_DR)
      FROM Xla_Distribution_Links
     WHERE Ae_Header_Id = p_Ae_Header_Id
       AND Ae_Line_Num = p_Ae_Line_Num;

Line: 144

/*    SELECT Aca.Check_Number, Pv.Vendor_Name, Pv.Segment1
      INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num
      FROM Ap_Checks_All            Aca,
           Xla_Transaction_Entities Ent,
           Xla_Ae_Headers           Aeh,
           Po_Vendors               Pv
     WHERE Ent.Application_Id = 200
       AND Aca.Check_Id = Ent.Source_Id_Int_1
       AND Aca.Vendor_Id = Pv.Vendor_Id(+)
       AND Ent.Entity_Code = 'AP_PAYMENTS'
       AND Ent.Entity_Id = Aeh.Entity_Id
       AND Aeh.Ae_Header_Id = p_Ae_Header_Id
       AND Aeh.Ledger_Id = p_Ledger_Id; */

Line: 168

            SELECT Invoice_Num
              INTO l_invoice_num_temp
              FROM Ap_Invoices_all
             WHERE Invoice_Id = l_Invoice_Id;

Line: 187

/*    SELECT DISTINCT Applied_To_Source_Id_Num_1
      INTO l_Invoice_Id
      FROM Xla_Distribution_Links
     WHERE Ae_Header_Id = p_Ae_Header_Id
       AND Ae_Line_Num = p_Ae_Line_Num;

Line: 195

      SELECT Invoice_Num
        INTO x_Invoice_Num
        FROM Ap_Invoices
       WHERE Invoice_Id = l_Invoice_Id;

Line: 267

    SELECT Aca.Receipt_Number, Hp.Party_Name, Cust.Account_Number
      FROM Ar_Cash_Receipts_All     Aca,
           Xla_Transaction_Entities Ent,
           Xla_Ae_Headers           Aeh,
           Hz_Cust_Accounts         Cust,
           Hz_Parties               Hp
     WHERE Ent.Application_Id = 222
       AND Aca.Cash_Receipt_Id = Ent.Source_Id_Int_1
       AND Aca.Pay_From_Customer = Cust.Cust_Account_Id(+)
       AND Cust.Party_Id = Hp.Party_Id(+)
       AND Ent.Entity_Code = 'RECEIPTS'
       AND Ent.Entity_Id = Aeh.Entity_Id
       AND Aeh.Ae_Header_Id = p_Ae_Header_Id
       AND Aeh.Ledger_Id = p_Ledger_Id;

Line: 282

    SELECT DISTINCT Applied_To_Source_Id_Num_1 + 0,
                    DECODE(NVL(UNROUNDED_ACCOUNTED_DR, '-1'),
                           '-1',
                           UNROUNDED_ACCOUNTED_CR,
                           -1 * UNROUNDED_ACCOUNTED_DR)
      FROM Xla_Distribution_Links
     WHERE Ae_Header_Id = p_Ae_Header_Id
       AND Ae_Line_Num = p_Ae_Line_Num;

Line: 311

/*    SELECT Aca.Receipt_Number, Hp.Party_Name, Cust.Account_Number
      INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num
      FROM Ar_Cash_Receipts_All     Aca,
           Xla_Transaction_Entities Ent,
           Xla_Ae_Headers           Aeh,
           Hz_Cust_Accounts         Cust,
           Hz_Parties               Hp
     WHERE Ent.Application_Id = 222
       AND Aca.Cash_Receipt_Id = Ent.Source_Id_Int_1
       AND Aca.Pay_From_Customer = Cust.Cust_Account_Id(+)
       AND Cust.Party_Id = Hp.Party_Id(+)
       AND Ent.Entity_Code = 'RECEIPTS'
       AND Ent.Entity_Id = Aeh.Entity_Id
       AND Aeh.Ae_Header_Id = p_Ae_Header_Id
       AND Aeh.Ledger_Id = p_Ledger_Id; */

Line: 341

             SELECT Trx_Number
              INTO l_invoice_num_temp
              FROM Ra_Customer_Trx_All
             WHERE Customer_Trx_Id = l_Customer_Trx_Id;

Line: 360

    SELECT DISTINCT Applied_To_Source_Id_Num_1+0
      INTO l_Customer_Trx_Id
      FROM Xla_Distribution_Links
     WHERE Ae_Header_Id = p_Ae_Header_Id
       AND Ae_Line_Num = p_Ae_Line_Num;

Line: 368

      SELECT Trx_Number
        INTO x_Invoice_Num
        FROM Ra_Customer_Trx_All
       WHERE Customer_Trx_Id = l_Customer_Trx_Id;

Line: 546

      SELECT DISTINCT JCA.PERIOD_NAME
             ,GP.PERIOD_NUM
        FROM JA_CN_CFS_ACTIVITIES_ALL  JCA
            ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
            ,RG_REPORT_AXES            RRA
            ,RG_REPORTS                RG
            ,GL_LEDGERS                LED
            ,GL_PERIODS                GP
       WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
         AND JCA.LEDGER_ID = P_LEDGER_ID
         AND JCA.GL_DATE >= L_DATE_FROM
         AND JCA.GL_DATE <= L_DATE_TO
         AND JCA.SOURCE = NVL(P_SOURCE,
                              JCA.SOURCE)
         AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
         AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
         AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
         AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
         AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
         AND RG.REPORT_ID = P_REPORT_ID
         AND LED.LEDGER_ID = P_LEDGER_ID
         AND GP.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
         AND GP.PERIOD_NAME = JCA.PERIOD_NAME
         --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017  add --fix bug 7488223 delete
         -- fix bug 7488223 add begin
         AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
            or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
                                                             where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
                                                                  ))
         ORDER BY GP.PERIOD_NUM DESC;

Line: 584

      SELECT DISTINCT
             -- Fix bug#6751696 delete begin
             --RRA.AXIS_NAME
             -- Fix bug#6751696 delete end
             -- Fix bug#6751696 add begin
             RRA.AXIS_SEQ
             -- Fix bug#6751696 add end
        FROM JA_CN_CFS_ACTIVITIES_ALL  JCA
            ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
            ,RG_REPORT_AXES            RRA
            ,RG_REPORTS                RG
       WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
         AND JCA.PERIOD_NAME = P_PERIOD_NAME
         AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
         AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
         AND JCA.SOURCE = NVL(P_SOURCE,
                              JCA.SOURCE)
         AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
         AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
         -- Fix bug#6859513 add begin
         AND RRA.AXIS_SEQ = NVL(P_ROW_NAME, RRA.AXIS_SEQ)
         -- Fix bug#6859513 add end
         AND JCA.LEDGER_ID = P_LEDGER_ID
         AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
         AND RG.REPORT_ID = NVL(P_REPORT_ID,
                                RG.REPORT_ID)
         --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017  add --fix bug 7488223 delete
         -- fix bug 7488223 add begin
          AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
            or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
                                                             where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
                                                                  ));

Line: 631

      SELECT JCA.TRX_ID
            ,JCA.SOURCE
            ,JCA.TRANSACTION_TYPE
            ,JCA.TRX_LINE_ID
            ,JCA.FUNC_AMOUNT
            ,JCA.ORIGINAL_AMOUNT
            ,JCA.DETAILED_CFS_ITEM
            ,JCA.THIRD_PARTY_NAME
            ,JCA.THIRD_PARTY_NUMBER
            ,JCA.REFERENCE_NUMBER
            ,RRA.DESCRIPTION
            -- Fix bug#6697073 begin--------------------------------
            ,JCA.TRX_NUMBER
            -- Fix bug#6697073 end----------------------------------
            ,null--fix bug 7487373 add
            ,JCA.Balancing_Segment--enhancment add
        FROM JA_CN_CFS_ACTIVITIES_ALL  JCA
            ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
            ,RG_REPORTS                RG
            ,RG_REPORT_AXES            RRA
       WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
         AND JCA.PERIOD_NAME = P_PERIOD_NAME
         AND JCA.SOURCE = NVL(P_SOURCE,
                              JCA.SOURCE)
         AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
         AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
         AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
         AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
         AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
         AND RG.REPORT_ID = P_REPORT_ID
         AND JCA.LEDGER_ID = P_LEDGER_ID
         -- Fix bug#6751696 delete begin
         --AND RRA.AXIS_NAME = P_ROW_NAME
         -- Fix bug#6751696 delete end
         -- Fix bug#6751696 add begin
         AND RRA.AXIS_SEQ = P_ROW_NAME
         -- Fix bug#6751696 add end
         --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017  add --fix bug 7488223 delete
         -- fix bug 7488223 add begin
          AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
            or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
                                                             where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
                                                                  ))
         -- -- fix bug 7488223 add end
       ORDER BY JCA.SOURCE
               ,JCA.THIRD_PARTY_NUMBER
               ,JCA.REFERENCE_NUMBER;

Line: 726

     DELETE
    FROM   JA_CN_LEDGER_LE_BSV_GT;

Line: 741

    SELECT name
      INTO l_le_name
      FROM XLE_ENTITY_PROFILES
     WHERE legal_entity_id=p_legal_entity_id;

Line: 747

    SELECT NAME
      INTO L_REPORT_NAME
      FROM RG_REPORTS
     WHERE REPORT_ID = P_REPORT_ID;

Line: 753

     SELECT name
       INTO l_ledger_name
       FROM gl_ledgers
      WHERE ledger_id=p_ledger_id;

Line: 765

    SELECT XMLELEMENT("P_START_PERIOD",
                      P_GL_PERIOD_FROM)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 771

    SELECT XMLELEMENT("P_END_PERIOD",
                      P_GL_PERIOD_TO)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 775

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

Line: 780

    SELECT XMLELEMENT("P_REPORT_NAME",
                      L_REPORT_NAME)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 784

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

Line: 789

    SELECT XMLELEMENT("P_SOURCE",
                      P_SOURCE)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 793

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

Line: 798

    SELECT XMLELEMENT("P_LEDGER_NAME",
                      L_LEDGER_NAME)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 802

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

Line: 807

    SELECT XMLELEMENT("P_LEGAL_ENTITY",
                      L_LE_NAME)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 811

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

Line: 817

    SELECT XMLELEMENT("P_ROW_NAME",
                      P_ROW_NAME)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 821

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

Line: 827

      SELECT XMLELEMENT("P_BSV",--enhancment add
                      P_BSV)
      INTO L_XML_ITEM
      FROM DUAL;

Line: 831

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

Line: 837

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

Line: 844

    SELECT LED.PERIOD_SET_NAME
          ,GP1.PERIOD_YEAR
          ,GP2.PERIOD_YEAR
      INTO L_PERIOD_SET_NAME
          ,L_YEAR_FROM
          ,L_YEAR_TO
      FROM GL_LEDGERS LED
          ,GL_PERIODS GP1
          ,GL_PERIODS GP2
     WHERE LED.LEDGER_ID = P_LEDGER_ID
           AND GP1.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
           AND GP1.PERIOD_NAME = P_GL_PERIOD_FROM
           AND GP2.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
           AND GP2.PERIOD_NAME = P_GL_PERIOD_TO;

Line: 867

    SELECT ACCOUNTED_PERIOD_TYPE
      INTO L_PERIOD_TYPE
      FROM GL_LEDGERS
     WHERE LEDGER_ID = P_LEDGER_ID;

Line: 873

    SELECT PERIOD_YEAR * 1000 + PERIOD_NUM
          ,START_DATE
      INTO L_PERIOD_NUM_FROM
          ,L_DATE_FROM
      FROM GL_PERIODS
     WHERE PERIOD_SET_NAME = L_PERIOD_SET_NAME
           AND PERIOD_NAME = P_GL_PERIOD_FROM
           AND PERIOD_TYPE = L_PERIOD_TYPE;

Line: 883

    SELECT PERIOD_YEAR * 1000 + PERIOD_NUM
          ,END_DATE
      INTO L_PERIOD_NUM_TO
          ,L_DATE_TO
      FROM GL_PERIODS
     WHERE PERIOD_SET_NAME = L_PERIOD_SET_NAME
           AND PERIOD_NAME = P_GL_PERIOD_TO
           AND PERIOD_TYPE = L_PERIOD_TYPE;

Line: 967

          L_FUNC_AMOUNT_TEMP.DELETE;

Line: 968

          L_INVOICE_NUM1.DELETE;

Line: 1026

              SELECT XMLELEMENT("PERIOD_NAME",
                                L_PERIOD_NAME)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1032

              SELECT XMLELEMENT("DETAILED_CFS_ITEM",
                                L_DETAILED_CFS_ITEM)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1036

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

Line: 1040

              SELECT XMLELEMENT("ROW_NAME",
                                L_ROW_DESCRIPTION)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1044

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

Line: 1048

              SELECT XMLELEMENT("SOURCE",
                                L_SOURCE)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1052

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

Line: 1057

                SELECT XMLELEMENT("BSV",--Fix bug#7334017  add
                                L_BSV)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1061

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

Line: 1067

              SELECT XMLELEMENT("THIRD_PARTY_NAME",
                                L_THIRD_PARTY_NAME)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1071

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

Line: 1075

              SELECT XMLELEMENT("RECEIPT_PAYMENT_NUMBER",
                                L_TRX_NUMBER)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1079

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

Line: 1085

              SELECT XMLELEMENT("TRANSACTION_NUMBER",
                                    L_INVOICE_NUM1(l_count))--L_INVOICE_NUM
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1091

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

Line: 1097

              SELECT XMLELEMENT("TRANSACTION_AMOUNT",
                                    L_FUNC_AMOUNT_TEMP(l_count))--L_FUNC_AMOUNT
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1102

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

Line: 1107

              SELECT XMLELEMENT("ROW_DETAIL",
                                L_XML_ROW_ITEMS)
                INTO L_XML_ITEM
                FROM DUAL;

Line: 1111

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

Line: 1128

        SELECT XMLELEMENT("ROW_AMOUNT",
                          L_ROW_AMOUNT)
          INTO L_XML_ITEM
          FROM DUAL;

Line: 1132

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

Line: 1137

        SELECT XMLELEMENT("ROW",
                          L_XML_ROW)
          INTO L_XML_ITEM
          FROM DUAL;

Line: 1147

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

Line: 1157

        SELECT XMLCONCAT(L_XML_PERIOD,
                         L_XML_ROWS_ALL)
          INTO L_XML_PERIOD
          FROM DUAL;

Line: 1162

        SELECT XMLELEMENT("PERIOD",
                          L_XML_PERIOD)
          INTO L_XML_ITEM
          FROM DUAL;

Line: 1166

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

Line: 1177

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

Line: 1204

      SELECT XMLELEMENT("EXCEPTION",
                        L_MSG_INVALID_GLPERIOD)
        INTO L_XML_ITEM
        FROM DUAL;

Line: 1208

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

Line: 1213

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

Line: 1230

      SELECT XMLELEMENT("EXCEPTION",
                        'Other_Exception')
        INTO L_XML_ITEM
        FROM DUAL;

Line: 1234

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

Line: 1239

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