APPS.ASL_EXCEL_UTIL_PVT SQL Statements

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

Line: 45

SELECT DISTINCT ACC1.customer_id
FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
WHERE  ACC1.salesforce_id =  p_salesforce_id
AND    ACC1.creation_date > p_last_sync_time
AND    ACC1.salesforce_id = ACC2.salesforce_id(+)
AND    ACC1.customer_id   = ACC2.customer_id (+)
AND    ACC2.creation_date(+) <= p_last_sync_time
AND    ACC2.salesforce_id IS NULL;

Line: 78

SELECT DISTINCT ACC1.customer_id, ACC1.lead_id
FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
WHERE  ACC1.salesforce_id =  p_salesforce_id
AND    ACC1.creation_date > p_last_sync_time
AND    ACC1.salesforce_id = ACC2.salesforce_id(+)
AND    ACC1.customer_id   = ACC2.customer_id (+)
AND    ACC1.lead_id       = ACC2.lead_id     (+)
AND    ACC2.creation_date(+) <= p_last_sync_time
AND    ACC2.salesforce_id IS NULL
AND    ACC1.LEAD_ID IS NOT NULL;

Line: 112

SELECT DISTINCT ACC1.customer_id, ACC1.sales_lead_id
FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
WHERE  ACC1.salesforce_id =  p_salesforce_id
AND    ACC1.creation_date > p_last_sync_time
AND    ACC1.salesforce_id = ACC2.salesforce_id(+)
AND    ACC1.customer_id   = ACC2.customer_id (+)
AND    ACC1.sales_lead_id       = ACC2.sales_lead_id     (+)
AND    ACC2.creation_date(+) <= p_last_sync_time
AND    ACC2.salesforce_id IS NULL
AND    ACC1.sales_lead_id IS NOT NULL;

Line: 147

  SELECT QUOTE_HEADER_ID
  FROM ASO_QUOTE_HEADERS_ALL
  WHERE QUOTE_NUMBER IN (
  SELECT DISTINCT ACC1.quote_number
  FROM ASO_QUOTE_ACCESSES ACC1, ASO_QUOTE_ACCESSES ACC2
  WHERE  ACC1.resource_id =  p_salesforce_id
  AND    ACC1.creation_date > p_last_sync_time
  AND    ACC1.resource_id = ACC2.resource_id(+)
  AND    ACC1.QUOTE_NUMBER   = ACC2.QUOTE_NUMBER (+)
  AND    ACC2.creation_date(+) <= p_last_sync_time
  AND    ACC1.resource_id IS NOT NULL
  AND    ACC2.resource_id IS NULL
  )
  AND MAX_VERSION_FLAG = 'Y';

Line: 191

  SELECT DISTINCT CATEGORY_ID
  FROM MTL_ITEM_CATEGORIES
  WHERE ORGANIZATION_ID = p_org_id
  AND CATEGORY_ID in
  (SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_INV_CATEGORY',p_user_id,p_resp_id,p_app_id)
   FROM DUAL
  );

Line: 203

  SELECT DISTINCT CATEGORY_ID
  FROM MTL_ITEM_CATEGORIES
  WHERE ORGANIZATION_ID = p_org_id
  AND INSTR(l_inv_categories, fnd_global.local_chr(39)||to_char(CATEGORY_ID)|| fnd_global.local_chr(39))>0;

Line: 210

  SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_INV_CATEGORY',p_user_id, p_resp_id, p_app_id)
  INTO l_inv_categories
  FROM DUAL;

Line: 246

      SELECT 1
         FROM QP_LIST_HEADERS_B
           WHERE LIST_HEADER_ID = p_header_id;

Line: 251

SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_PRICE_LIST',p_user_id,p_resp_id,p_app_id) into l_prof_price_list_headers FROM DUAL;

Line: 360

           M_NEW_CUST_TBL.DELETE;

Line: 362

           M_NEW_CNT_TBL.DELETE;

Line: 363

           M_NEW_OPP_TBL.DELETE;

Line: 364

           M_NEW_OPP_LINE_TBL.DELETE;

Line: 366

           M_NEW_OPP_CLASS_TBL.DELETE;

Line: 368

           M_NEW_LEAD_TBL.DELETE;

Line: 369

           M_OLD_INV_TBL.DELETE;

Line: 370

           M_OLD_PRICE_LIST_TBL.DELETE;

Line: 371

           M_NEW_CUST_ACCOUNT_TBL.DELETE;

Line: 416

  SELECT SYSDATE INTO t_current_sync_time FROM DUAL;

Line: 439

,p_mgr_update IN VARCHAR2
,p_admin_update IN VARCHAR2
) IS

