APPS.DPP_EXECUTIONPROCESS_PVT SQL Statements

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

Line: 87

		SELECT NVL(AUTOMATIC_FLAG,'N') AUTOMATIC_FLAG
		FROM OZF_PROCESS_SETUP_ALL OPSA, DPP_EXECUTION_PROCESSES DEP
		WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRD_PRF_ID,0)
		AND OPSA.PROCESS_CODE = P_PROCESS_CODE
		AND OPSA.PROCESS_CODE = DEP.PROCESS_CODE
		AND OPSA.ORG_ID = P_IN_ORG_ID
		AND DEP.TRANSACTION_HEADER_ID=P_TXN_HDR_ID;

Line: 97

        SELECT dpp.transaction_header_id
        FROM dpp_transaction_headers_all dpp
        WHERE dpp.transaction_header_id = p_txn_header_id
        AND NOT EXISTS (SELECT DISTINCT dcc.transaction_header_id
        FROM dpp_customer_claims_all dcc
        WHERE dcc.transaction_header_id = dpp.transaction_header_id);

Line: 105

    CURSOR get_lines_for_updatepo_csr(p_txn_hdr_id IN NUMBER) IS
        SELECT dpp.transaction_header_id
        FROM dpp_transaction_headers_all dpp
        WHERE dpp.transaction_header_id = p_txn_hdr_id
        AND EXISTS (SELECT update_purchasing_docs
        FROM dpp_transaction_lines_all dtl
        WHERE nvl(update_purchasing_docs,'N') = 'N'
        AND dtl.transaction_header_id = p_txn_hdr_id);

Line: 115

    CURSOR get_lines_for_updateinv_csr(p_txn_hdr_id IN NUMBER) IS
        SELECT dtl.transaction_line_id
        FROM dpp_transaction_lines_all dtl
        WHERE dtl.transaction_header_id = p_txn_hdr_id
        AND EXISTS (SELECT UPDATE_INVENTORY_COSTING
        FROM dpp_transaction_lines_all
        WHERE nvl(UPDATE_INVENTORY_COSTING,'N') = 'N'
        AND transaction_header_id = p_txn_hdr_id)
        AND rownum = 1;

Line: 127

        SELECT dtl.transaction_line_id
        FROM dpp_transaction_lines_all dtl
        WHERE dtl.transaction_header_id = p_txn_hdr_id
        AND EXISTS (SELECT UPDATE_ITEM_LIST_PRICE
        FROM dpp_transaction_lines_all
        WHERE nvl(UPDATE_ITEM_LIST_PRICE,'N') = 'N'
        AND transaction_header_id = p_txn_hdr_id)
        AND rownum = 1;

Line: 137

       SELECT DPP.TRANSACTION_HEADER_ID,
              DPP.TRANSACTION_NUMBER,
              DPP.VENDOR_ID,
              DPP.VENDOR_SITE_ID
       FROM DPP_TRANSACTION_HEADERS_ALL DPP
       WHERE DPP.TRANSACTION_STATUS = 'APPROVED'
       AND DPP.EFFECTIVE_START_DATE <= SYSDATE
       AND TO_NUMBER(DPP.ORG_ID) = P_IN_ORG_ID
       AND DPP.TRANSACTION_NUMBER = NVL(P_TXN_NUMBER,DPP.TRANSACTION_NUMBER);

Line: 179

          FOR get_lines_for_updatepo_rec IN get_lines_for_updatepo_csr(get_approved_txn_rec.transaction_header_id) LOOP
            EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
                L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'UPDTPO');

Line: 204

          FOR get_lines_for_updateinv_rec IN get_lines_for_updateinv_csr(get_approved_txn_rec.transaction_header_id) LOOP
            EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
                L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'INVC');

Line: 356

         SELECT dpp.transaction_header_id
          FROM dpp_transaction_headers_all dpp
         WHERE dpp.transaction_header_id = p_txn_hdr_id
     AND ROWNUM=1;

Line: 362

        SELECT NVL(AUTOMATIC_FLAG,'N') AUTOMATIC_FLAG
        FROM OZF_PROCESS_SETUP_ALL OPSA, DPP_EXECUTION_PROCESSES DEP
        WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRD_PRF_ID,0)
        AND OPSA.PROCESS_CODE = P_PROCESS_CODE
        AND OPSA.PROCESS_CODE = DEP.PROCESS_CODE
        AND OPSA.ORG_ID = P_IN_ORG_ID
        AND DEP.TRANSACTION_HEADER_ID=P_TXN_HDR_ID;

Line: 584

	L_TXN_LINE_ID.DELETE();

Line: 675

    SELECT SUPP_TRADE_PROFILE_ID
    INTO L_RET_ID
    FROM OZF_SUPP_TRD_PRFLS_ALL
    WHERE SUPPLIER_ID = P_VENDOR_ID
    AND SUPPLIER_SITE_ID = P_VENDOR_SITE_ID
    AND ORG_ID = P_ORG_ID;

