Scheduling Jobs with DBMS_SCHEDULER in Oracle (PL/SQL Procedure)

 First define a program that is capable of executing PL/SQL script:

CREATE PROCEDURE xx_plsql_script_01
IS
  . . . .
EXCEPTION
WHEN OTHERS THEN
  -------Error Message
END;

STEP 1 – Create program

BEGIN
  dbms_scheduler.create_program( program_name => 'stored_procedure_program',
                                program_type => 'STORED_PROCEDURE',
                                program_action => 'XX_PLSQL_SCRIPT_01',
                                enabled => FALSE,
                                comments => 'Program for Data Insert/Update');
  dbms_scheduler.ENABLE (name=>'stored_procedure_program');
END;

STEP 2 – Create Schedule

BEGIN
  dbms_scheduler.create_schedule ( schedule_name => 'XX_PLSQL_SCRIPT_01_11AM_SCHEDULE',
                                  start_date => systimestamp,
                                  repeat_interval => 'freq=daily; byhour=11; byminute=0; bysecond=0;',
                                  end_date => NULL,
                                  comments => 'Run 11AM everyday');
END;

STEP 3 – Create Job

BEGIN
  dbms_scheduler.create_job ( job_name => 'XX_PLSQL_SCRIPT_01_11am_JOB',
                             program_name => 'stored_procedure_program',
                             schedule_name => 'XX_PLSQL_SCRIPT_01_11AM_SCHEDULE',
                             enabled => TRUE,
                             comments => 'My test scheduler job for 11am');
END;

Ref: https://www.support.dbagenesis.com/post/scheduling-jobs-with-dbms_scheduler

