[Home] [Help]
90:
91: --Cursor to get the corresponding hz_location id for the install location id
92: CURSOR l_get_location_id(cp_party_site_id IN NUMBER) IS
93: SELECT hzp.location_id
94: FROM HZ_PARTY_SITES HZP
95: WHERE HZP.PARTY_SITE_ID = cp_party_site_id;
96:
97: --Cursor to get the corresponding party_site_id FOR a location id
98: CURSOR l_get_party_site_id(cp_location_id IN NUMBER) IS
96:
97: --Cursor to get the corresponding party_site_id FOR a location id
98: CURSOR l_get_party_site_id(cp_location_id IN NUMBER) IS
99: SELECT HZP.PARTY_SITE_ID
100: FROM HZ_PARTY_SITES HZP,
101: HZ_PARTY_SITE_USES HZU
102: WHERE HZP.LOCATION_ID = cp_location_id
103: AND HZP.party_site_id = HZU.PARTY_SITE_ID
104: AND HZU.SITE_USE_TYPE = 'INSTALL_AT' ;
108: SELECT /*a.CUST_ACCT_SITE_ID */
109: b.site_use_id
110: FROM hz_cust_acct_sites_all a,
111: hz_cust_site_uses_all b,
112: hz_party_sites c
113: WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
114: AND b.site_use_code = 'SHIP_TO'
115: AND a.party_site_id = c.party_site_id
116: AND a.cust_account_id = cp_cust_acct_id
210: 'l_get_instlocid%FOUND');
211:
212: END IF;
213: --Check the source of the install location id
214: IF l_inst_loc_type_code = 'HZ_PARTY_SITES' THEN
215: -- get hz location id
216: OPEN l_get_location_id(l_inst_loc_id);
217: FETCH l_get_location_id INTO l_loc_id;
218: CLOSE l_get_location_id;
3102: c.party_id,
3103: c.location_id
3104: FROM hz_cust_acct_sites_all a,
3105: hz_cust_site_uses_all b,
3106: hz_party_sites c
3107: WHERE
3108: a.cust_acct_site_id = b.cust_acct_site_id
3109: AND b.site_use_code = 'SHIP_TO'
3110: AND b.site_use_id = cp_site_use_id -- asaanka changed for bug # 6612165
3119: c.location_id,
3120: b.site_use_id
3121: FROM hz_cust_acct_sites_all a,
3122: hz_cust_site_uses_all b,
3123: hz_party_sites c
3124: WHERE
3125: a.cust_acct_site_id = b.cust_acct_site_id
3126: AND b.site_use_code = 'BILL_TO'
3127: AND b.site_use_id = (SELECT DECODE(okcl.bill_to_site_use_id, null, okch.bill_to_site_use_id, okcl.bill_to_site_use_id)
3139: c.location_id,
3140: b.site_use_id
3141: FROM hz_cust_acct_sites_all a,
3142: hz_cust_site_uses_all b,
3143: hz_party_sites c
3144: WHERE
3145: a.cust_acct_site_id = b.cust_acct_site_id
3146: AND b.site_use_code = 'BILL_TO'
3147: AND b.site_use_id = (SELECT bill_to_site_use_id
11083: c.party_id,
11084: c.location_id
11085: FROM hz_cust_acct_sites_all a,
11086: hz_cust_site_uses_all b,
11087: hz_party_sites c
11088: WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
11089: AND b.site_use_code = 'SHIP_TO'
11090: AND a.party_site_id = c.party_site_id
11091: AND a.cust_account_id = cp_cust_acct_id
15844: c.party_id,
15845: c.location_id
15846: FROM hz_cust_acct_sites_all a,
15847: hz_cust_site_uses_all b,
15848: hz_party_sites c,
15849: hz_parties d
15850: WHERE
15851: a.cust_acct_site_id = b.cust_acct_site_id
15852: AND b.site_use_code = 'BILL_TO'
15862: c.party_id,
15863: c.location_id
15864: FROM hz_cust_acct_sites_all a,
15865: hz_cust_site_uses_all b,
15866: hz_party_sites c,
15867: hz_parties d
15868: WHERE
15869: a.cust_acct_site_id = b.cust_acct_site_id
15870: AND b.site_use_code = 'SHIP_TO'
15910: -- Cursor to get the corresponding hz_location id for the install location id
15911: CURSOR l_get_location_id(cp_party_site_id IN NUMBER) IS
15912: -- SECHAWLA 12-DEC-05 4690074
15913: SELECT hzp.location_id
15914: FROM HZ_PARTY_SITES hzp, hz_party_site_uses hsu
15915: WHERE hsu.PARTY_SITE_USE_ID = cp_party_site_id
15916: AND hzp.party_Site_id = hsu.party_site_id;
15917:
15918: -- Cursor to get the ship_to_site_use_id corresponding to the install_location_id of the asset
15919: CURSOR l_get_shiptositeid(cp_cust_acct_id IN NUMBER, cp_inst_loc_id IN NUMBER,cp_loc_id IN NUMBER) IS
15920: SELECT b.site_use_id, c.party_site_id, c.party_id, c.location_id
15921: FROM hz_cust_acct_sites_all a,
15922: hz_cust_site_uses_all b,
15923: hz_party_sites c
15924: WHERE a.cust_acct_site_id = b.cust_acct_site_id
15925: AND b.site_use_code = 'SHIP_TO'
15926: AND a.party_site_id = c.party_site_id
15927: AND a.cust_account_id = cp_cust_acct_id