BEGIN

    t_access_profile_rec.cust_access_profile_value := p_cust_access;

Line: 448

    t_access_profile_rec.mgr_update_profile_value := p_mgr_update;

Line: 449

    t_access_profile_rec.admin_update_profile_value := p_admin_update;

Line: 455

** and Check_Customer_Updateble together. Right now the problem is the DownloadProcessor.java
** is too generic to split one column 'IY' to 'I' and 'Y'.
** TBD:
** Would it be better to delete all the entries where download_status = 'N'?
*/
/* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
/* and ASL_NEW_ORG_REC_TYPE to ASL_NEW_CUST_REC_TYPE */

FUNCTION Check_Organization_Download
(p_customer_id IN NUMBER
,p_org_creation_date IN DATE
,p_org_update_date   IN DATE
,p_profile_creation_date IN DATE
,p_ploc_update_date  IN DATE
,p_sloc_update_date  IN DATE
,p_bloc_update_date  IN DATE
,p_phone_update_date IN DATE
,p_email_update_date IN DATE
) RETURN VARCHAR2 IS
 l_new_org_rec ASL_NEW_CUST_REC_TYPE;

Line: 495

    ELSIF  ((p_org_update_date > t_last_sync_time) OR
             (p_profile_creation_date > t_last_sync_time) OR
             (p_ploc_update_date > t_last_sync_time) OR
             (p_sloc_update_date > t_last_sync_time) OR
             (p_bloc_update_date > t_last_sync_time) OR
             (p_phone_update_date > t_last_sync_time) OR
             (p_email_update_date > t_last_sync_time))
    THEN
       l_download_status := 'U';

Line: 516

,p_per_update_date   IN DATE
,p_profile_creation_date IN DATE
,p_phone_update_date IN DATE
,p_email_update_date IN DATE
) RETURN VARCHAR2 IS
 l_new_per_rec ASL_NEW_CUST_REC_TYPE;

Line: 541

    ELSIF  ((p_per_update_date > t_last_sync_time) OR
             (p_profile_creation_date > t_last_sync_time) OR
             (p_phone_update_date > t_last_sync_time) OR
             (p_email_update_date > t_last_sync_time))
    THEN
       l_download_status := 'U';

Line: 558

,p_opp_update_date IN DATE
,p_opportunity_id  IN NUMBER
,p_customer_id     IN NUMBER
,p_customer_update_date IN DATE
,p_contact_party_update_date  IN DATE
,p_contact_person_update_date  IN DATE
,p_rel_update_date IN DATE
) RETURN VARCHAR2  IS

  l_new_opp_rec ASL_NEW_OPPORTUNITY_REC_TYPE;

Line: 587

     ELSIF ((p_opp_update_date > t_last_sync_time) OR
            (p_customer_update_date > t_last_sync_time) OR
            (p_contact_party_update_date > t_last_sync_time) OR
            (p_contact_person_update_date > t_last_sync_time) OR
            (p_rel_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 606

,p_line_update_date IN DATE
,p_opportunity_id IN NUMBER
,p_opp_line_id  IN NUMBER
) RETURN VARCHAR2 IS
  l_new_opp_line_rec ASL_NEW_OPP_LINE_REC_TYPE;

Line: 626

     ELSIF ((p_line_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 640

,p_class_update_date IN DATE
,p_opportunity_id IN NUMBER
,p_opp_class_id  IN NUMBER
) RETURN VARCHAR2 IS
  l_new_opp_class_rec ASL_NEW_OPP_CLASS_REC_TYPE;

Line: 661

     ELSIF ((p_class_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 675

 p_issue_update_date    IN DATE,
 p_opportunity_id       IN NUMBER,
 p_opp_issue_id         IN NUMBER
) RETURN VARCHAR2 IS
  l_new_opp_issues_rec ASL_NEW_OPP_ISSUES_REC_TYPE;

Line: 695

     ELSIF ((p_issue_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 708

,p_credit_last_update_date     IN DATE
,p_lead_line_id             IN NUMBER
,p_group_last_update_date     IN DATE
,p_resource_last_update_date  IN DATE
) RETURN VARCHAR2 IS
    l_download_status VARCHAR2(1) := 'I';

Line: 728

  ELSIF ((p_credit_last_update_date > t_last_sync_time) OR
         (p_group_last_update_date > t_last_sync_time) OR
         (p_resource_last_update_date > t_last_sync_time)
        )
  THEN
    l_download_status := 'U';

Line: 749

,p_person_update_date    IN DATE
,p_contact_update_date   IN DATE
,p_loc_update_date       IN DATE
,p_phone_update_date     IN DATE
,p_email_update_date     IN DATE
) RETURN VARCHAR2 IS

  l_download_status VARCHAR2(1) := 'I';

Line: 776

  ELSIF ((p_person_update_date > t_last_sync_time) OR
         (p_contact_update_date > t_last_sync_time) OR
         (p_loc_update_date > t_last_sync_time) OR
         (p_phone_update_date > t_last_sync_time) OR
         (p_email_update_date > t_last_sync_time)
        )
  THEN
     l_download_status := 'U';

Line: 796

,p_tl_update_date  IN DATE
) RETURN VARCHAR2 IS

 l_download_status VARCHAR2(1) := 'I';

Line: 831

  ELSIF ((p_tl_update_date > t_last_sync_time)
        )
  THEN
     l_download_status := 'U';

Line: 844

,p_lead_last_update_date   IN DATE
,p_sales_lead_id           IN NUMBER
,p_customer_id             IN NUMBER
,p_customer_update_date    IN DATE
,p_cnt_party_update_date    IN DATE
,p_rel_last_update_date  IN DATE
,p_cnt_person_update_date   IN  DATE
) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 877

    ELSIF ((p_lead_last_update_date > t_last_sync_time) OR
            (p_customer_update_date > t_last_sync_time) OR
            (p_cnt_party_update_date > t_last_sync_time) OR
            (p_cnt_person_update_date > t_last_sync_time) OR
            (p_rel_last_update_date > t_last_sync_time)
           )
    THEN
        l_download_status := 'U';

Line: 893

,p_line_last_update_date   IN DATE
,p_sales_lead_id  IN NUMBER
) RETURN VARCHAR2 IS
    l_download_status VARCHAR2(1) := 'I';