Line: 723

    SELECT COUNT(1)
    FROM OZF_PROCESS_SETUP_ALL
    WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRADE_PROFILE_ID,0)
    AND ENABLED_FLAG = 'Y'
    AND ORG_ID = P_ORG_ID;

Line: 829

   SELECT dpp.transaction_header_id,
          dpp.transaction_number,
          dpp.vendor_id,
          dpp.vendor_site_id
    FROM dpp_transaction_headers_all dpp
   WHERE dpp.transaction_status = 'ACTIVE'
     AND trunc(dpp.effective_start_date) = trunc(sysdate)+p_days
     AND to_number(dpp.org_id) = p_in_org_id;

Line: 854

        SELECT AUTOMATE_NOTIFICATION_DAYS INTO L_DAYS
        FROM OZF_SYS_PARAMETERS_ALL
        WHERE ORG_ID = P_IN_ORG_ID;

Line: 1026

   InsertExecProcesses(
          p_txn_hdr_id  =>  p_txn_hdr_id,
          p_org_id      => p_org_id,
          p_supp_trd_prfl_id => l_supp_trade_profile_id,
          x_msg_count        => x_msg_count,
          x_msg_data         => x_msg_data,
          x_return_status    => x_return_status );

Line: 1087

PROCEDURE InsertExecProcesses(
    p_txn_hdr_id              IN NUMBER,
    p_org_id                  IN NUMBER,
    p_supp_trd_prfl_id        IN NUMBER,
    x_msg_count               OUT  NOCOPY  NUMBER,
    x_msg_data                OUT  NOCOPY  VARCHAR2,
    x_return_status           OUT  NOCOPY  VARCHAR2
)
IS
   l_api_name               CONSTANT VARCHAR2(30) := 'InsertExecProcesses';

Line: 1107

   SELECT COUNT(1)
   FROM DPP_EXECUTION_PROCESSES
   WHERE transaction_header_id = p_txn_hdr_id;

Line: 1114

    SELECT dppl.lookup_code
      FROM dpp_lookups dppl,
           OZF_PROCESS_SETUP_ALL opsa
     WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
       AND dppl.tag is not null
       AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
       AND opsa.enabled_flag = 'Y'
       AND opsa.org_id = p_org_id
       AND dppl.lookup_code = opsa.process_code;

Line: 1125

    SAVEPOINT InsertExecProcesses;

Line: 1143

       DELETE FROM DPP_EXECUTION_PROCESSES
         WHERE transaction_header_id = p_txn_hdr_id;

Line: 1157

       INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
                                              transaction_header_id,
                                              created_by,
                                              creation_date,
                                              last_updated_by,
                                              last_update_date,
                                              last_update_login
       )
       VALUES (get_process_codes_rec.lookup_code,
                  p_txn_hdr_id,
                  l_user_id,
                  sysdate,
                  l_user_id,
                  sysdate,
                  l_login_id
       );

Line: 1175

         FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while inserting into DPP_EXECUTION_PROCESSES: ' || SQLERRM);

Line: 1183

       ROLLBACK TO InsertExecProcesses;

Line: 1194

       ROLLBACK TO InsertExecProcesses;

Line: 1205

        ROLLBACK TO InsertExecProcesses;

Line: 1219

END InsertExecProcesses;

Line: 1275

SELECT transaction_header_id,
       transaction_number,
       ref_document_number,
       skip_adjustment_flag,
       skip_approval_flag
FROM dpp_transaction_headers_all dtha, ozf_supp_trd_prfls_all ostpa
WHERE dtha.vendor_id = ostpa.supplier_id
AND dtha.vendor_site_id = ostpa.supplier_site_id
AND dtha.org_id = ostpa.org_id
AND dtha.transaction_status = 'ACTIVE'
AND trunc(dtha.effective_start_date) <= trunc(sysdate)
AND dtha.org_id = p_org_id;

Line: 1331

          UPDATE dpp_transaction_headers_all
          SET transaction_status = 'PENDING_APPROVAL',
            object_version_number = object_version_number +1,
            last_updated_by = l_user_id,
            last_update_date = sysdate,
            last_update_login = l_login_id,
            request_id = l_request_id,
            program_application_id = l_program_application_id,
            program_id = l_program_id,
            program_update_date = sysdate
          WHERE transaction_header_id = l_transaction_header_id;

Line: 1385

          UPDATE dpp_transaction_headers_all
          SET transaction_status = 'APPROVED',
              object_version_number = object_version_number +1,
              last_updated_by = l_user_id,
              last_update_date = sysdate,
              last_update_login = l_login_id,
              request_id = l_request_id,
              program_application_id = l_program_application_id,
              program_id = l_program_id,
              program_update_date = sysdate
          WHERE transaction_header_id = l_transaction_header_id;

Line: 1433

          UPDATE dpp_transaction_headers_all
          SET transaction_status = 'PENDING_ADJUSTMENT',
              object_version_number = object_version_number +1,
              last_updated_by = l_user_id,
              last_update_date = sysdate,
              last_update_login = l_login_id,
              request_id = l_request_id,
              program_application_id = l_program_application_id,
              program_id = l_program_id,
              program_update_date = sysdate
          WHERE transaction_header_id = l_transaction_header_id;

