APPS.IBE_LEAD_IMPORT_PVT SQL Statements

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

Line: 50

         select ibe_lead_import_log_s1.nextval
         into l_log_id
         From dual;

Line: 59

     printDebug('Insert into ibe_lead_import_log', 'write_log');

Line: 66

     Insert into IBE_LEAD_IMPORT_LOG
     (
	    Log_Id,
	    Begin_Date,
	    End_Date,
            Lead_Type,
	    status,
            import_mode,
            elapsed_time,
            num_imported,
            Num_failed,
            Num_success,
            creation_date,
            created_by,
            last_update_date,
            last_updated_by,
            last_updatE_login,
            security_group_id,
            object_version_number
     ) Values
     (
	    l_log_id,
	    p_begin_date,
	    p_end_date,
	    p_lead_type,
	    p_status,
            p_import_mode,
            0,
            0,
            0,
            0,
            sysdate,
            FND_GLOBAL.USER_ID,
            sysdate,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.USER_ID,
            0,
	    1
     );

Line: 109

         printDebug('error inserting to ibe_lead_import_details', 'write_log');

Line: 112

  procedure update_log(
      p_status		IN NUMBER,
      p_log_id		IN NUMBER,
      p_num_success	IN NUMBER,
      p_num_Failed	IN NUMBER,
      p_num_total	IN NUMBER,
      p_elapsed_time    IN NUMBER
  ) IS
     l_log_id NUMBER := p_log_id;

Line: 122

      update ibe_lead_import_log
      Set status = p_status,
	  num_success = p_num_success,
	  num_Failed = p_num_failed,
	  num_imported = p_num_total,
	  elapsed_time = p_elapsed_time
      Where log_id = p_log_id;

Line: 131

     	  printDebug('Cannot update record with log_id ' || l_log_id, 'Update_log');

Line: 132

          printDebug('No Data Found ', 'Update_log');

Line: 135

          printDebug('Cannot update record with log_id ' || l_log_id, 'Update_Log');

Line: 136

          printDebug(sqlerrm, 'Update_Log');

Line: 137

  END Update_Log;

Line: 139

  procedure insert_log_details
  (
      p_message		IN VARCHAR2,
      p_header_rec	IN G_LEADS_REC,
      p_status_flag	IN VARCHAR2,
      p_purge_flag	IN VARCHAR2,
      p_log_id		IN NUMBER
  ) IS
     l_write_detail_log VARCHAR2(1) := G_WRITE_DETAIL_LOG;

Line: 161

              select ibe_lead_import_details_s1.nextval
              into l_detail_id
              From dual;

Line: 174

            select detail_id
	    into l_old_detail_id
            From ibe_lead_import_details
            where quote_header_id = p_header_rec.quote_header_id;

Line: 179

	    delete from ibe_lead_import_details
	    where quote_header_id = p_header_rec.quote_header_id;

Line: 190

         insert into IBE_LEAD_IMPORT_DETAILS
         (
             Detail_Id,
             Log_Id,
             Quote_Header_id,
             Order_Id,
             Customer_First_name,
             Customer_last_name,
             Phone_Number,
             Fax_Number,
             Email_Address,
             Notes,
             Customer_Name,
             Address1,
             Address2,
             Address3,
             City,
             State,
             Postal_Code,
             Country,
             status_Flag,
             Message,
             Creation_Date,
             Created_By,
             Last_update_date,
             Last_Updated_By,
             Last_Update_login,
             Security_Group_Id,
             Object_Version_number
         ) Values
         (
             l_detail_id,
             l_log_id,
             p_header_rec.Quote_Header_id,
             p_header_rec.Order_Id,
             ' ' ,
             ' ' ,
             ' ',
             ' ',
             ' ',
             p_header_rec.Notes,
             p_header_rec.party_name,
             null,
             null,
             null,
              null,
             null,
             null,
             null,
             p_status_flag,
             p_Message,
             sysdate,
             FND_GLOBAL.User_ID,
             sysdate,
             FND_GLOBAL.User_ID,
             FND_GLOBAL.User_ID,
             0,
             0
         );

