Oracle Apps Knowledge Sharing

In this post, we have given a query which gives the basic details related to External bank accounts, Bank branches and bank setup details. This inturn helps you to understand the joins between bank related tables/views.

Tables/Views Involved:

iby_external_bank_accounts_v

iby_ext_banks_v

iby_ext_bank_branches_v

fnd_territories_vl

Query:

 

     SELECT ieba.ext_bank_account_id,
            ieba.bank_account_name,
            ieba.bank_account_number,
            ieba.bank_account_num_electronic,
            ieba.currency_code,
            ieba.primary_acct_owner_name,
            ieba.start_date bank_account_start_date,
            ieba.end_Date  bank_account_end_date,   
            ieba.country_code,            
            ft.territory_short_name country,
            ieb.bank_name,
            ieb.address1 bank_address1,
            ieb.address2 bank_address2,
            ieb.city bank_city,
            ieb.state bank_state,
            ieb.postal_code bank_postal_code,
            ieb.country bank_country,
            ieba.bank_branch_name,
            ieba.branch_number,
            iebb.bank_branch_type,
            iebb.start_date branch_start_date,
            iebb.end_date branch_end_date,
            iebb.address_line1 branch_address_line1,
            iebb.address_line2 branch_address_line2,
            iebb.city bank_city,
            iebb.state bank_state,
            iebb.zip bankbranch_postal_code,
            iebb.country bank_branch_country,
            iebb.operation_flag
       FROM apps.iby_external_bank_accounts_v ieba
           ,apps.iby_ext_banks_v              ieb
           ,apps.iby_ext_bank_branches_v      iebb
           ,fnd_territories_vl ft
      WHERE ieba.bank_party_id = ieb.bank_party_id
        AND ieb.bank_party_id = iebb.bank_party_id
        AND ieba.branch_party_id = iebb.branch_party_id
        AND ft.territory_code = ieba.country_code; 

In this post, we have provided a base script which can be used to import daily currency conversion rates in general ledger

Interface Table: GL_DAILY_RATES_INTERFACE

Instance: R12

Script:

DECLARE 
  lv_from_currency   VARCHAR2(5) DEFAULT 'INR';
  lv_to_currenty     VARCHAR2(5) DEFAULT 'SEK';
  ln_conversion_rate NUMBER DEFAULT 10;
  ln_batch_number    NUMBER DEFAULT 1;
  ln_dummy_char      NUMBER;
  lv_request_id      NUMBER;
  lv_result          BOOLEAN;
  lv_phase1          VARCHAR2(100);
  lv_status1         VARCHAR2(100);
  lv_dev_phase1      VARCHAR2(100);
  lv_dev_status1     VARCHAR2(100);
  lv_message1        VARCHAR2(100);
BEGIN
  -- check whether the from currency exists and enabled 
  BEGIN 
    SELECT 1 
      INTO ln_dummy_char
      FROM fnd_currencies 
     WHERE currency_flag = 'Y' 
       AND enabled_flag  = 'Y'
       AND currency_code = UPPER(lv_from_currency);
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('Error: From Currency is not valid');
  END;
  
  -- check whether the from currency exists and enabled 
  BEGIN 
    SELECT 1 
      INTO ln_dummy_char
      FROM fnd_currencies 
     WHERE currency_flag = 'Y' 
       AND enabled_flag  = 'Y'
       AND currency_code = UPPER(lv_to_currenty);
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('Error: To Currency is not valid');
  END;  
  
  -- Insert into Interface table 
  INSERT INTO gl_daily_rates_interface
        (from_currency, 
         to_currency, 
         from_conversion_date, 
         to_conversion_date, 
         user_conversion_type, 
         conversion_rate, 
         mode_flag, 
         inverse_conversion_rate, 
         user_id, 
         launch_rate_change, 
         error_code,
         batch_number
        ) 
  VALUES 
       (lv_from_currency,
        lv_to_currenty,
        TO_DATE(SYSDATE,'DD-MON-RRRR'),
        TO_DATE(SYSDATE+30,'DD-MON-RRRR'),
        'Corporate',
        ln_conversion_rate,
        'I',
        (1/ln_conversion_rate), 
        0,
        NULL,
        NULL,
        ln_batch_number
       );     

   -- Initialize the enviroment 
   fnd_global.apps_initialize
   (
      user_id       => 0       --User Id
      ,resp_id      => 52480   --Responsibility Id
      ,resp_appl_id => 101     --Responsibility Application Id
   );
  
   -- Submit Program - Daily Rates Import and Calculation
   lv_request_id := fnd_request.submit_request(application => 'SQLGL',
                                               program     => 'GLDRICCP',
                                               description => NULL,
                                               start_time  => NULL,
                                               sub_request => NULL,
                                               argument1   => ln_batch_number 
                                               );
   COMMIT;
   
   IF lv_request_id = 0
   THEN
     DBMS_OUTPUT.PUT_LINE(' Failed to submit Process GLDRICCP.' 
                           || fnd_message.get
                         );
   ELSE
     lv_result := fnd_concurrent.wait_for_request(lv_request_id
                                                 ,1
                                                 ,0
                                                 ,lv_phase1
                                                 ,lv_status1
                                                 ,lv_dev_phase1
                                                 ,lv_dev_status1
                                                 ,lv_message1
                                                 );
  END IF;
  
  IF NOT lv_result
  THEN
    DBMS_OUTPUT.PUT_LINE('No Status returned for the request Id: ' 
                          || lv_request_id
                        );
  ELSE
    DBMS_OUTPUT.PUT_LINE('The Req-Id of GLDRICCP Process is ' 
                         || lv_request_id
                        );
  END IF;
