[Home] [Help]
2763: IF l_hzpartyrel_sql IS NOT NULL THEN
2764: IF l_party_select_sql IS NULL THEN
2765: l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id,hzcsua.site_use_code from ';
2766: l_party_select_sql := l_party_select_sql || ' hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa, hz_cust_accounts hzca, ';
2767: l_party_select_sql := l_party_select_sql || ' hz_relationships hzpr ';
2768: l_party_where_sql := ' WHERE ' ;
2769: l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'')';
2770: l_party_where_sql := l_party_where_sql || ' AND hzcsua.status = ''A'' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2771: l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND hzpr.subject_id = hzca.party_id ';
2774: l_party_where_sql := l_party_where_sql || ' AND hzpr.status = ''A'' AND ' || l_hzpartyrel_sql;
2775: write_conc_log('IF l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
2776:
2777: ELSE
2778: l_party_select_sql := l_party_select_sql || ', hz_relationships hzpr';
2779: l_party_where_sql := l_party_where_sql || ' hzpr.subject_id = hzca.party_id AND hzpr.start_date <= SYSDATE ' ;
2780: l_party_where_sql := l_party_where_sql || ' AND NVL(hzpr.end_date, SYSDATE) >= SYSDATE ' ;
2781: l_party_where_sql := l_party_where_sql || ' AND hzpr.relationship_code = ''SUBSIDIARY_OFF'' ';
2782: l_party_where_sql := l_party_where_sql || ' AND hzpr.status = ''A'' AND ' || l_hzpartyrel_sql;
2820:
2821: IF l_party_select_sql IS NULL THEN
2822: l_party_select_sql := 'SELECT DISTINCT hzca.party_id, hzca.cust_account_id, hzcsua.cust_acct_site_id,hzcsua.site_use_code ';
2823: l_party_select_sql := l_party_select_sql || ' from hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa, hz_cust_accounts hzca, ';
2824: l_party_select_sql := l_party_select_sql || ' hz_relationships hzpr, hz_party_sites hzps, hz_locations hzloc ';
2825: l_party_where_sql := ' WHERE ' || l_hzlocations_sql;
2826: l_party_where_sql := l_party_where_sql || ' hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') and hzcsua.status = ''A'' ';
2827: l_party_where_sql := l_party_where_sql || ' AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
2828: l_party_where_sql := l_party_where_sql || ' AND hzcasa.cust_account_id = hzca.cust_account_id AND ' ;
3021: l_index NUMBER;
3022:
3023: CURSOR c_get_object_ids IS
3024: SELECT subject_id
3025: FROM hz_relationships
3026: WHERE relationship_code = fnd_profile.VALUE('AMS_PARTY_RELATIONS_TYPE')
3027: AND subject_type = 'ORGANIZATION'
3028: AND subject_table_name = 'HZ_PARTIES'
3029: AND object_type = 'ORGANIZATION'
3312: /* yzhao: 08/07/2002 fix performance issue. Use index on relationship_type */
3313: CURSOR c_get_all_bgroots(p_relationship_code VARCHAR2, p_relationship_type VARCHAR2, p_direction_code VARCHAR2) IS
3314: -- get all root object_ids
3315: SELECT distinct r1.object_id
3316: FROM hz_relationships r1
3317: WHERE r1.relationship_type = p_relationship_type
3318: AND r1.relationship_code = p_relationship_code
3319: AND r1.subject_type = 'ORGANIZATION'
3320: AND r1.subject_table_name = 'HZ_PARTIES'
3327: */
3328: AND r1.directional_flag = NVL(p_direction_code, r1.directional_flag)
3329: AND NOT EXISTS
3330: (SELECT 1
3331: FROM hz_relationships r2
3332: WHERE r1.object_id = r2.subject_id
3333: AND r2.relationship_type = p_relationship_type
3334: AND r2.relationship_code = p_relationship_code
3335: AND r2.subject_type = 'ORGANIZATION'