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

Oracle Apps r12 API to Delete BEE (batch Element Entry) Lines

 

declare

cursor c1 is

select batch_line_id,object_version_number from pay_batch_lines

where batch_id = 126167;

begin

for i in c1 loop

pay_batch_element_entry_api.delete_batch_line

 (p_validate                      => false

  ,p_batch_line_id                 => i.batch_line_id

  ,p_object_version_number         => i.object_version_number

  );

  end loop;

  commit;

  exception when others then 

  dbms_output.put_line(sqlerrm);

end;

Apps r12 financials update budget account in the Purchase Requisition

CREATE OR REPLACE 

procedure update_req2(p_req_id in number, p_attribute_1 in varchar2 --new buget_account_id

)is

l_req_hdr XX_po_requisition_update_pub.req_hdr1;

l_req_line_tbl XX_po_requisition_update_pub.req_line_tbl1;

l_req_dist_tbl XX_po_requisition_update_pub.req_dist_tbl;

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHaR2(2000);

l_concatenated_segments varchar2(1000);

p_quantity number;

cursor c1 is

select attribute1,attribute2,org_id,segment1 from PO_REQUISITION_HEADERS_ALL where REQUISITION_HEADER_ID=p_req_id;

cursor c2 is

select * from PO_REQUISITION_LINES_ALL where REQUISITION_HEADER_ID=p_req_id;

cursor c3 (p_REQUISITION_LINE_ID number) is

 select * from PO_REQ_DISTRIBUTIONS_ALL  

  where REQUISITION_LINE_ID=p_REQUISITION_LINE_ID;

  L_J NUMBER:=0;

  L_K NUMBER:=0;

begin

--:= 204; --mandatory

--l_req_hdr.description := 'Updated VIA API';

for i in c1 loop

--insert into xxtest

--values('INSIDEC2',SYSDATE,p_req_id,I.ATTRIBUTE2||'-old',p_attribute_2);

--commit;

   L_J:=0;

   select fnd_flex_ext.get_segs(application_short_name  =>    'SQLGL',

                    key_flex_code     =>'GL#',

                    structure_number   =>'50408',

                    --combination_id    => I.ATTRIBUTE1

                    combination_id    => p_attribute_1

                    ) INTO l_concatenated_segments

                    FROM DUAL;

       l_req_hdr.org_id :=I.ORG_ID;

       l_req_hdr.segment1  :=I.segment1;

  for j in c2 loop

    L_K:=0;

    L_J:=L_J+1;

    l_req_line_tbl(L_J).requisition_line_num := L_J; --set the req line #

    l_req_line_tbl(L_J).requisition_number := i.segment1; --set req #

    

    for k in c3(j.REQUISITION_LINE_ID) loop

      L_K:=L_K+1;

      l_req_dist_tbl(L_K).requisition_number := i.segment1;

       l_req_dist_tbl(L_K).req_line_num := j.LINE_NUM ;

       l_req_dist_tbl(L_K).distribution_num := L_K;

      -- l_req_dist_tbl(L_K).budget_account:= l_concatenated_segments;

     -- update PO_REQ_DISTRIBUTIONS_ALL set BUDGET_ACCOUNT_ID= I.ATTRIBUTE1 where DISTRIBUTION_ID=k.DISTRIBUTION_ID;

      update PO_REQ_DISTRIBUTIONS_ALL set BUDGET_ACCOUNT_ID= p_attribute_1 where DISTRIBUTION_ID=k.DISTRIBUTION_ID;

    END LOOP;

  END LOOP;

 END LOOP;

  

 

    xx_PO_REQUISITION_UPDATE_PUB.update_requisition

    (

    p_init_msg_list => 'T'

    ,p_commit => 'Y'

    ,x_return_status => l_return_status

    ,x_msg_count => l_msg_count

    ,x_msg_data => l_msg_data

    ,p_submit_approval => 'N'

    ,p_req_hdr => l_req_hdr

    ,p_req_line_tbl => l_req_line_tbl

    ,p_req_dist_tbl => l_req_dist_tbl

    );

  dbms_output.put_line ('Test'|| l_return_status || ' MSG' ||l_msg_data);

end;

R12 SQL query for the employees under supervisor

 CREATE OR REPLACE VIEW XX_EMP_SUP_V AS

SELECT DISTINCT PERSON_ID,EMPLOYEE_NUMBER, SUPERVISOR_ID,EMPLOYEE_FULL_NAME,SUPERVISOR_FULL_NAME FROM(

SELECT     LEVEL LEVL1, e.*

      FROM (SELECT DISTINCT papf.person_id, papf.employee_number,

                            papf.full_name "EMPLOYEE_FULL_NAME",

                            paaf.supervisor_id,

                            papf1.employee_number "SUPERVISOR_EMP_NUMBER"

                            ,papf1.full_name "SUPERVISOR_FULL_NAME"

                       FROM apps.per_all_people_f papf,

                            apps.per_all_assignments_f paaf,

                            apps.per_all_people_f papf1,

                            apps.per_person_types ppt

                      WHERE papf.person_id = paaf.person_id

                        AND papf1.person_id = paaf.supervisor_id

                        --AND papf.business_group_id = 142

                        AND papf.business_group_id = paaf.business_group_id

                        AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                                AND papf.effective_end_date

                        AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date

                        AND papf1.effective_end_date

                        AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                                AND paaf.effective_end_date

                        AND ppt.person_type_id = papf.person_type_id

                        AND ppt.user_person_type <> 'Ex-employee') e

CONNECT BY PRIOR person_id = supervisor_id  and prior sys_guid() is not null 

START WITH person_id = person_id--4530;--5541;-- pass here supervisorid

)