Friday, January 11, 2019

Supplier Contact Information Queries

Hi Everyone,
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.


Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.


  1. Address Book--> Communication Field.
  2. Contact Directory
  3. Remittance Email id at Site Level
  4. Remittance Email id at Header Level

Queries : 

  • Address Book --> Communication Field


select ass.vendor_name, ass.segment1 supplier_num,hcp.email_address
from hz_party_sites hps,
hz_contact_points hcp,
ap_suppliers ass,
ap_supplier_sites_all assa
where 1=1 
and ass.vendor_id=assa.vendor_id
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 assa.org_id = xxxx;
--AND ass.segment1='77439'


  • Contact Directory

SELECT  asu.segment1 Supp_Num
,asu.vendor_name
,hpcp.email_address
FROM
 hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
--,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpcp.contact_point_type='EMAIL'
AND assa.org_id = xxxx
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES'
--AND asu.segment1='77439'


  • Remittance Email id at Site Level

  SELECT
       s.vendor_name              "Vendor Name",
       s.segment1                 "Vendor Number",
       s.vendor_type_lookup_code  "Vendor Type",
       st.vendor_site_code        "Vendor Site Code",
       ou.name                    "Operating Unit",
       iepa.remit_advice_delivery_method  "Remittance Delivery Method",
       iepa.remit_advice_email            "Remittance Advice Email"
  FROM
       ap.ap_suppliers              s,
       ap.ap_supplier_sites_all     st,
       hr_operating_units           ou,
       iby.iby_external_payees_all  iepa
 WHERE
       1=1
   AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))
   AND s.enabled_flag = 'Y'
   AND iepa.supplier_site_id = st.vendor_site_id
   AND iepa.payee_party_id = s.party_id
   AND st.org_id = ou.organization_id
   AND st.vendor_id = s.vendor_id
  -- and s.segment1='77439'
   and st.org_id= xxx
 ORDER BY s.vendor_name, st.vendor_site_code;



  • Remittance Email id at Header Level

SELECT aps.vendor_name, aps.segment1 supplier_num,iepa.remit_advice_email 
       FROM  APPS.ap_suppliers aps,
          apps.ap_supplier_sites_all apss
       ,APPS.iby_external_payees_all iepa
       WHERE  iepa.payee_party_id = aps.party_id
       and aps.vendor_id= apss.vendor_id
         AND default_payment_method_code is null
        -- AND  aps.segment1='77439'
        and apss.org_id = xxx
         and iepa.party_site_id IS null

Monday, September 10, 2018

Description for GL Interface Error Status

Hi,
If you need to find the description of various statuses in GL INTERFACE generated from Journal Import Program in GL,  it can be done via Standard Lookup Called 'psp_susp_ac_errors'


SELECT Description,Lookup_code FROM  fnd_lookups where lookup_type='PSP_SUSP_AC_ERRORS'
WHERE LOOKUP_CODE=GL_INTERFACE.STATUS


Thanks.

Monday, June 25, 2018

Submit GL Journal Import From PLSQL

GL Journal Import is used to import journals from sub ledgers and external systems into Oracle GL. We can do it manually or programatically. This post describes how to call the GL Journal Import from plsql procedure. We will be looking at importing AR (Receivable) Journal Entries.


Pre Requisite step is to insert the journal records to be imported into GL_INTERFACE Table. The status of the records in the GL_INTERFACE table for new records should be “NEW”.


DECLARE
 
   l_conc_id          NUMBER;
   l_int_run_id       NUMBER;
   l_access_set_id    NUMBER;
   l_org_id           NUMBER := 81;
   l_sob_id           NUMBER := 101;
   l_user_id          NUMBER := FND_GLOBAL.USER_ID;
   l_resp_id          NUMBER := FND_GLOBAL.RESP_ID;
   l_resp_app_id      NUMBER := FND_GLOBAL.RESP_APPL_ID;
 
BEGIN
 
   fnd_global.apps_initialize
   (
      user_id       => l_user_id       --User Id
      ,resp_id      => l_resp_id       --Responsibility Id
      ,resp_appl_id => l_resp_app_id   --Responsibility Application Id
   );
 
   mo_global.set_policy_context('S',l_org_id);
 
   SELECT   gl_journal_import_s.NEXTVAL
     INTO   l_int_run_id
     FROM   dual;
 
   SELECT   access_set_id
     INTO   l_access_set_id
     FROM   gl_access_sets
    WHERE   name = 'VISION OPERATIONS SET' ;
 
   INSERT INTO gl_interface_control
   (
      je_source_name
      ,interface_run_id
      ,status
      ,set_of_books_id
   )
   VALUES
   (
      'Receivables'
      ,l_int_run_id
      ,'S'
      ,l_sob_id
   );
 
   l_conc_id := fnd_request.submit_request
                   ( application   => 'SQLGL'
                    ,program       => 'GLLEZL'
                    ,description   => NULL
                    ,start_time    => SYSDATE
                    ,sub_request   => FALSE
                    ,argument1     => l_int_run_id    --interface run id
                    ,argument2     => l_access_set_id --data access set_id
                    ,argument3     => 'N'             --post to suspense
                    ,argument4     => NULL            --from date
                    ,argument5     => NULL            --to date
                    ,argument6     => 'N'             --summary mode
                    ,argument7     => 'N'             --import DFF
                    ,argument8     => 'Y'             --backward mode
                   );
 
   COMMIT;
 
   DBMS_OUTPUT.PUT_LINE('GL Import Submitted. Request Id : '||l_conc_id);
 
EXCEPTION
   WHEN OTHERS THEN
 
      DBMS_OUTPUT.PUT_LINE('Error while submitting the GL Import Program.');
      DBMS_OUTPUT.PUT_LINE('Error : '||SQLCODE||'-'||SUBSTR(SQLERRM,1,200));
END;

Supplier Contact Information Queries

Hi Everyone, Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier. Their are 4 places, ...