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

Associative array in select statement as normal database table

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 ;