Line: 1456

      Update_HeaderLog(
                p_api_version_number => 1.0
            ,   p_init_msg_list      => FND_API.G_FALSE
            ,   p_commit             => FND_API.G_FALSE
            ,   p_validation_level   => FND_API.G_VALID_LEVEL_FULL
            ,   x_return_status      => l_return_status
            ,   x_msg_count          => l_msg_count
            ,   x_msg_data           => l_msg_data
            ,   p_transaction_header_id => l_transaction_header_id
      ) ;

Line: 1468

        fnd_file.put_line(fnd_file.log, ' Update_HeaderLog. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);

Line: 1635

  SELECT transaction_number, org_id
  FROM dpp_transaction_headers_all
  WHERE transaction_header_id = p_txn_hdr_id;

Line: 1660

   update_status(
                p_api_version_number => l_api_version_number
            ,   p_init_msg_list      => l_init_msg_list
            ,   p_commit             => l_commit
            ,   p_validation_level   => l_validation_level
            ,   x_return_status      => l_return_status
            ,   x_msg_count          => l_msg_count
            ,   x_msg_data           => l_msg_data
            ,   p_txn_hdr_id         => l_txn_hdr_id
            ,   p_to_status          => 'APPROVED'
      ) ;

Line: 1673

        fnd_file.put_line(fnd_file.log, ' Update_Status. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);

Line: 1760

PROCEDURE update_status(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2    := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2    := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER      := FND_API.g_valid_level_full,
    x_return_status              OUT  NOCOPY  VARCHAR2,
    x_msg_count                  OUT  NOCOPY  NUMBER,
    x_msg_data                   OUT  NOCOPY  VARCHAR2,
    p_txn_hdr_id                 IN   NUMBER,
    p_to_status                  IN   VARCHAR2
)
IS
--Declare the variables
l_api_name                CONSTANT VARCHAR2(30) := 'update_status';

Line: 1786

   SAVEPOINT UPDATE_STATUS;

Line: 1805

   UPDATE dpp_transaction_headers_all
   SET transaction_status = p_to_status,
       object_version_number = object_version_number +1,
       last_updated_by = l_user_id,
       last_update_date = sysdate,
       last_update_login = l_login_id
   WHERE transaction_header_id = p_txn_hdr_id;

Line: 1813

   Update_HeaderLog(
                p_api_version_number => l_api_version_number
            ,   p_init_msg_list      => p_init_msg_list
            ,   p_commit             => p_commit
            ,   p_validation_level   => p_validation_level
            ,   x_return_status      => l_return_status
            ,   x_msg_count          => l_msg_count
            ,   x_msg_data           => l_msg_data
            ,   p_transaction_header_id => p_txn_hdr_id
      ) ;

Line: 1825

        fnd_file.put_line(fnd_file.log, ' Update_HeaderLog. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);

Line: 1836

       ROLLBACK TO UPDATE_STATUS;

Line: 1847

       ROLLBACK TO UPDATE_STATUS;

Line: 1858

        ROLLBACK TO UPDATE_STATUS;

Line: 1872

END update_status;

Line: 1889

PROCEDURE Update_HeaderLog(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2    := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2    := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER      := FND_API.g_valid_level_full,
    x_return_status              OUT  NOCOPY  VARCHAR2,
    x_msg_count                  OUT  NOCOPY  NUMBER,
    x_msg_data                   OUT  NOCOPY  VARCHAR2,
    p_transaction_header_id      IN   NUMBER
)
IS
--Declare the variables
l_api_name                CONSTANT VARCHAR2(30) := 'Update_HeaderLog';

Line: 1919

SELECT *
FROM dpp_transaction_headers_all dtha
WHERE dtha.transaction_header_id = p_transaction_header_id;

Line: 1925

   SAVEPOINT UPDATE_HEADERLOG;

Line: 1945

      FND_FILE.PUT_LINE(FND_FILE.LOG,'      Begin Update HeaderLog');

Line: 1948

   SELECT fnd_profile.VALUE('DPP_AUDIT_ENABLED')
   INTO l_log_enabled
   FROM dual;

Line: 1976

            l_txn_hdr_hist_rec.last_update_date          := fetch_header_rec.last_update_date;

Line: 1977

            l_txn_hdr_hist_rec.last_updated_by           := fetch_header_rec.last_updated_by;

Line: 1978

            l_txn_hdr_hist_rec.last_update_login         := fetch_header_rec.last_update_login;

Line: 2013

            dpp_log_pvt.insert_headerlog(
                p_api_version       => l_api_version_number
               ,p_init_msg_list     => p_init_msg_list
               ,p_commit            => p_commit
               ,p_validation_level  => p_validation_level
               ,x_return_status     => l_return_status
               ,x_msg_count         => x_msg_count
               ,x_msg_data          => x_msg_data
               ,p_txn_hdr_rec       => l_txn_hdr_hist_rec
            );

Line: 2025

               FND_FILE.PUT_LINE(FND_FILE.LOG,'      End Update HeaderLog');

Line: 2050

END Update_HeaderLog;