[Home] [Help]
3100: AND hcsu.status = 'A';
3101:
3102: CURSOR get_party_acc_id (cv_party_site_id IN NUMBER) IS
3103: SELECT hp.party_id , hc.cust_account_id
3104: FROM hz_party_sites hps
3105: , hz_parties hp
3106: , hz_cust_accounts hc
3107: WHERE hps.party_id = hp.party_id
3108: AND hp.party_id = hc.party_id (+)
5154: IS
5155: SELECT hps.party_id , hcsu.site_use_id
5156: FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
5157: , hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
5158: , hz_party_sites hps
5159: , hz_cust_accounts hc
5160: WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
5161: AND hcs.party_site_id = hps.party_site_id
5162: AND hps.party_id = hc.party_id
5171: /*
5172: SELECT hcsu.site_use_id , hp.party_id , hc.cust_account_id
5173: FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
5174: , hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
5175: , hz_party_sites hps
5176: , hz_parties hp
5177: , hz_cust_accounts hc
5178: WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
5179: AND hcs.party_site_id = hps.party_site_id
5187: CURSOR get_party_site_acc_id (pc_party_site_id IN NUMBER, pc_site_use VARCHAR2) IS
5188: SELECT hc.cust_account_id , hps.party_id , hcsu.site_use_id
5189: FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
5190: , hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
5191: , hz_party_sites hps
5192: , hz_cust_accounts hc
5193: WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
5194: AND hcs.party_site_id = hps.party_site_id
5195: AND hps.party_id = hc.party_id
5540: , hcas.party_site_id
5541: , hp.party_id
5542: FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5543: hz_cust_site_uses hcsu, -- hz_cust_site_uses_all hcsu,
5544: hz_party_sites hps,
5545: hz_parties hp
5546: WHERE hcas.party_site_id = hps.party_site_id
5547: AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5548: AND hps.party_id = hp.party_id
5555: , pc_site_use VARCHAR2 ) IS
5556: SELECT hcas.cust_acct_site_id
5557: FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5558: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5559: hz_party_sites hps
5560: WHERE hcas.party_site_id = hps.party_site_id
5561: AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5562: AND hcas.cust_account_id = pc_account_id
5563: AND hcsu.location = pc_location
5569: , hp.party_id
5570: , hp.party_name
5571: FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5572: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5573: hz_party_sites hps,
5574: hz_parties hp
5575: WHERE hcas.party_site_id = hps.party_site_id
5576: AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5577: AND hps.party_id = hp.party_id
5581: SELECT hcas.cust_account_id
5582: , hp.party_id
5583: , hp.party_name
5584: FROM hz_cust_acct_sites_all hcas,
5585: hz_party_sites hps,
5586: hz_parties hp
5587: WHERE hcas.party_site_id = hps.party_site_id
5588: AND hps.party_id = hp.party_id
5589: AND hcas.cust_acct_site_id = pc_site_id;
5591: CURSOR get_party_site_id ( pc_site_use_id NUMBER ) IS
5592: SELECT hps.party_site_id
5593: FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5594: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5595: hz_party_sites hps
5596: WHERE hcas.party_site_id = hps.party_site_id
5597: AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5598: AND hcsu.cust_acct_site_id = pc_site_use_id
5599: UNION
5599: UNION
5600: SELECT hps.party_site_id
5601: FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5602: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5603: hz_party_sites hps
5604: WHERE hcas.party_site_id = hps.party_site_id
5605: AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5606: AND hcsu.site_use_id = pc_site_use_id;
5607:
5612: hp.party_name
5613: FROM hz_cust_accounts hca,
5614: hz_parties hp,
5615: hz_cust_acct_sites_all hcas,
5616: hz_party_sites hps
5617: WHERE hcas.party_site_id = hps.party_site_id
5618: AND hcas.cust_account_id = hca.cust_account_id
5619: AND hp.party_id = hca.party_id
5620: AND hps.party_site_id = pc_address_id;
5629: FROM hz_cust_accounts hca,
5630: hz_parties hp,
5631: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5632: hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5633: hz_party_sites hps,
5634: hz_locations hl
5635: WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5636: AND hcas.party_site_id = hps.party_site_id
5637: AND hcsu.status = 'A'
5657: FROM hz_cust_accounts hca,
5658: hz_parties hp,
5659: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5660: hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5661: hz_party_sites hps,
5662: hz_locations hl
5663: WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5664: AND hcas.party_site_id = hps.party_site_id
5665: AND hcsu.status = 'A'
5687: FROM hz_cust_accounts hca,
5688: hz_parties hp,
5689: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5690: hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5691: hz_party_sites hps
5692: WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5693: AND hcas.party_site_id = hps.party_site_id
5694: AND hcsu.status = 'A'
5695: AND hps.status = 'A'
5716: FROM hz_cust_accounts hca,
5717: hz_parties hp,
5718: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5719: hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5720: hz_party_sites hps
5721: WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5722: AND hcas.party_site_id = hps.party_site_id
5723: AND hcsu.status = 'A'
5724: AND hps.status = 'A'
5743: FROM hz_cust_accounts hca,
5744: hz_parties hp,
5745: hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
5746: hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
5747: hz_party_sites hps,
5748: hz_locations hl
5749: WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
5750: AND hcas.party_site_id = hps.party_site_id
5751: AND hcsu.status = 'A'
5771: , site.party_site_id
5772: , party.party_id
5773: , party.party_name
5774: FROM hz_parties party
5775: , hz_party_sites site
5776: , hz_locations loc
5777: , hz_party_site_uses use
5778: WHERE site.location_id = loc.location_id
5779: AND party.party_id = site.party_id
6424: hzmps.party_site_id,
6425: score ,
6426: hzps.creation_date
6427: FROM hz_matched_party_sites_gt hzmps,
6428: hz_party_sites hzps
6429: WHERE search_context_id = pc_search_id
6430: AND hzps.party_site_id = hzmps.party_site_id
6431: AND hzps.party_id = hzmps.party_id
6432: ORDER BY score desc, hzps.creation_date desc;
7265: , hp.party_id
7266: , hp.party_name
7267: FROM hz_cust_acct_sites hcas,
7268: hz_cust_site_uses hcsu,
7269: hz_party_sites hps,
7270: hz_parties hp
7271: WHERE hp.party_id = hcas.cust_account_id
7272: AND hp.party_id = hps.party_id
7273: AND hps.party_site_id = hcas.party_site_id
7292: , hp.party_id
7293: , hp.party_name
7294: FROM hz_cust_acct_sites hcas,
7295: hz_cust_site_uses hcsu,
7296: hz_party_sites hps,
7297: hz_parties hp
7298: WHERE hp.party_id = hcas.cust_account_id
7299: AND hp.party_id = hps.party_id
7300: AND hps.party_site_id = hcas.party_site_id
7310: , hp.party_id
7311: , hp.party_name
7312: FROM hz_cust_acct_sites hcas,
7313: hz_cust_site_uses hcsu,
7314: hz_party_sites hps,
7315: hz_parties hp
7316: WHERE hp.party_id = hcas.cust_account_id
7317: AND hp.party_id = hps.party_id
7318: AND hps.party_site_id = hcas.party_site_id
7331: , hp.party_id
7332: , hp.party_name
7333: FROM hz_cust_acct_sites hcas,
7334: hz_cust_site_uses hcsu,
7335: hz_party_sites hps,
7336: hz_parties hp,
7337: hz_cust_site_uses shcsu
7338: WHERE hp.party_id = hcas.cust_account_id
7339: AND hp.party_id = hps.party_id
7353: , hp.party_id
7354: , hp.party_name
7355: FROM hz_cust_acct_sites hcas,
7356: hz_cust_site_uses hcsu,
7357: hz_party_sites hps,
7358: hz_parties hp,
7359: hz_cust_acct_sites shcas,
7360: hz_cust_site_uses shcsu
7361: WHERE hp.party_id = hcas.cust_account_id