Line: 252

         printDebug('error inserting to ibe_lead_import_details', 'insert_log_details');

Line: 253

         printDebug(sqlerrm, 'insert_log_details');

Line: 254

  End insert_log_details;

Line: 290

   delete from IBE_TEMP_TABLE where key =p_keyString;

Line: 299

      INSERT into IBE_TEMP_TABLE (KEY, CHAR_VAL) VALUES (p_keyString,l_OutString);

Line: 301

      INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(l_OutString));

Line: 315

      INSERT into IBE_TEMP_TABLE (KEY, CHAR_VAL) VALUES (p_keyString,l_OutString);

Line: 317

      INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(l_OutString));

Line: 322

     x_QueryString := 'SELECT CHAR_VAL FROM IBE_TEMP_TABLE WHERE KEY = :'||p_number||'';

Line: 324

     x_QueryString := 'SELECT NUM_VAL FROM IBE_TEMP_TABLE WHERE KEY = :'||p_number||'';

Line: 368

      l_MyStmt := ' select qh.quote_header_id, hzcp1.contact_point_id phone_id,'||
          ' hp.party_id, hp.party_name, hp.party_type, max(hps.party_site_id) party_site_id,'||
          ' hr.subject_id rel_party_id, ho.org_contact_id,'||
          ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'',''DECISION_MAKER'',''END_USER'') contact_role_code,'||
   	  ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_CART_NUMBER'')  || qh.quote_number    Notes,'||
          ' qh.currency_code, qh.quote_header_id, qh.quote_number Order_Num,'||
   	  ' qh.creation_date Order_Creation_Date,:p_promo_code promo_code,'||
          ' qh.total_quote_price total_amount,'||
          ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_CART_LEAD'')  lead_description, '||
          ' qh.quote_header_id SOURCE_PRIMARY_REFERENCE, qh.minisite_id SOURCE_SECONDARY_REFERENCE, '||
          ' qh.marketing_source_code_id SOURCE_PROMOTION_ID '||
          ' FROM aso_quote_headers_all qh,'||
          ' hz_cust_accounts hca, hz_parties hp,hz_parties hp2,'||
          ' hz_relationships hr,'||
          ' hz_contact_points hzcp1, '||
          ' hz_party_sites hps, hz_org_contacts ho , hz_party_site_uses hpsu'||
          ' where qh.cust_account_id = hca.cust_account_id'||
          ' and qh.order_id is null'||
          ' and hca.party_id = hp.party_id'||
          ' and qh.party_id =  hp2.party_id'||
          ' and hp.party_id = hps.party_id'||
          ' and hps.party_site_id = hpsu.party_site_id '||
          ' and hpsu.primary_per_type = ''Y'''||
          ' and hp.party_number not in ('||l_party_number_query||')'||
          ' and qh.party_id  = hr.party_id(+)'||
          ' and hr.relationship_id  = ho.party_relationship_id(+)'||
	  ' and nvl(hr.directional_flag,''F'') =''F'' '||
          ' and hp2.party_id = hzcp1.owner_table_id(+)'||
          ' and hzcp1.primary_flag (+)= ''Y'''||
          ' and hzcp1.owner_table_name (+)= '''||l_owner_table_name||''''||
          ' and hzcp1.contact_point_type (+)= '''||l_contact_point_type||''''||
          ' and hzcp1.phone_line_type (+)= '''||l_phone_line_type||''''||
          ' and hzcp1.contact_point_purpose (+)= '''||l_priority_use_code_day||''''||
          ' and qh.quote_source_code in (''IStore Account'',''IStore Oneclick'')'||
          ' and qh.resource_id is null'||
          ' and qh.QUOTE_EXPIRATION_DATE + 1  >= :p_begin_date and qh.QUOTE_EXPIRATION_DATE+ 1 <= :p_end_date'||
          ' and not exists ( SELECT hzp.party_id FROM '||
                          ' jtf_auth_principals_b p, '||
                          ' jtf_auth_principals_b p1, '||
                          ' JTF_AUTH_PRINCIPAL_MAPS c, '||
                          ' fnd_user u,  '||
                          ' hz_parties hzp '||
                          ' WHERE p1.principal_name in ('||l_role_exclusion_query||')' ||
                          ' AND p.principal_name=u.user_name '||
                          ' and u.customer_id = hzp.party_id '||
                          ' and hzp.party_id  = hp2.party_id'||
                          ' and p1.JTF_AUTH_PRINCIPAL_ID = c.JTF_AUTH_PARENT_PRINCIPAL_ID '||
                          ' and c.JTF_AUTH_PRINCIPAL_ID = p.JTF_AUTH_PRINCIPAL_ID)'||
          ' Group by '||
          ' qh.quote_header_id,'||
          ' hzcp1.contact_point_id,  hp.party_id,    hp.party_name, '||
          ' hp.party_type,  qh.invoice_to_party_site_id ,  '||
          ' hr.subject_id ,'||
          ' ho.org_contact_id,'||
          ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'', ''DECISION_MAKER'', ''END_USER''),'||
          ' qh.quote_number ,qh.total_quote_price ,'||
          ' qh.currency_code,'||
          ' qh.quote_header_id ,'||
          ' qh.quote_number ,'||
          ' qh.creation_date,'||
          ' qh.quote_header_id, qh.minisite_id,'||
          ' qh.marketing_source_code_id,'||
          ' qh.total_quote_price';

Line: 450

            select ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
	           ql.uom_code, sum(nvl(ql.quantity,0)) quantity,
                   msik.concatenated_segments part_no, msik.description product_description
                   ,ql.line_quote_price  line_price , ql.marketing_source_code_id promotion_id
	    From aso_quote_lines_all ql, mtl_system_items_kfv msik
            Where ql.quote_header_id = p_quote_header_id
            And ql.inventory_item_id = msik.inventory_item_id
            And ql.organization_id= msik.organization_id
	    Group by ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
		ql.uom_code, msik.concatenated_segments, msik.description,
                ql.line_quote_price,ql.marketing_source_code_id
	    Order by QL.Line_Quote_price desc;

Line: 497

      l_myStmt := ' select qh.quote_header_id, hzcp1.contact_point_id phone_id,'||
         ' hp.party_id, hp.party_name, hp.party_type,'||
         ' max(hps.party_site_id) party_site_id, hr.subject_id rel_party_id,'||
         ' ho.org_contact_id,'||
         ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'',''DECISION_MAKER'' ,''END_USER'') contact_role_code,'||
   	 ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_NUMBER'') || oh.order_number'||
         ' || fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_REFERENCE'') || oh.orig_sys_document_ref Notes,'||
         ' qh.currency_code, qh.order_id, qh.quote_number Order_Num, qh.creation_date Order_Creation_Date,:p_promo_code promo_code,'||
         ' oe_totals_grp.Get_Order_Total(oh.header_id,null,''ALL'') total_amount,'||
         ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_LEAD'') lead_description, '||
         ' qh.quote_header_id SOURCE_PRIMARY_REFERENCE, qh.minisite_id SOURCE_SECONDARY_REFERENCE, '||
	 ' qh.marketing_source_code_id SOURCE_PROMOTION_ID '||
         ' FROM aso_quote_headers_all qh, oe_order_headers_all oh,'||
         ' hz_cust_accounts hca, hz_parties hp, hz_parties hp2,'||
         ' hz_relationships hr, '||
         ' hz_contact_points hzcp1, '||
         ' hz_party_sites hps, hz_org_contacts ho,hz_party_site_uses hpsu'||
         ' where qh.cust_account_id = hca.cust_account_id'||
         ' and hca.party_id = hp.party_id'||
         ' and qh.party_id  = hp2.party_id'||
         ' and hp.party_number not in ('||l_party_number_query||')'||
         ' and hp.party_id = hps.party_id'||
         ' and hps.party_site_id = hpsu.party_site_id '||
         ' and hpsu.primary_per_type = ''Y'''||
         ' and hp2.party_id  = hr.party_id(+)'||
         ' and hr.relationship_id  = ho.party_relationship_id(+)'||
         ' and hp2.party_id = hzcp1.owner_table_id(+)'||
	 ' and nvl(hr.directional_flag,''F'') = ''F'' '||
         ' and hzcp1.primary_flag (+)= ''Y'''||
         ' and hzcp1.owner_table_name (+)= '''||l_owner_table_name||''''||
         ' and hzcp1.contact_point_type (+)= '''||l_contact_point_type||''''||
         ' and hzcp1.phone_line_type (+)= '''||l_phone_line_type||''''||
         ' and hzcp1.contact_point_purpose (+)= '''||l_priority_use_code_day ||''''||
         ' and qh.quote_source_code in (''IStore Account'',''IStore Oneclick'')'||
         ' and qh.resource_id is null '||
         ' and qh.quote_header_id = oh.source_document_id '||
	    ' and qh.order_id = oh.header_id '||
         ' and not exists ( SELECT hzp.party_id'||
                          ' FROM '||
                          ' jtf_auth_principals_b p, '||
                          ' jtf_auth_principals_b p1, '||
                          ' JTF_AUTH_PRINCIPAL_MAPS c, '||
                          ' fnd_user u,  '||
                          ' hz_parties hzp '||
                          ' WHERE p1.principal_name in ('||l_role_exclusion_query||') '||
                          ' AND p.principal_name=u.user_name'||
                          ' and u.customer_id = hzp.party_id'||
                          ' and hzp.party_id  = hp2.party_id'||
                          ' and p1.JTF_AUTH_PRINCIPAL_ID = c.JTF_AUTH_PARENT_PRINCIPAL_ID '||
                          ' and c.JTF_AUTH_PRINCIPAL_ID = p.JTF_AUTH_PRINCIPAL_ID'||
                         ' )'||
         ' and oh.creation_date >= :p_begin_date and oh.creation_date < :p_end_date'||
         ' Group by  qh.quote_header_id,'||
            ' hzcp1.contact_point_id,'||
            ' hp.party_id,'||
   	    ' hp.party_name,'||
   	    ' hp.party_type,'||
            ' qh.invoice_to_party_site_id, '||
            ' hr.subject_id ,'||
            ' ho.org_contact_id,'||
            ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'', ''DECISION_MAKER'', ''END_USER'') , '||
            ' oh.order_number, oh.payment_amount, oh.payment_type_code,oh.orig_sys_document_ref  ,'||
            ' qh.currency_code,'||
            ' qh.order_id,'||
            ' qh.quote_number ,'||
            ' qh.creation_date ,'||
            ' qh.quote_header_id, qh.minisite_id,'||
            ' qh.marketing_source_code_id,'||
            ' oh.header_id';

Line: 589

	  select ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
		 ql.uom_code, sum( nvl(ql.quantity, 0)) quantity,
	  	 msik.concatenated_segments part_no, msik.description product_description
                 ,ql.line_quote_price line_price ,ql.marketing_source_code_id promotion_id
          From ASO_QUOTE_LINES_ALL QL, MTL_SYSTEM_ITEMS_KFV MSIK
          Where ql.quote_header_id = p_order_header_id
          And ql.inventory_item_id = msik.inventory_item_id
	  And ql.organization_id = msik.organization_id
	  Group by ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
		ql.uom_code, msik.concatenated_segments, msik.description,
                ql.line_quote_price,ql.marketing_source_code_id
          order by ql.line_quote_price desc;

Line: 632

            select max(end_date )
            into l_begin_date
	    From ibe_lead_import_log
            where status = 1
	    And lead_type = p_lead_type;

Line: 641

                select sysdate-1
                into l_begin_date
		from dual;

Line: 654

	      select max(end_date)
	      into l_begin_date
	      From ibe_lead_import_log
	      where status = 1
	      And lead_type = p_lead_type;

Line: 662

	         select sysdate -1
	         into l_begin_date
		 From dual;

Line: 797

      printDebug('Update the current log records to be inactive', 'Create_Order_lead');

Line: 800

	    select max(log_id)
	    into G_LAST_LOG_ID
	    From ibe_lead_import_log
	    where lead_type = G_ORDER_LEAD;

Line: 806

	    delete From ibe_lead_import_details
	    where log_id < G_LAST_LOG_ID;

Line: 818

          update ibe_lead_import_log
          set status = 0
          where status = 1
          and lead_type = G_ORDER_LEAD;

Line: 828

      printDebug('Insert a new log record to IBE_LEAD_IMPORT_LOG', 'Create_Order_lead');

Line: 847

      select sysdate
      into l_start_time
      From dual;

Line: 893

          l_order_line_tbl.delete;

Line: 921

	  printDebug('Insert_Log Details ', 'Create_order_Leads');

Line: 923

          insert_log_details(
              p_message		=> l_error_msg,
	      p_header_Rec	=> l_order_rec,
	      p_status_flag	=> l_return_status,
              p_purge_flag	=> p_purge_flag,
	      p_log_id		=> l_log_id
	  );

Line: 934

      select sysdate
      into l_end_time
      From dual;

Line: 940

      printDebug('Update Log with num_success ' || l_num_success || ' num_Failed ' || l_num_failed ||
	' Total ' || l_total || ' Elapsed Time: ' || l_elapsed_time, 'Create_order_leads');

Line: 952

      update_log(
        p_status		=> l_status,
	p_log_id		=> l_log_id,
        p_num_success		=> l_num_success,
	p_num_failed		=> l_num_failed,
	p_num_total		=> l_total,
        p_elapsed_time 		=> l_elapsed_time
      );

Line: 1089

      printDebug('Update the current log records to be inactive', 'Create_Quote_lead');

Line: 1092

	    select max(log_id)
	    into G_LAST_LOG_ID
	    From ibe_lead_import_log
	    where lead_type = G_QUOTE_LEAD;

Line: 1097

            delete From ibe_lead_import_details
	    where log_id < G_LAST_LOG_ID;

Line: 1107

          update ibe_lead_import_log
          set status = 0
          where status = 1
          and lead_type = G_QUOTE_LEAD;

Line: 1132

      select sysdate
      into l_start_time
      From dual;

Line: 1170

           l_quote_line_tbl.delete;

Line: 1197

	  printDebug('Insert_Log Details ', 'Create_Quote_Leads');

Line: 1198

          insert_log_details(
              p_message		=> l_error_msg,
	      p_header_Rec	=> l_Quote_rec,
	      p_status_flag	=> l_return_status,
              p_purge_flag	=> p_purge_flag,
	      p_log_id		=> l_log_id
	  );

Line: 1209

      select sysdate
      into l_end_time
      From dual;

Line: 1216

      printDebug('Update Log with num_success ' || l_num_success || ' num_Failed ' || l_num_failed ||
	' Total ' || l_total || ' Elapsed Time: ' || l_elapsed_time, 'Create_Quote_leads');

Line: 1229

      update_log(
        p_status		=> l_status,
	p_log_id		=> l_log_id,
        p_num_success		=> l_num_success,
	p_num_failed		=> l_num_failed,
	p_num_total		=> l_total,
        p_elapsed_time 		=> l_elapsed_time
      );

Line: 1289

  select as_import_interface_s.nextval into p_import_interface_id from dual;

Line: 1291

INSERT INTO AS_IMPORT_INTERFACE
	(
	IMPORT_INTERFACE_ID,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_LOGIN,
	REQUEST_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_ID,
	PROGRAM_UPDATE_DATE,
	LOAD_TYPE,
	LOAD_DATE,
	LOAD_STATUS,
	CUSTOMER_NAME,
	PROMOTION_CODE,
	PARTY_ID,
	PARTY_TYPE,
	PARTY_SITE_ID,
	CONTACT_PARTY_ID,
	PHONE_ID,
	LEAD_NOTE,
	VEHICLE_RESPONSE_CODE,
	SOURCE_SYSTEM,
	CURRENCY_CODE,
	BUDGET_AMOUNT,
	ORIG_SYSTEM_REFERENCE,
	PRM_ASSIGNMENT_TYPE ,
	DESCRIPTION,
	SOURCE_PRIMARY_REFERENCE,
	SOURCE_SECONDARY_REFERENCE,
	SOURCE_PROMOTION_ID
	)
	VALUES
	(
	 p_import_interface_id,
	 sysdate,
	 FND_GLOBAL.user_id,
	 SYSDATE,
	 FND_GLOBAL.user_id,
	 FND_GLOBAL.login_id,
	 FND_GLOBAL.conc_request_id ,
	 FND_GLOBAL.prog_appl_id,
	 FND_GLOBAL.conc_program_id ,
	 SYSDATE,
	 'LEAD_LOAD',
	 SYSDATE,
	 'NEW',
	l_header_rec.PARTY_NAME,
	l_header_rec.promo_code,
	l_header_rec.PARTY_ID,
	l_header_rec.PARTY_TYPE,
	l_header_rec.PARTY_SITE_ID,
	l_header_rec.REL_PARTY_ID,
	l_header_rec.PHONE_ID,
	l_header_rec.NOTES,
	 nvl(fnd_profile.value_specific('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE', null, null, 671), 'EMAIL'),
	 'STORE',
	 nvl(l_header_rec.currency_code, fnd_profile.value('AS_CURRENCY_CODE')),
	l_header_rec.TOTAL_AMOUNT,
	 ' STORE ' ||l_header_rec.quote_header_id,
	 'SINGLE',
	 l_header_rec.lead_description,
	 l_header_rec.SOURCE_PRIMARY_REFERENCE,
         l_header_rec.SOURCE_SECONDARY_REFERENCE,
         l_header_rec.SOURCE_PROMOTION_ID
  );

Line: 1375

       /** SELECT MAX(IT.INTEREST_TYPE_ID)
        into   p_interest_type_id
        from AS_INTEREST_TYPES_B IT,
        MTL_CATEGORIES_B MC,
        MTL_ITEM_CATEGORIES MIC,FND_ID_FLEX_STRUCTURES FIFS
        WHERE
        FIFS.ID_FLEX_CODE = 'MCAT' AND
        FIFS.APPLICATION_ID = 401 AND
        FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES' AND
        MC.STRUCTURE_ID = FIFS.ID_FLEX_NUM AND
        MC.SEGMENT1 = TO_CHAR(IT.INTEREST_TYPE_ID) AND
        MIC.CATEGORY_ID = MC.CATEGORY_ID
        AND MIC.inventory_item_id = p_lines_rec_tbl(i).inventory_item_id
	and MIC.organization_id = p_lines_rec_tbl(i).organization_id;  **/

Line: 1393

        INSERT INTO AS_IMP_LINES_INTERFACE
        (
        IMP_LINES_INTERFACE_ID,
        IMPORT_INTERFACE_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE,
        INTEREST_TYPE_ID,
        PRIMARY_INTEREST_CODE_ID,
        SECONDARY_INTEREST_CODE_ID,
        INVENTORY_ITEM_ID,
        ORGANIZATION_ID,
        UOM_CODE,
        QUANTITY,
        BUDGET_AMOUNT,
        SOURCE_PROMOTION_ID
        )
        VALUES
        (
         as_imp_lines_interface_s.nextval,
         p_import_interface_id,
         SYSDATE,
         FND_GLOBAL.user_id,
         SYSDATE,
         FND_GLOBAL.user_id,
         FND_GLOBAL.login_id,
         FND_GLOBAL.conc_request_id ,
         FND_GLOBAL.prog_appl_id,
         FND_GLOBAL.conc_program_id ,
         SYSDATE,
         null,
         null,
         null,
         p_lines_rec_tbl(i).inventory_item_id,
         p_lines_rec_tbl(i).organization_id,
	 p_lines_rec_tbl(i).UOM_CODE,
         p_lines_rec_tbl(i).QUANTITY,
         p_lines_rec_tbl(i).LINE_PRICE,
         p_lines_rec_tbl(i).PROMOTION_ID
        );

Line: 1493

    SELECT source_code_id
    FROM ams_source_codes
    WHERE source_code = c_promotion_code
    AND active_flag = 'Y';

Line: 1714

       select log_id, num_imported, Num_failed, Num_success
       From ibe_lead_import_log
       where lead_type  = G_QUOTE_LEAD
       And status = 1 ;