APPS.JAI_PAN_UPDATE_PKG SQL Statements

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

Line: 38

Procedure pan_update ( P_errbuf      OUT NOCOPY varchar2,
		       P_return_code OUT NOCOPY varchar2,
                       P_vendor_id    IN         PO_VENDORS.vendor_id%TYPE,
                       P_old_pan_num  IN   JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
		       P_new_pan_num  IN   JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
		       P_debug_flag   IN         varchar2) is


/* Cursor to lock the jai_ap_tds_thhold_grps */

Cursor C_lock_thhold_grps is
 select threshold_grp_id,
        vendor_id,
	org_tan_num,
	vendor_pan_num,
	section_type,
	section_code,
	fin_year,
	total_invoice_amount,
	total_invoice_cancel_amount,
	total_invoice_apply_amount,
	total_invoice_unapply_amount,
	total_tax_paid,
	total_thhold_change_tax_paid,
	current_threshold_slab_id,
	created_by,
	creation_date,
	last_updated_by,
	last_update_date,
	last_update_login
   from JAI_AP_TDS_THHOLD_GRPS
  where vendor_id = P_vendor_id
    and vendor_pan_num = p_old_pan_num
  order by vendor_id,threshold_grp_id
  for UPDATE of threshold_grp_id NOWAIT;

Line: 76

/* Update the tables in the following order

(1) ja_in_vendor_tds_info_hdr
(2) jai_ap_tds_thhold_grps
(3) jai_ap_tds_thhold_xceps

*/

lv_vendor_site_id_updated varchar2(1000) ;

Line: 85

lv_thhold_grp_id_updated varchar2(1000) ;

Line: 86

lv_thhold_xcep_id_updated varchar2(1000) ;

Line: 96

 lv_vendor_site_id_updated  := '';

Line: 97

 lv_thhold_grp_id_updated   := '';

Line: 98

 lv_thhold_xcep_id_updated  := '';

Line: 142

  lv_debug_msg := ' 1. Update JAI_AP_TDS_THHOLD_GRPS';

Line: 151

     lv_debug_msg := ' 2. Going to update JAI_AP_TDS_THHOLD_GRPS';

Line: 157

      update JAI_AP_TDS_THHOLD_GRPS
         set vendor_pan_num = P_new_pan_num
       where vendor_id = P_vendor_id
         and vendor_pan_num = P_old_pan_num
	 and threshold_grp_id = thhold_grps.threshold_grp_id;

Line: 163

      lv_debug_msg := ' 3. Done with update of '|| thhold_grps.threshold_grp_id;

Line: 169

      lv_thhold_grp_id_updated := lv_thhold_grp_id_updated || '-' || thhold_grps.threshold_grp_id;

Line: 171

      lv_debug_msg := ' 4. Value of lv_thhold_grp_id_updated '|| lv_thhold_grp_id_updated;

Line: 182

  lv_debug_msg := ' 5. Update JAI_AP_TDS_VENDOR_HDRS';

Line: 188

  for vndr_tds_hdr in (select vthdr.*
                           from JAI_AP_TDS_VENDOR_HDRS vthdr
			  where vthdr.vendor_id = P_vendor_id
			    and vthdr.pan_no = P_old_pan_num)
    loop

     lv_debug_msg := ' 6. Going to update JAI_AP_TDS_VENDOR_HDRS';

Line: 200

      update JAI_AP_TDS_VENDOR_HDRS
         set pan_no = P_new_pan_num
       where vendor_id = vndr_tds_hdr.vendor_id
         and vendor_site_id = vndr_tds_hdr.vendor_site_id
	 and pan_no = P_old_pan_num;

Line: 207

     lv_debug_msg := ' 7. Done with update of vendor '|| vndr_tds_hdr.vendor_id;

Line: 215

        lv_vendor_site_id_updated := lv_vendor_site_id_updated || ' - '||vndr_tds_hdr.vendor_site_id;

Line: 218

      lv_debug_msg := ' 8. Value of lv_vendor_site_id_updated '|| lv_vendor_site_id_updated;

Line: 230

  lv_debug_msg := ' 9. Update jai_ap_tds_thhold_xceps';

Line: 236

  for thhold_xceps in (select tdsxps.*
                          from jai_ap_tds_thhold_xceps tdsxps
			 where tdsxps.vendor_id = P_vendor_id
			   and vendor_pan = P_old_pan_num)
   loop

     lv_debug_msg := ' 10. Going to update jai_ap_tds_thhold_xceps';

Line: 248

     Update jai_ap_tds_thhold_xceps
        set vendor_pan = P_new_pan_num
      where vendor_id = P_vendor_id
        and vendor_pan = P_old_pan_num;

Line: 253

     lv_debug_msg := ' 11. Done with update of vendor'||P_vendor_id ;

Line: 259

     lv_thhold_xcep_id_updated := lv_thhold_xcep_id_updated || '-' || thhold_xceps.threshold_exception_id;

Line: 261

     lv_debug_msg := ' 12. Value of lv_thhold_xcep_id_updated '|| lv_thhold_xcep_id_updated;

Line: 274

  lv_debug_msg := ' 13. Inside insert -  ';

Line: 280

   Insert into jai_ap_tds_pan_changes
    ( pan_change_id,
      vendor_id,
      old_pan_num,
      new_pan_num,
      request_id,
      request_date,
      vendor_site_id_updated,
      thhold_grp_id_updated,
      thhold_xcep_id_updated,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login
    )
   values
    ( jai_ap_tds_pan_changes_s.nextval,
      P_vendor_id,
      P_old_pan_num,
      P_new_pan_num,
      ln_request_id,
      sysdate,
      lv_vendor_site_id_updated,
      lv_thhold_grp_id_updated,
      lv_thhold_xcep_id_updated,
      sysdate,
      fnd_global.user_id,
      sysdate,
      fnd_global.user_id,
      fnd_global.login_id
    );

Line: 329

       Print_log(lv_debug_flag,'(Pan update :Exception) Vendor to be updated by this process are locked');

Line: 333

End pan_update;