create job api for Oracle Apps R12

DECLARE
  lv_group_name            VARCHAR2(200) := 'HR_51';
  ln_business_group_id     NUMBER;
  ln_job_group_id          NUMBER; 
  ln_job_id                NUMBER;
  ln_object_version_number NUMBER;
  ln_job_definition_id     NUMBER;
  lv_job_name              VARCHAR2(200);
  
  CURSOR C1 IS 
  
  SELECT INITCAP(A)JOB_NAME
   FROM TESTA
   where   UPPER(A)  NOT IN (SELECT UPPER(NAME) FROM PER_JOBS)
   ;
   
   
   
BEGIN

  -- get group id and business group id
  BEGIN
    SELECT business_group_id,
           job_group_id
      INTO ln_business_group_id,
           ln_job_group_id
      FROM per_job_groups
     --WHERE displayed_name = lv_group_name;
     where business_group_id = 81;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to get the job group details.Error:'||SQLERRM);
      RAISE;
  END;
FOR I  IN C1 LOOP
BEGIN
  HR_JOB_API.CREATE_JOB
  (p_validate                      => FALSE
  ,p_business_group_id             => ln_business_group_id
  ,p_date_from                     => TO_DATE('01-JAN-1950')
  ,p_comments                      => 'Uploaded from API 31032022'
  ,p_date_to                       => NULL
  ,p_job_group_id                  => ln_job_group_id
  ,p_concat_segments               => i.JOB_NAME
  ,p_language_code                 => 'US'
  ,p_job_id                        => ln_job_id
  ,p_object_version_number         => ln_object_version_number
  ,p_job_definition_id             => ln_job_definition_id
  ,p_name                          => lv_job_name
  );
 

UPDATE TESTA SET B = 'DONE'
WHERE A = I.JOB_NAME;

  DBMS_OUTPUT.PUT_LINE('JOB ID: '||ln_job_id);
  DBMS_OUTPUT.PUT_LINE('OBJECT_VERSION_NUMBER: '||ln_object_version_number);
  DBMS_OUTPUT.PUT_LINE('JOB_DEFINITION_ID: '||ln_job_definition_id);
  DBMS_OUTPUT.PUT_LINE('JOB_NAME: '||lv_job_name);
  
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to create a job.Error:'||SQLERRM);
      --RAISE;
      UPDATE TESTA SET B = 'ERROR'
WHERE A = I.JOB_NAME;
 
      
END;


END LOOP;

END;

Oracle sql query using _ (underscore) in condition

 

Query 1:

SELECT * FROM FND_USER

WHERE

USER_NAME LIKE 'FC_%';

 

Output: FCCI, FC_

 

Query 2:

SELECT * FROM FND_USER

WHERE

REPLACE(USER_NAME, '_', '~') LIKE 'FC~%';

 

Output: Only FC_

Apps R12 report output in text layout width issue

 Step1:  Concurrent Program --> Style: BACS, 

                                                     Style Required checked

                                                    keep Rows and Columns fields blank.

Step2: in the rdf file 

          Paper Layout --> Main Section -> Orientiation  Landscape 

                                                           Report Width --> 100 or 200 or 300

                                                          Section Width --> 20

Run the report in the report builder itself and check the output, drag if needed the width here itself.

 

Sending Email with Attachment from the dynamic folder and latest file

 Requirement:

to select the latest file in the current Dated folder (ddmmyyyy).

1. Creating the powershell file with ps commands. (sendemail.ps1)

2. bat file to run the above file. (sendemail.bat)

keep the both files in the same folder.

------------1-----------

$EmailFrom = "sender@gmail.com" 

$EmailTo = "receiver@gmail.com"

$Subject = "Test1"

$Body = "Test Body"

$folderName = (Get-Date).tostring("ddMMyyyy") 

$path = "C:\Users\admin\Desktop\SendEmailScripts\"+$folderName + "\"

ECHO $path

$latest = (Get-ChildItem  $path | Sort-Object -Descending -Property LastWriteTime | select -First 1)

ECHO $latest

$AttachmentName = $path+$latest.Name 

ECHO $AttachmentName

$SMTPServer = "smtp.gmail.com"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $Body)

$Attachment  = New-Object System.Net.Mail.Attachment($AttachmentName) 

$SMTPMessage.Attachments.Add($AttachmentName)  

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)

$SMTPClient.EnableSsl = $true

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("sender@gmail.com", "yourpassword");

$SMTPClient.Send($SMTPMessage)

--------------------------------2-----------------

@ECHO OFF

PowerShell.exe  -ExecutionPolicy Bypass -Command "& '%~dpn0.ps1'"

PAUSE

SQL Query Apps R12 user last_connect, responsbility, type userInfo

 select last_connect, usr.user_name, resp.responsibility_key, function_type--, icx.*

  from apps.icx_sessions icx

  join apps.fnd_user usr on usr.user_id=icx.user_id

  left join apps.fnd_responsibility resp on resp.responsibility_id=icx.responsibility_id

  where 1=1-- last_connect>sysdate-nvl(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),30)/60/24

    and disabled_flag != 'Y' and pseudo_flag = 'N'

    AND LAST_CONNECT >= '01-JUN-2021'

    and usr.user_name  in ('AFZAL')

    ORDER BY 1,2

