[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
11048: c.party_id,
11049: c.location_id
11050: FROM hz_cust_acct_sites_all a,
11051: hz_cust_site_uses_all b,
11052: hz_party_sites c
11053: WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
11054: AND b.site_use_code = 'SHIP_TO'
11055: AND a.party_site_id = c.party_site_id
11056: AND a.cust_account_id = cp_cust_acct_id
15772: c.party_id,
15773: c.location_id
15774: FROM hz_cust_acct_sites_all a,
15775: hz_cust_site_uses_all b,
15776: hz_party_sites c,
15777: hz_parties d
15778: WHERE
15779: a.cust_acct_site_id = b.cust_acct_site_id
15780: AND b.site_use_code = 'BILL_TO'
15790: c.party_id,
15791: c.location_id
15792: FROM hz_cust_acct_sites_all a,
15793: hz_cust_site_uses_all b,
15794: hz_party_sites c,
15795: hz_parties d
15796: WHERE
15797: a.cust_acct_site_id = b.cust_acct_site_id
15798: AND b.site_use_code = 'SHIP_TO'
15838: -- Cursor to get the corresponding hz_location id for the install location id
15839: CURSOR l_get_location_id(cp_party_site_id IN NUMBER) IS
15840: -- SECHAWLA 12-DEC-05 4690074
15841: SELECT hzp.location_id
15842: FROM HZ_PARTY_SITES hzp, hz_party_site_uses hsu
15843: WHERE hsu.PARTY_SITE_USE_ID = cp_party_site_id
15844: AND hzp.party_Site_id = hsu.party_site_id;
15845:
15846: -- Cursor to get the ship_to_site_use_id corresponding to the install_location_id of the asset
15847: CURSOR l_get_shiptositeid(cp_cust_acct_id IN NUMBER, cp_inst_loc_id IN NUMBER,cp_loc_id IN NUMBER) IS
15848: SELECT b.site_use_id, c.party_site_id, c.party_id, c.location_id
15849: FROM hz_cust_acct_sites_all a,
15850: hz_cust_site_uses_all b,
15851: hz_party_sites c
15852: WHERE a.cust_acct_site_id = b.cust_acct_site_id
15853: AND b.site_use_code = 'SHIP_TO'
15854: AND a.party_site_id = c.party_site_id
15855: AND a.cust_account_id = cp_cust_acct_id