Showing posts with label TCA. Show all posts
Showing posts with label TCA. Show all posts

Sunday, May 9, 2021

API to create a Phone Contact Point in TCA Oracle Apps R12 (HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT)

 

 

 

 

 

 

 

In this post, we have given a sample script to create a PHONE contact point for a Party in TCA (Trading Community Architecture).

API used: HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT

Tested Instance: R12.2.4

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_init_msg_list            VARCHAR2(200)  DEFAULT fnd_api.g_true;
  lv_return_status            VARCHAR2(200);
  ln_msg_count                NUMBER;
  lv_msg_data                 VARCHAR2(2000);
  lv_msg                      VARCHAR2(2000); 
  ln_contact_point_id         NUMBER;
  ln_object_version_number    NUMBER;
  lr_contact_point_rec_type   hz_contact_point_v2pub.contact_point_rec_type;
  lr_phone_rec_type           hz_contact_point_v2pub.phone_rec_type; 
BEGIN   
  lr_contact_point_rec_type.actual_content_source   
                   := hz_contact_point_v2pub.g_miss_content_source_type;  
  lr_contact_point_rec_type.owner_table_name        := 'HZ_PARTIES';
  lr_contact_point_rec_type.owner_table_id          := 1234;  
  lr_contact_point_rec_type.contact_point_type      := 'PHONE';
  lr_contact_point_rec_type.created_by_module       := 'TCA_V2_API';  
  lr_contact_point_rec_type.primary_flag            := 'N';
  
  lr_phone_rec_type.phone_line_type                 := 'GEN';
  lr_phone_rec_type.phone_area_code                 := '+91';
  lr_phone_rec_type.phone_number                    := '1234567890';
          
  hz_contact_point_v2pub.create_phone_contact_point 
           ( p_init_msg_list      => lv_init_msg_list,
             p_contact_point_rec  => lr_contact_point_rec_type,
             p_phone_rec          => lr_phone_rec_type,
             x_contact_point_id   => ln_contact_point_id,
             x_return_status      => lv_return_status,
             x_msg_count          => ln_msg_count,
             x_msg_data           => lv_msg_data
            );          
                    
  dbms_output.put_line ('API Status: ' || lv_return_status);

  IF (lv_return_status <> 'S')
  THEN
    dbms_output.put_line ('ERROR :' || lv_msg_data);
  END IF;

  dbms_output.put_line ('Create Phone is completed');
  COMMIT;
  
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ('Error:' || SQLERRM);
    ROLLBACK;
END;


Sunday, May 9, 2021 by Team search · 0

Friday, April 2, 2021

API to create a Web contact point in TCA Oracle Apps R12 (HZ_CONTACT_POINT_V2PUB.CREATE_WEB_CONTACT_POINT)

 

 

 

 

 

 

 

In this post, we have given a sample script to create a WEB contact point for a Party in TCA (Trading Community Architecture).

API used: HZ_CONTACT_POINT_V2PUB.CREATE_WEB_CONTACT_POINT

Tested Instance: R12.2.4

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_init_msg_list            VARCHAR2(200)  DEFAULT fnd_api.g_true;
  lv_return_status            VARCHAR2(200);
  ln_msg_count                NUMBER;
  lv_msg_data                 VARCHAR2(2000);
  lv_msg                      VARCHAR2(2000); 
  ln_contact_point_id         NUMBER;
  ln_object_version_number    NUMBER;
  lr_contact_point_rec_type   hz_contact_point_v2pub.contact_point_rec_type;
  lr_web_rec_type             hz_contact_point_v2pub.web_rec_type;  