Line: 907

    ELSIF (p_line_last_update_date > t_last_sync_time)
    THEN
        l_download_status := 'U';

Line: 920

,p_team_last_update_date  IN DATE
,p_customer_id     IN NUMBER
,p_group_last_update_date IN DATE
,p_resource_last_update_date IN DATE
) RETURN VARCHAR2 IS
   l_download_status VARCHAR2(1) := 'I';

Line: 937

    ELSIF ((p_team_last_update_date > t_last_sync_time) OR
           (p_group_last_update_date > t_last_sync_time) OR
           (p_resource_last_update_date > t_last_sync_time)
          )
    THEN
        l_download_status := 'U';

Line: 953

,p_team_last_update_date  IN DATE
,p_opportunity_id     IN NUMBER
,p_group_last_update_date IN DATE
,p_resource_last_update_date IN DATE
) RETURN VARCHAR2 IS
    l_download_status VARCHAR2(1) := 'I';

Line: 970

    ELSIF ((p_team_last_update_date > t_last_sync_time) OR
           (p_group_last_update_date > t_last_sync_time) OR
           (p_resource_last_update_date > t_last_sync_time)
          )
    THEN
        l_download_status := 'U';

Line: 987

,p_team_last_update_date  IN DATE
,p_lead_id     IN NUMBER
,p_group_last_update_date IN DATE
,p_resource_last_update_date IN DATE
) RETURN VARCHAR2 IS
    l_download_status VARCHAR2(1) := 'I';

Line: 1003

    ELSIF ((p_team_last_update_date > t_last_sync_time) OR
           (p_group_last_update_date > t_last_sync_time) OR
           (p_resource_last_update_date > t_last_sync_time)
          )
    THEN
        l_download_status := 'U';

Line: 1021

,p_add_update_date   IN DATE
) RETURN VARCHAR2 IS
 l_download_status VARCHAR2(1) := 'I';

Line: 1033

    ELSIF (p_add_update_date > t_last_sync_time)
    THEN
        l_download_status := 'U';

Line: 1045