END
;

In this post we have given a query which helps you to gather the basic customer details and profile  information. In other words, it helps to understand the link between below tables.

Tables Used:

HZ_PARTIES

HZ_CUST_ACCOUNTS

HZ_CUST_ACCT_SITES_ALL

HZ_CUSTOMER_PROFILES

HZ_CUST_PROFILE_AMTS

HZ_CUST_SITE_USES_ALL

RA_GROUPING_RULES

AR_STATEMENT_CYCLES

RA_TERMS

AR_COLLECTORS

HZ_CUST_PROFILE_CLASSES

Instance: R12

Script: 

SELECT DISTINCT hzp.party_name,
       hzp.orig_system_reference party_osr,
       hzp.party_number,
       hzp.party_type,
       hzp.duns_number,
       hzp.tax_reference,
       hzp.category_code,
       hzc.orig_system_reference cust_osr,
       hzc.account_number,
       hzc.account_name,
       hou.NAME ou_name,
       (SELECT a.NAME
          FROM ar.hz_cust_profile_classes a
         WHERE a.profile_class_id = hzcp.profile_class_id
		) profile_class_name,
       (SELECT a.NAME
          FROM ar.ar_collectors a
         WHERE a.collector_id = hzcp.collector_id
		) collector_name,
       (SELECT a.NAME
          FROM ar.ar_statement_cycles a
         WHERE statement_cycle_id = hzcp.statement_cycle_id
		) statement_cycle_name,
       (SELECT a.NAME 
	 FROM apps.ra_terms a 
	WHERE a.term_id = hzcp.standard_terms) payment_term_name,
       (SELECT a.NAME
          FROM ar.ra_grouping_rules a
         WHERE a.grouping_rule_id = hzcp.grouping_rule_id) grouping_rules_name,
       (SELECT a.orig_system_reference
          FROM hz_cust_site_uses_all a
         WHERE a.site_use_id = hzcp.site_use_id) profile_site_use_osr,
       hzcp.credit_checking,
       hzcp.tolerance,
       hzcp.credit_hold,
       hzcp.credit_balance_statements,
       hzcp.send_statements,
       hzcp.jgzz_attribute1 enable_late_charges,
       hzcp.interest_charges,
       hzcp.jgzz_attribute2 late_charge_calculation_trx,
       hzcp.jgzz_attribute5 hold_charged_invoices_flag,
       hzcp.dunning_letters,
       hzcp.discount_terms,
       hzcp.interest_period_days,
       hzcp.payment_grace_days,
       hzcp.discount_grace_days,
       hzcp.auto_rec_incl_disputed_flag,
       hzcp.attribute_category cust_prof_dff_category,       
       hzcp.attribute2 cust_prof_dff_attr2,
       hzcpm.currency_code,
       hzcpm.trx_credit_limit,
       hzcpm.overall_credit_limit,
       hzcpm.min_dunning_amount,
       hzcpm.min_dunning_invoice_amount,
       hzcpm.max_interest_charge,
       hzcpm.min_statement_amount,
       hzcpm.auto_rec_min_receipt_amount,
       hzcpm.interest_rate,
       hzcpm.min_fc_balance_amount,
       hzcpm.min_fc_invoice_amount,
       (SELECT a.orig_system_reference
          FROM hz_cust_site_uses_all a
         WHERE a.site_use_id = hzcpm.site_use_id
	   ) profile_amt_site_use_osr
  FROM ar.hz_parties             hzp,
       ar.hz_cust_accounts       hzc,
       ar.hz_cust_acct_sites_All has,
       ar.hz_customer_profiles   hzcp,
       ar.hz_cust_profile_amts   hzcpm,
       apps.hr_operating_units   hou
 WHERE hzp.party_id                 = hzc.party_id
   AND hzc.cust_account_id          = hzcp.cust_account_id
   and hzcp.cust_account_profile_id = hzcpm.cust_account_profile_id(+)
   AND has.cust_account_id          = hzc.cust_Account_id
   AND HAS.ORG_ID                   = HOU.ORGANIZATION_ID
   AND hou.organization_id          = 105 -- Operating unit id
   -- Customer is active and created after cut off date
   AND ((trunc(hzc.creation_date) >'01-MAY-2012' and hzc.status = 'A')
   -- AR invoices created after cut off date
    or exists
               (SELECT 1
                  FROM ar.ra_customer_trx_all t
                 WHERE trunc(t.trx_date)>'01-MAY-2012'
                 and t.bill_to_customer_id = hzc.cust_account_id
                 and t.org_id = hzc.org_id
                 and hzc.status = 'A')
    -- AR invoice Payments are in open status 
      or exists
               (SELECT 1
                  FROM ar.ar_payment_schedules_all apsa
                 WHERE apsa.CLASS IN ('INV', 'CM', 'DM','PMT')
                   AND apsa.status = 'OP'
                   AND apsa.org_id = hzc.org_id
                   AND apsa.customer_id = hzc.cust_account_id
                   AND to_number(apsa.acctd_amount_due_remaining) <>
                       to_number('0')
                 GROUP BY apsa.customer_id
                 having sum(nvl(apsa.acctd_amount_due_remaining,0))<>0))