BEGIN   
  lr_contact_point_rec_type.actual_content_source   
                   := hz_contact_point_v2pub.g_miss_content_source_type;  
  lr_contact_point_rec_type.contact_point_type      := 'WEB';
  lr_contact_point_rec_type.created_by_module       := 'TCA_V2_API';
  lr_contact_point_rec_type.primary_flag            := 'N';
  lr_contact_point_rec_type.owner_table_name        := 'HZ_PARTIES';
  lr_contact_point_rec_type.owner_table_id          := 4168;
  lr_web_rec_type.web_type                          := 'HTTP';
  lr_web_rec_type.url                               := 'www.shareoracleapps.com';
          
  hz_contact_point_v2pub.create_web_contact_point 
           ( p_init_msg_list      => lv_init_msg_list,
             p_contact_point_rec  => lr_contact_point_rec_type,
             p_web_rec            => lr_web_rec_type,
             x_contact_point_id   => ln_contact_point_id,
             x_return_status      => lv_return_status,
             x_msg_count          => ln_msg_count,
             x_msg_data           => lv_msg_data
            );          
					
  dbms_output.put_line ('API Status: ' || lv_return_status);

  IF (lv_return_status <> 'S')
  THEN
    dbms_output.put_line ('ERROR :' || lv_msg_data);
  END IF;

  dbms_output.put_line ('Create Email is completed');
  COMMIT;
  
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ('Error:' || SQLERRM);
    ROLLBACK;
END;

 

Friday, April 2, 2021 by Team search · 0

Saturday, March 20, 2021

API to create a Email contact point in TCA Oracle Apps R12 (HZ_CONTACT_POINT_V2PUB.CREATE_EMAIL_CONTACT_POINT)

 

 

 

 

 

 

 

 

In this post, we have given a sample script to create a EMAIL contact point for a Party in TCA (Trading Community Architecture).

API used: HZ_CONTACT_POINT_V2PUB.CREATE_EMAIL_CONTACT_POINT

Tested Instance: R12.2.4

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_init_msg_list            VARCHAR2(200)  DEFAULT fnd_api.g_true;
  lv_return_status            VARCHAR2(200);
  ln_msg_count                NUMBER;
  lv_msg_data                 VARCHAR2(2000);
  lv_msg                      VARCHAR2(2000); 
  ln_contact_point_id         NUMBER;
  ln_object_version_number    NUMBER;
  lr_contact_point_rec_type   hz_contact_point_v2pub.contact_point_rec_type;
  lr_email_rec_type           hz_contact_point_v2pub.email_rec_type;
BEGIN   
  lr_contact_point_rec_type.actual_content_source   
                   := hz_contact_point_v2pub.g_miss_content_source_type;  
  lr_contact_point_rec_type.contact_point_type      := 'EMAIL';
  lr_contact_point_rec_type.created_by_module       := 'TCA_V2_API';
  lr_contact_point_rec_type.primary_flag            := 'N';
  lr_contact_point_rec_type.owner_table_name        := 'HZ_PARTIES';
  lr_contact_point_rec_type.owner_table_id          := 4168;
  lr_email_rec_type.email_format                    := 'MAILTEXT';
  lr_email_rec_type.email_address                   := 'test@share.com';
          
  hz_contact_point_v2pub.create_email_contact_point 
           ( p_init_msg_list      => lv_init_msg_list,
             p_contact_point_rec  => lr_contact_point_rec_type,
             p_email_rec          => lr_email_rec_type,
             x_contact_point_id   => ln_contact_point_id,
             x_return_status      => lv_return_status,
             x_msg_count          => ln_msg_count,
             x_msg_data           => lv_msg_data
            );
  DBMS_OUTPUT.put_line ('API Status: ' || lv_return_status);

  IF (lv_return_status <> 'S')
  THEN
    DBMS_OUTPUT.put_line ('ERROR :' || lv_msg_data);
  END IF;

  DBMS_OUTPUT.put_line ('Create Email is completed');
  COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error:' || SQLERRM);
      ROLLBACK;
END;

 

Saturday, March 20, 2021 by Team search · 1

Tuesday, January 12, 2021

Query to get active Customer and their Profile Details (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS)

 

 

 

 

 

 

 

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:

  1. HZ_PARTIES
  2. HZ_CUST_ACCOUNTS
  3. HZ_CUST_ACCT_SITES_ALL
  4. HZ_CUSTOMER_PROFILES
  5. HZ_CUST_PROFILE_AMTS
  6. HZ_CUST_SITE_USES_ALL
  7. RA_GROUPING_RULES
  8. AR_STATEMENT_CYCLES
  9. RA_TERMS
  10. AR_COLLECTORS
  11. 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))

Tuesday, January 12, 2021 by Team search · 0

Subscribe to: Posts (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search