** Passing in a customer, check if it is updateable by this particular
** resource
** For contact access priv, the sql will pass in the object_id to check.
** because contact's updateable belongs to its object that it relates to.
*/
FUNCTION CHECK_CUSTOMER_UPDATEBLE
(p_api_version_number IN NUMBER
,p_init_msg_list      IN VARCHAR2
,p_validation_level IN NUMBER
,p_customer_id IN NUMBER
,p_party_type IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
  l_access_privilege VARCHAR2(1);

Line: 1065

  SELECT party_type
  FROM HZ_PARTIES
  WHERE party_id = p_party_id;

Line: 1137

END CHECK_CUSTOMER_UPDATEBLE;

Line: 1139

FUNCTION CHECK_OPPORTUNITY_UPDATEBLE
(p_api_version_number IN NUMBER
,p_init_msg_list      IN VARCHAR2
,p_validation_level IN NUMBER
,p_opportunity_id IN NUMBER
) RETURN VARCHAR2 IS
  l_access_privilege VARCHAR2(1);

Line: 1179

END CHECK_OPPORTUNITY_UPDATEBLE;

Line: 1181

FUNCTION CHECK_LEAD_UPDATEBLE
(p_api_version_number IN NUMBER
,p_init_msg_list      IN VARCHAR2
,p_validation_level IN NUMBER
,p_sales_lead_id IN NUMBER
) RETURN VARCHAR2 IS
  l_access_privilege VARCHAR2(1);

Line: 1221

END CHECK_LEAD_UPDATEBLE;

Line: 1224

FUNCTION CHECK_CUSTOMER_UPDATEBLE(p_customer_id IN NUMBER) RETURN VARCHAR2 IS
  v_dummy VARCHAR2(1);

Line: 1226

CURSOR C_CUSTOMER_UPDATEABLE (p_customer_id NUMBER,
                              p_resource_id NUMBER) IS
    SELECT 'X' FROM AS_ACCESSES_ALL
    WHERE salesforce_id = p_resource_id
    AND   customer_id   = p_customer_id
    AND   lead_id       is NULL
    AND   sales_lead_id is NULL;

Line: 1234

   OPEN C_CUSTOMER_UPDATEABLE(p_customer_id, t_salesforce_id);

Line: 1235

   FETCH C_CUSTOMER_UPDATEABLE INTO v_dummy;

Line: 1236

   IF C_CUSTOMER_UPDATEABLE%NOTFOUND
   THEN
      CLOSE C_CUSTOMER_UPDATEABLE;

Line: 1241

   CLOSE C_CUSTOMER_UPDATEABLE;

Line: 1243

END CHECK_CUSTOMER_UPDATEBLE;

Line: 1252

   SELECT 'X' FROM AS_FC_SALESFORCE_V
   WHERE salesforce_id = p_salesforce_id
   AND sales_group_id = p_group_id
   AND member_delete_flag <> 'Y'
   AND rrel_delete_flag <> 'Y'
   AND manager_flag = 'Y';

Line: 1274

        SELECT name FROM AMS_P_SOURCE_CODES_V
        WHERE SOURCE_CODE_ID = p_source_code_id;

Line: 1292

,p_qot_update_date         IN DATE
,p_qot_header_id           IN NUMBER
,p_cust_accnt_update_date  IN DATE
,p_customer_update_date    IN DATE
,p_org_contact_update_date IN DATE
,p_rel_update_date         IN DATE
,p_contact_party_update_date IN DATE
,p_sold_to_party_update_date IN DATE
,p_related_obj_update_date IN DATE
,p_related_opp_update_date IN DATE

) RETURN VARCHAR2  IS

  l_new_qot_rec ASL_NEW_QUOTE_REC_TYPE;

Line: 1324

     ELSIF ((p_qot_update_date > t_last_sync_time) OR
            (p_cust_accnt_update_date > t_last_sync_time) OR
            (p_customer_update_date > t_last_sync_time) OR
            (p_org_contact_update_date > t_last_sync_time) OR
            (p_rel_update_date > t_last_sync_time) OR
            (p_contact_party_update_date > t_last_sync_time) OR
            (p_sold_to_party_update_date > t_last_sync_time) OR
            (p_related_obj_update_date > t_last_sync_time) OR
            (p_related_opp_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 1351

,p_quote_line_update_date   IN DATE
,p_quote_line_det_update_date IN DATE
,p_quote_header_id IN NUMBER
,p_quote_line_id   IN NUMBER
,p_quote_line_detail_id       IN NUMBER
) RETURN VARCHAR2 IS
  l_new_qot_det_rec ASL_NEW_QUOTE_DET_REC_TYPE;

Line: 1375

     ELSIF ((p_quote_line_update_date > t_last_sync_time) OR
            (p_quote_line_det_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 1392

,p_quote_shipment_update_date   IN DATE
,p_quote_header_id   IN NUMBER
,p_quote_shipment_id IN NUMBER
,p_ship_to_site_update_date     IN DATE
,p_ship_to_relation_update_date IN DATE
,p_ship_to_contact_update_date  IN DATE

) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1414

     ELSIF ((p_quote_shipment_update_date > t_last_sync_time) OR
            (p_ship_to_site_update_date > t_last_sync_time) OR
            (p_ship_to_relation_update_date > t_last_sync_time) OR
            (p_ship_to_contact_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 1433

,p_quote_payment_update_date   IN DATE
,p_quote_header_id   IN NUMBER
,p_quote_payment_id IN NUMBER

) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1452

     ELSIF (p_quote_payment_update_date > t_last_sync_time)
     THEN
        l_download_status := 'U';

Line: 1468

,p_price_Adj_update_date   IN DATE
,p_quote_header_id   IN NUMBER
,p_quote_line_id   IN NUMBER
,p_price_adjustment_id IN NUMBER

) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1488

     ELSIF (p_price_Adj_update_date > t_last_sync_time)
     THEN
        l_download_status := 'U';

Line: 1503

,p_qot_Salesteam_update_date   IN DATE
,p_quote_header_id   IN NUMBER
,p_quote_access_id   IN NUMBER
) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1521

     ELSIF (p_qot_Salesteam_update_date > t_last_sync_time)
     THEN
        l_download_status := 'U';

Line: 1536

,p_qot_scredit_update_date   IN DATE
,p_quote_header_id   IN NUMBER
,p_quote_sales_credit_id   IN NUMBER
) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1554

     ELSIF (p_qot_scredit_update_date > t_last_sync_time)
     THEN
        l_download_status := 'U';

