Query to get Customer Details in Oracle Fusion

select party.party_id

      ,party.party_number

      ,party.party_name customer_name

      ,(select party_nm.attribute1 from HZ_PERSON_PROFILES party_nm where party_nm.party_id = party.party_id) person_name_global

      ,party.party_type 

      ,party.orig_system_reference party_orig_system_reference

      ,party.sales_account_id

      ,cust_account.cust_account_id

      ,cust_account.account_number 

      ,cust_account.customer_class_code

      ,cust_account.orig_system_reference cust_orig_system_reference

      ,cust_accts.cust_acct_site_id

      ,cust_accts.party_site_id

      ,cust_accts.orig_system_reference cust_site_orig_system_reference

      ,cust_accts.bill_to_flag

      ,cust_accts.ship_to_flag

      ,party_site.location_id

      ,party_site.party_site_number

      ,party_site.party_site_name

      ,party_site.orig_system_reference party_site_orig_system_reference

      ,site_use.site_use_id

      ,site_use.site_use_code

      ,site_use.primary_flag

      ,site_use.location

      ,site_use.orig_system_reference site_use_orig_system_reference

      ,ref_accts.bu_id

      ,ref_accts.ledger_id

      ,ledger.name ledger_name

      ,ref_accts.rev_ccid

      ,ref_accts.rec_ccid

      ,rev_gcc.segment1||'-'||rev_gcc.segment2||'-'||rev_gcc.segment3||'-'||rev_gcc.segment4||'-'||rev_gcc.segment5||'-'||rev_gcc.segment6||'-'||rev_gcc.segment7||'-'||rev_gcc.segment8 revenue_account

      ,rec_gcc.segment1||'-'||rec_gcc.segment2||'-'||rec_gcc.segment3||'-'||rec_gcc.segment4||'-'||rec_gcc.segment5||'-'||rec_gcc.segment6||'-'||rec_gcc.segment7||'-'||rec_gcc.segment8 receivable_account

from hz_parties party

    ,hz_cust_accounts cust_account

    ,hz_cust_acct_sites_all cust_accts

    ,hz_party_sites party_site

    ,hz_cust_site_uses_all site_use

    ,ar_ref_accounts_all ref_accts

    ,gl_code_combinations rev_gcc

    ,gl_code_combinations rec_gcc

    ,gl_ledgers ledger

where party_name = 'AYMAN ABDULMOHSEN AL-OJAIMI'

  and party.party_id = cust_account.party_id

  and cust_account.cust_account_id = cust_accts.cust_account_id

  and cust_accts.party_site_id = party_site.party_site_id

  and cust_accts.cust_acct_site_id = site_use.cust_acct_site_id

  --AND site_use.primary_flag = 'Y'

  AND ref_accts.source_ref_table(+) = 'HZ_CUST_SITE_USES_ALL'

  and ref_accts.source_ref_account_id(+) = site_use.site_use_id

  and ref_accts.rev_ccid = rev_gcc.code_combination_id(+)

  and ref_accts.rec_ccid = rec_gcc.code_combination_id(+)

  and ref_accts.ledger_id = ledger.ledger_id(+)

Script to update Supplier pay_group_lookup_code in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.pay_group_lookup_code := 'Employee';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Type(ap_suppliers.vendor_type_lookup_code) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.vendor_type_lookup_code := 'LOCAL';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Tax Payer ID(AP_SUPPLIERS.NUM_1099) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.jgzz_fiscal_code := 'X123456789X';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index     => v_index

                ,p_encoded       => 'F'

,p_data          => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END