Vendor Details Query In Oracel Apps R12

 SELECT pv.party_id,

       pv.vendor_id,
       pvs.vendor_site_id,
       pvs.party_site_id,
       pvs.org_id,
       hou.name                operating_unit,
       pv.vendor_name,
       pv.segment1             vendor_code,
       pv.creation_date,
       pv.end_date_active      vendor_inactive_date,
       pvs.inactive_date       vendor_site_inactive_date,
       pvs.vendor_site_code,
       ( Regexp_replace(pvs.address_line1, '[^-/&,;# ,.[:alnum:]]', '')
         || Regexp_replace(pvs.address_line2, '[^-/&,;# ,.[:alnum:]]', '')
         || Regexp_replace(pvs.address_line3, '[^-/&,;# ,.[:alnum:]]', '')
         || Regexp_replace(pvs.address_line4, '[^-/&,;# ,.[:alnum:]]', '') )
                               Vendor_address,
       pvs.city,
       pvs.zip,
       (SELECT ftt.territory_short_name
        FROM   apps.fnd_territories_tl ftt
        WHERE  pvs.country = ftt.territory_code
               AND ROWNUM = 1) country_name,
       (SELECT hl_ship.location_code
        FROM   apps.hr_locations hl_ship
        WHERE  PVS.ship_to_location_id = hl_ship.location_id
               AND ROWNUM = 1) ship_loc,
       (SELECT hl_bill.location_code
        FROM   apps.hr_locations hl_bill
        WHERE  PVS.bill_to_location_id = hl_bill.location_id
               AND ROWNUM = 1) bill_loc,
       (SELECT apt.name
        FROM   apps.ap_terms apt
        WHERE  pvs.terms_id = apt.term_id
               AND ROWNUM = 1) term_name,
       (SELECT gcc_lia.concatenated_segments
        FROM   apps.gl_code_combinations_kfv gcc_lia
        WHERE  pvs.accts_pay_code_combination_id = gcc_lia.code_combination_id
               AND ROWNUM = 1) liab_account,
       (SELECT gcc_pre.concatenated_segments
        FROM   apps.gl_code_combinations_kfv gcc_pre
        WHERE  pvs.prepay_code_combination_id = gcc_pre.code_combination_id
               AND ROWNUM = 1) prepay_account,
       (SELECT gcc_fut.concatenated_segments
        FROM   apps.gl_code_combinations_kfv gcc_fut
        WHERE  pvs.future_dated_payment_ccid = gcc_fut.code_combination_id
               AND ROWNUM = 1) future_account,
       (SELECT ieppm.payment_method_code
        FROM   apps.iby_external_payees_all iepa,
               apps.iby_ext_party_pmt_mthds ieppm
        WHERE  iepa.ext_payee_id = ieppm.ext_pmt_party_id
               AND ieppm.primary_flag = 'Y'
               AND iepa.supplier_site_id = pvs.vendor_site_id
               AND ( ( ieppm.inactive_date IS NULL )
                      OR ( ieppm.inactive_date > SYSDATE ) )
               AND iepa.party_site_id = pvs.party_site_id
               AND iePA.payee_party_id = pv.party_id
               AND ROWNUM = 1) payment_method,
       pvs.pay_on_code,
       (SELECT person.person_first_name
        FROM   apps.ap_supplier_contacts apsc,
               apps.hz_parties person,
               apps.hz_parties pty_rel
        WHERE  apsc.per_party_id = person.party_id
               AND apsc.rel_party_id = pty_rel.party_id
               AND apsc.org_party_site_id = pvs.party_site_id
               AND ROWNUM = 1) person_first_name,
       (SELECT person.person_last_name
        FROM   apps.ap_supplier_contacts apsc,
               apps.hz_parties person,
               apps.hz_parties pty_rel
        WHERE  apps.apsc.per_party_id = person.party_id
               AND apsc.rel_party_id = pty_rel.party_id
               AND apsc.org_party_site_id = pvs.party_site_id
               AND ROWNUM = 1) person_last_name,
       (SELECT pty_rel.primary_phone_number
        FROM   apps.ap_supplier_contacts apsc,
               apps.hz_parties person,
               apps.hz_parties pty_rel
        WHERE  apsc.per_party_id = person.party_id
               AND apsc.rel_party_id = pty_rel.party_id
               AND apsc.org_party_site_id = pvs.party_site_id
               AND ROWNUM = 1) primary_phone_number,
       (SELECT pty_rel.email_address
        FROM   apps.ap_supplier_contacts apsc,
               apps.hz_parties person,
               apps.hz_parties pty_rel
        WHERE  apsc.per_party_id = person.party_id
               AND apsc.rel_party_id = pty_rel.party_id
               AND apsc.org_party_site_id = pvs.party_site_id
               AND ROWNUM = 1) email_address,
       /*(select LISTAGG(regexp_replace(pvc.prefix||' '||pvc.first_name||' '||pvc.middle_name||' '||pvc.last_name,'[^- ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
         OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id,pvs.org_id)
         from        ap_supplier_contacts pvc
         where pvs.vendor_site_id = pvc.vendor_site_id
         and rownum=1)contact_person,
         (select LISTAGG(regexp_replace(pvc.phone,'[^-/()+&,;# ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
         OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id,pvs.org_id)
         from        ap_supplier_contacts pvc
         where pvs.vendor_site_id = pvc.vendor_site_id
         and rownum=1)phone_number,
         (select LISTAGG(regexp_replace(pvc.email_address,'[^-@_ ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
         OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id)
         from        ap_supplier_contacts pvc
         where pvs.vendor_site_id = pvc.vendor_site_id
         and rownum=1)email,*/
       (SELECT Regexp_replace(To_char (jpvs.vat_reg_no), '[^- ,.[:alnum:]]', '')
        FROM   apps.jai_cmn_vendor_sites jpvs
        WHERE  pvs.vendor_site_id = jpvs.vendor_site_id
               AND ROWNUM = 1) tin_number,
       (SELECT To_char (jpvs.service_tax_regno)
        FROM   apps.jai_cmn_vendor_sites jpvs
        WHERE  pvs.vendor_site_id = jpvs.vendor_site_id
               AND ROWNUM = 1) service_tax_regno,
       (SELECT jpvs.ec_code
        FROM   apps.jai_cmn_vendor_sites jpvs
        WHERE  pvs.vendor_site_id = jpvs.vendor_site_id
               AND ROWNUM = 1) ecc_code,
       (SELECT Regexp_replace(jpvs.cst_reg_no, '[^- ,.[:alnum:]]', '')
        FROM   apps.jai_cmn_vendor_sites jpvs
        WHERE  pvs.vendor_site_id = jpvs.vendor_site_id
               AND ROWNUM = 1) cst_reg_no,
       (SELECT jvt.tds_vendor_type_lookup_code
        FROM   apps.jai_ap_tds_vendor_hdrs jvt
        WHERE  pvs.vendor_site_id = jvt.vendor_site_id
               AND ROWNUM = 1) tds_vendor_type_code,
       pv.vendor_type_lookup_code,
       (SELECT flv.meaning
        FROM   apps.fnd_lookup_values flv
        WHERE  flv.lookup_code( + ) = pv.vendor_type_lookup_code
               AND flv.lookup_type( + ) = 'VENDOR TYPE'
               AND flv.LANGUAGE( + ) = Userenv('lang')
               AND ROWNUM = 1) vendor_type,
       (SELECT jvt.tan_no
        FROM   apps.jai_ap_tds_vendor_hdrs jvt
        WHERE  pvs.vendor_site_id = jvt.vendor_site_id
               AND ROWNUM = 1) tan_no,
       CASE
         WHEN pvs.create_debit_memo_flag = 'Y' THEN 'Active'
         ELSE NULL
       END                     create_debit_memo_flag,
       CASE
         WHEN pv.end_date_active IS NULL THEN 'Active'
         ELSE 'Inactive'
       END                     Vendor_Status,
       CASE
         WHEN pvs.inactive_date IS NULL THEN 'Active'
         ELSE 'Inactive'
       END                     Site_Status,
       (SELECT ieba.bank_account_num
        FROM   apps.iby_ext_bank_accounts ieba,
               apps.iby_external_payees_all iepa,
               apps.iby_pmt_instr_uses_all ipiua
        WHERE  ipiua.instrument_id = ieba.ext_bank_account_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND iePA.payee_party_id = pv.party_id
               AND iePA.supplier_site_id = pvs.vendor_site_id
               AND iePA.party_site_id = pvs.party_site_id
               AND ipiua.end_date IS NULL
               AND ROWNUM = 1) bank_account_num,
       (SELECT ieba.bank_account_name
        FROM   apps.iby_ext_bank_accounts ieba,
               apps.iby_external_payees_all iepa,
               apps.iby_pmt_instr_uses_all ipiua
        WHERE  ipiua.instrument_id = ieba.ext_bank_account_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND iePA.payee_party_id = pv.party_id
               AND iePA.supplier_site_id = pvs.vendor_site_id
               AND iePA.party_site_id = pvs.party_site_id
               AND ipiua.end_date IS NULL
               AND ROWNUM = 1) bank_account_name,
       (SELECT ieba.attribute1
        FROM   apps.iby_ext_bank_accounts ieba,
               apps.iby_external_payees_all iepa,
               apps.iby_pmt_instr_uses_all ipiua
        WHERE  ipiua.instrument_id = ieba.ext_bank_account_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND iePA.payee_party_id = pv.party_id
               AND iePA.supplier_site_id = pvs.vendor_site_id
               AND iePA.party_site_id = pvs.party_site_id
               AND ipiua.end_date IS NULL
               AND ROWNUM = 1) IFSC_CODE,
       (SELECT ipiua.attribute1
        FROM   apps.iby_ext_bank_accounts ieba,
               apps.iby_external_payees_all iepa,
               apps.iby_pmt_instr_uses_all ipiua
        WHERE  ipiua.instrument_id = ieba.ext_bank_account_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
               AND iePA.payee_party_id = pv.party_id
               AND iePA.supplier_site_id = pvs.vendor_site_id
               AND iePA.party_site_id = pvs.party_site_id
               AND ipiua.end_date IS NULL
               AND ROWNUM = 1) attribute1,
       (SELECT jrav.reporting_code
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) state_code,
       (SELECT jrav.reporting_code_description
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) state_name,
       (SELECT jprlv.registration_number
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) gst_register_number,
       (SELECT jprlv.secondary_registration_number
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) PAN_NO,
       (SELECT jprlv.registration_type_code
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) REGISTRATION_TYPE_CODE,
       (SELECT sec_registration_type_code
        FROM   apps.jai_party_regs jprv,
               apps.jai_party_reg_lines jprlv,
               apps.jai_reporting_associations jrav
        WHERE  jprlv.party_reg_id = jprv.party_reg_id
               AND jprv.party_reg_id = jrav.entity_id
               AND jprlv.regime_id = jrav.regime_id
               AND jprlv.regime_id = 10001--- 'IND GST'
               AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
               AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
                                           Nvl (jprlv.effective_to, SYSDATE))
               AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
                                           Nvl (jrav.effective_to, SYSDATE))
               AND jprv.party_id = pv.vendor_id
               AND jprv.party_site_id = pvs.vendor_site_id
               AND jprv.org_id = pvs.org_id
               AND ROWNUM = 1) SEC_REGISTRATION_TYPE_CODE,
       (SELECT lookup_code
        FROM   apps.pos_bus_class_reqs a,
               apps.pos_supplier_mappings b
        WHERE  a.mapping_id = b.mapping_id
               AND B.vendor_id = pv.vendor_id
               AND ROWNUM = 1) MSME_FLAG
FROM   apps.ap_suppliers pv,
       apps.ap_supplier_sites_all pvs,
       apps.hr_operating_units hou
WHERE  pv.vendor_id = pvs.vendor_id
       AND pvs.org_id = hou.organization_id;