[Home] [Help]
96: CURSOR l_cust_cur( p_customer_id IN NUMBER )
97: IS
98: SELECT hp.party_name,
99: hca.account_number
100: FROM hz_parties hp,
101: hz_cust_accounts hca
102: WHERE hca.party_id = hp.party_id
103: AND hca.cust_account_id = p_customer_id;
104:
839: HZ_CUST_SITE_USES_ALL HCSU,
840: HZ_CUST_ACCT_SITES_ALL HCAS,
841: HZ_PARTY_SITES HPS,
842: HZ_CUST_ACCOUNTS HCA,
843: HZ_PARTIES HP,
844: WSH_LOCATIONS WL1
845: WHERE
846: WL1.wsh_location_id = p_loc_id AND
847: HCA.CUST_ACCOUNT_ID = p_cust_id AND --bugfix 3842898
917: -- removed the nvl from org_id k proj
918:
919: CURSOR c_cust_info_cur(p_customer_id IN NUMBER) IS
920: SELECT HP.PARTY_NAME, HP.PARTY_NUMBER
921: FROM HZ_PARTIES HP,
922: HZ_CUST_ACCOUNTS HCA
923: WHERE HP.PARTY_ID = HCA.PARTY_ID
924: AND HCA.CUST_ACCOUNT_ID = p_customer_id;
925:
1194: l_debug_on BOOLEAN;
1195: --
1196: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1197: || 'GET_NAME_NUMBER';
1198: l_per_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
1199: l_per_middle_name HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
1200: l_per_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
1201: l_owner_table_id NUMBER;
1202:
1195: --
1196: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1197: || 'GET_NAME_NUMBER';
1198: l_per_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
1199: l_per_middle_name HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
1200: l_per_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
1201: l_owner_table_id NUMBER;
1202:
1203: cursor l_ship_to_site_contact_csr(p_contact_id IN NUMBER) is
1196: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1197: || 'GET_NAME_NUMBER';
1198: l_per_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
1199: l_per_middle_name HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
1200: l_per_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
1201: l_owner_table_id NUMBER;
1202:
1203: cursor l_ship_to_site_contact_csr(p_contact_id IN NUMBER) is
1204: SELECT PER_CONTACT.PERSON_FIRST_NAME,
1209: from HZ_CUST_ACCOUNT_ROLES HCAR,
1210: HZ_RELATIONSHIPS HREL,
1211: HZ_ORG_CONTACTS HOC,
1212: HZ_CONTACT_POINTS PHONE_CONTACT,
1213: HZ_PARTIES PER_CONTACT
1214: WHERE HCAR.CUST_ACCOUNT_ROLE_ID = p_contact_id
1215: AND HREL.PARTY_ID = HCAR.PARTY_ID
1216: AND HCAR.ROLE_TYPE = 'CONTACT'
1217: AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
1214: WHERE HCAR.CUST_ACCOUNT_ROLE_ID = p_contact_id
1215: AND HREL.PARTY_ID = HCAR.PARTY_ID
1216: AND HCAR.ROLE_TYPE = 'CONTACT'
1217: AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
1218: AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1219: AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1220: AND HREL.SUBJECT_TYPE = 'PERSON'
1221: AND HREL.DIRECTIONAL_FLAG = 'F'
1222: AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
1215: AND HREL.PARTY_ID = HCAR.PARTY_ID
1216: AND HCAR.ROLE_TYPE = 'CONTACT'
1217: AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
1218: AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1219: AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1220: AND HREL.SUBJECT_TYPE = 'PERSON'
1221: AND HREL.DIRECTIONAL_FLAG = 'F'
1222: AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
1223: AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
1219: AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1220: AND HREL.SUBJECT_TYPE = 'PERSON'
1221: AND HREL.DIRECTIONAL_FLAG = 'F'
1222: AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
1223: AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
1224: AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
1225: AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
1226: AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
1227: AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y';
1228:
1229: cursor l_ship_to_site_ph_csr(p_owner_tbl_id IN NUMBER) is
1230: SELECT RAW_PHONE_NUMBER
1231: FROM HZ_CONTACT_POINTS
1232: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
1233: AND OWNER_TABLE_ID = p_owner_tbl_id
1234: AND CONTACT_POINT_TYPE = 'PHONE'
1235: AND PHONE_LINE_TYPE = 'GEN';
1236: