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

)