How to Use Associative array in select statement as a normal database table

Introduction

This Post illustrates steps required to Associative array in select statement as a normal database table in Oracle EBS R12.

Step 1

First Create Associative Array Type

TYPE  assoc_array_type  IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;

if required We can use as Cursor parameter, but this is optional

function or procedure_name (Parameter1   datatype)

DECLARE  SECTION

assoc_var  assoc_array_type;

CURSOR  invoice_cur(cur_para_assocv  assoc_array_type) IS

SELECT DISTINCT

trx_head.trx_number invoice_number,

cust_acct.cust_account_id,

SUBSTR(cust_acct.account_number,7,6) customer_number,

cust_acct.account_name,

loc.address1,

loc.address2,

loc.address3,

loc.address4,

loc.city,

loc.county,

loc.state,

loc.postal_code,

loc.country,

term.NAME payment_term,

trx_head.term_id term_id,

trx_head.cust_trx_type_id trx_type_id,

trx_head.bill_to_site_use_id,

trx_head.bill_to_contact_id,

party_sites.party_id,

cust_acct.account_number

FROM

ra_customer_trx           trx_head,

ra_cust_Trx_types         trx_types,

hz_locations              loc,

ra_terms                  term,

hz_cust_accounts          cust_acct,

hz_party_sites            party_sites,

hz_cust_acct_sites        cust_acct_sites,

hz_cust_site_uses         cust_site_uses

WHERE cust_acct.cust_account_id = trx_head.bill_to_customer_id

AND term.term_id = trx_head.term_id

AND trx_head.cust_trx_type_id = trx_types.cust_trx_type_id

AND loc.location_id = party_sites.location_id

AND party_sites.party_site_id = cust_acct_sites.party_site_id

AND cust_acct_sites.cust_acct_site_id = cust_site_uses.cust_acct_site_id

AND cust_acct_sites.cust_account_id = cust_acct.cust_account_id

AND cust_site_uses.site_use_id = trx_head.bill_to_site_use_id

AND term.due_cutoff_day IS NULL

AND trx_head.complete_flag = ‘Y’

AND trx_head.printing_option = ‘PRI’

AND trx_head.bill_to_customer_id IS NOT NULL

AND cust_acct_sites.attribute9 IS NOT NULL

AND cust_acct_sites.attribute9 IN (SELECT * FROM TABLE(cur_para_assocv))

— The above show the associative array used as normal Table.

BEGIN  SECTION………………… IF SOME STATEMENTS REQUIRED YOU CAN USE  HERE

if  Parameter1 =’REST ALL’ then

select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE(‘ORG_ID’) and (ATTRIBUTE9 NOT LIKE ‘XXX’ AND ATTRIBUTE9 NOT LIKE ‘YYY’ AND ATTRIBUTE9 NOT LIKE ‘ZZZ’ ) ;

elsif  Parameter1 =’XXX’   then

select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE(‘ORG_ID’) and ATTRIBUTE9  LIKE ‘XXX%’ ;

elsif  Parameter1 =’YYY’ THEN

select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE(‘ORG_ID’) and ATTRIBUTE9  LIKE ‘YYY%’ ;

elsif  Parameter1=’ZZZ’ then

select distinct attribute9 BULK COLLECT INTO assoc_var from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE(‘ORG_ID’) and ATTRIBUTE9  LIKE ‘ZZZ%’ ;

else

select distinct attribute9 BULK COLLECT INTO assoc_var  from apps.hz_cust_acct_sites_all where org_id=apps.FND_PROFILE.VALUE(‘ORG_ID’);

end if;

FOR invoice_rec IN invoice_cur (assoc_var )

LOOP

………………………………..SOME STATEMENTS HERE

END LOOP;

END ;

 

Summary

This Post described  Associative array in select statement as a normal database table in Oracle EBS R12.

 

Got any queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.