Apps R12 Supplier with Tax Number Query

   SELECT DISTINCT

         AP.VENDOR_ID,

         AP.SEGMENT1 VENDOR_NUMBER,

         AP.VENDOR_NAME,

         NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation') SUPPLIER_TYPE,

         AP.START_DATE_ACTIVE,

         AP.END_DATE_ACTIVE,

         --         hp.duns_number ISO_NUMBER,

         hp.duns_number_C ISO_NUMBER,

         hp.mission_statement REG_NUMBER_DOF,

         --         hp.*,

         AP.STANDARD_INDUSTRY_CLASS LICENCE_NUMBER,

         APS.VENDOR_SITE_CODE,

         APS.VENDOR_SITE_ID,

         APS.ADDRESS_LINE1,

         AP.CREATION_DATE,

         NVL (

            (SELECT FULL_NAME

               FROM FND_USER A, PER_ALL_PEOPLE_F B

              WHERE     A.EMPLOYEE_ID = B.PERSON_ID

                    AND SYSDATE BETWEEN EFFECTIVE_START_DATE

                                    AND EFFECTIVE_END_DATE

                    AND A.USER_ID = AP.CREATED_BY),

            U.USER_NAME)

            AS "CREATED_BY",

         AP.LAST_UPDATE_DATE,

         NVL (

            (SELECT FULL_NAME

               FROM FND_USER C, PER_ALL_PEOPLE_F D

              WHERE     C.EMPLOYEE_ID = D.PERSON_ID

                    AND SYSDATE BETWEEN EFFECTIVE_START_DATE

                                    AND EFFECTIVE_END_DATE

                    AND C.USER_ID = AP.CREATED_BY),

            U2.USER_NAME)

            AS "LAST_UPDATED_BY",

         (SELECT HCP.EMAIL_ADDRESS

            FROM HZ_PARTY_SITES HPS, HZ_CONTACT_POINTS HCP, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.CONTACT_POINT_TYPE = 'EMAIL'

                 AND ROWNUM = 1)

            EMAIL,

         (SELECT HCP.PHONE_AREA_CODE || ' ' || HCP.PHONE_NUMBER PHONE

            FROM HZ_CONTACT_POINTS HCP, HZ_PARTY_SITES HPS, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.PRIMARY_FLAG = 'Y'

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HCP.STATUS = 'A'

                 AND HCP.CONTACT_POINT_TYPE = 'PHONE'

                 AND HCP.PHONE_LINE_TYPE = 'GEN'

                 AND ROWNUM = 1)

            PHONE,

         (SELECT HCP.PHONE_AREA_CODE || ' ' || HCP.PHONE_NUMBER PHONE

            FROM HZ_CONTACT_POINTS HCP, HZ_PARTY_SITES HPS, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.STATUS = 'A'

                 AND HCP.CONTACT_POINT_TYPE = 'PHONE'

                 AND HCP.PHONE_LINE_TYPE = 'FAX'

                 AND ROWNUM = 1)

            FAX,

         ieb.bank_name "BANK NAME",

         ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

         ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME",

         ieba.IBAN "BANK_IBAN"

           ,AP.PARTY_ID

           ,APS.PARTY_SITE_ID

           ,ap.VAT_REGISTRATION_NUM

    FROM PER_ALL_PEOPLE_F PER,

         AP_SUPPLIERS AP,

         hz_parties hp,

         AP_SUPPLIER_SITES_ALL APS,

         apps.iby_ext_bank_accounts ieba,

         apps.iby_account_owners iao,

         apps.iby_ext_banks_v ieb,

         FND_USER U,

         FND_USER U2

   WHERE     1 = 1

         AND PER.PERSON_ID(+) = AP.EMPLOYEE_ID

         AND ap.party_id = hp.party_id

         AND AP.VENDOR_ID = APS.VENDOR_ID(+)

         AND AP.SEGMENT1 >= NVL ( :p_from_vendor_id, AP.SEGMENT1)

         AND AP.SEGMENT1 <= NVL ( :p_to_vendor_id, AP.SEGMENT1)

         AND U.USER_ID = AP.CREATED_BY

         AND U2.USER_ID = AP.LAST_UPDATED_BY

         AND iao.account_owner_party_id(+) = ap.party_id

         AND ieba.ext_bank_account_id(+) = iao.ext_bank_account_id

         AND iao.PRIMARY_FLAG(+) = 'Y'

         AND ieba.ext_bank_account_id(+) = iao.ext_bank_account_id

         AND ieba.bank_id = ieb.bank_party_id(+)

         AND  AP.END_DATE_ACTIVE IS NULL ---ADDED ON 03-09-2020 BY TAREK

         AND NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation') = NVL (:p_supplier_type , NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation'))

ORDER BY 1

EBS R12.2 supplier OAF page personalization to keep checkbox default value CO Extension

EBS R12.2 supplier OAF page personalization to keep checkbox default value

 package xxtaxcoextension.oracle.apps.pos.supplier.webui;

import oracle.apps.pos.supplier.webui.ByrTaxDtCO;

import oracle.apps.fnd.framework.webui.OAPageContext;

import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.beans.message.OAMessageCheckBoxBean;

public class ByrTaxDtCOEx extends ByrTaxDtCO {

  public ByrTaxDtCOEx() {

  }

  public void processRequest(OAPageContext pageContext, OAWebBean webBean)

   {

    super.processRequest(pageContext, webBean);

     OAMessageCheckBoxBean CheckBoxBean = (OAMessageCheckBoxBean)webBean.findChildRecursive("allwTaxWthld");

      CheckBoxBean.setChecked(true);

   }

}

Reference: https://mogalafzal.blogspot.com/search?q=co+extension

for detail steps visit above link