Line: 1571

,p_inv_item_b_update_date  IN DATE
,p_inv_item_tl_update_date IN DATE
,p_inv_catgry_update_date  IN DATE
,p_inv_uom_update_date     IN DATE
,p_inv_category_id   IN NUMBER
,p_inv_item_id       IN NUMBER
) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1591

     ELSIF ( (p_inv_item_b_update_date > t_last_sync_time) OR
             (p_inv_item_tl_update_date > t_last_sync_time) OR
             (p_inv_catgry_update_date > t_last_sync_time) OR
             (p_inv_uom_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 1612

,p_list_line_update_date   IN DATE
,p_line_attr_update_date   IN DATE
,p_list_header_id   IN NUMBER
,p_inv_category_id  IN NUMBER
) RETURN VARCHAR2 IS
  l_download_status VARCHAR2(1) := 'I';

Line: 1627

     ELSIF ((p_list_line_update_date > t_last_sync_time) OR
            (p_line_attr_update_date > t_last_sync_time)
           )
     THEN
        l_download_status := 'U';

Line: 1649

,p_cust_update_date   IN DATE
,p_cust_accnt_update_date   IN DATE
,p_loc_update_date      IN DATE
,p_site_update_date     IN DATE
,p_site_use_update_date IN DATE
) RETURN VARCHAR2 IS
 l_new_account_rec ASL_CUSTOMER_ACCOUNT_REC_TYPE;

Line: 1669

    ELSIF  ((p_cust_update_date > t_last_sync_time) OR
            (p_cust_accnt_update_date > t_last_sync_time) OR
            (p_loc_update_date > t_last_sync_time) OR
            (p_site_update_date > t_last_sync_time) OR
            (p_site_use_update_date > t_last_sync_time)
           )
    THEN
       l_download_status := 'U';

Line: 1690

,p_cust_update_date   IN DATE
,p_loc_update_date      IN DATE
,p_site_update_date     IN DATE
) RETURN VARCHAR2 IS
 l_download_status VARCHAR2(1) := 'I';

Line: 1705

    ELSIF  ((p_cust_update_date > t_last_sync_time) OR
            (p_loc_update_date > t_last_sync_time) OR
            (p_site_update_date > t_last_sync_time)
           )
    THEN
       l_download_status := 'U';

Line: 1724

,p_cust_update_date   IN DATE
,p_contact_update_date      IN DATE
) RETURN VARCHAR2 IS
 l_download_status VARCHAR2(1) := 'I';

Line: 1738

    ELSIF  ((p_cust_update_date > t_last_sync_time) OR
            (p_contact_update_date > t_last_sync_time)
           )
    THEN
       l_download_status := 'U';

Line: 1803

,p_lead_last_update_date IN DATE
,p_customer_update_date    IN DATE
) RETURN VARCHAR2  IS
BEGIN
  IF (M_FULL_SYNC = FALSE) THEN
    IF   (p_customer_update_date > t_last_sync_time)
     THEN
	IF ((p_lead_creation_date < t_last_sync_time) AND
	    (p_lead_last_update_date < t_last_sync_time)
           )
	THEN
            return 'NI';

Line: 1823

,p_opp_update_date IN DATE
,p_customer_update_date IN DATE
) RETURN VARCHAR2  IS
BEGIN
  IF (M_FULL_SYNC = FALSE) THEN
    IF   (p_customer_update_date > t_last_sync_time)
     THEN
	IF  ((p_opp_creation_date < t_last_sync_time) AND
             (p_opp_update_date < t_last_sync_time)
            )
	THEN
            return 'NI';