[Home] [Help]
86:
87: pg_temp_site_use_id NUMBER;
88: pg_temp_contact_phone VARCHAR2(360);
89: pg_temp_contacts VARCHAR2(360);
90: pg_temp_contact_name HZ_PARTIES.PARTY_NAME%TYPE;
91:
92: p_not_implemented_exp EXCEPTION;
93:
94: TYPE aging_mfar_tab IS TABLE OF ar_aging_mfar_extract%ROWTYPE INDEX BY BINARY_INTEGER;
372: into pg_temp_contact_name,
373: pg_temp_contact_phone,
374: pg_temp_contacts
375: from hz_cust_account_roles acct_role,
376: hz_parties party,
377: hz_relationships rel,
378: hz_contact_points cont_point,
379: hz_cust_account_roles car,
380: hz_cust_site_uses site_uses
380: hz_cust_site_uses site_uses
381: where site_uses.site_use_id = p_site_use_id
382: and site_uses.cust_acct_site_id = acct_role.cust_acct_site_id(+)
383: and acct_role.party_id = rel.party_id(+)
384: and rel.subject_table_name(+) = 'HZ_PARTIES'
385: and rel.object_table_name(+) = 'HZ_PARTIES'
386: and rel.directional_flag(+) = 'F'
387: and acct_role.role_type = 'CONTACT'
388: and rel.subject_id = party.party_id(+)
381: where site_uses.site_use_id = p_site_use_id
382: and site_uses.cust_acct_site_id = acct_role.cust_acct_site_id(+)
383: and acct_role.party_id = rel.party_id(+)
384: and rel.subject_table_name(+) = 'HZ_PARTIES'
385: and rel.object_table_name(+) = 'HZ_PARTIES'
386: and rel.directional_flag(+) = 'F'
387: and acct_role.role_type = 'CONTACT'
388: and rel.subject_id = party.party_id(+)
389: and acct_role.cust_account_role_id = car.cust_account_role_id(+)
387: and acct_role.role_type = 'CONTACT'
388: and rel.subject_id = party.party_id(+)
389: and acct_role.cust_account_role_id = car.cust_account_role_id(+)
390: and car.party_id = cont_point.owner_table_id(+)
391: and cont_point.owner_table_name(+) = 'HZ_PARTIES'
392: and NVL(cont_point.contact_point_type(+),'N') not in ('EDI','EMAIL','WEB')
393: and nvl( nvl(cont_point.phone_line_type(+),
394: cont_point.contact_point_type(+)), 'GEN') = 'GEN'
395: and nvl(acct_role.status,'A') = 'A'
1369: IF pg_in_customer_name_low IS NOT NULL OR
1370: pg_in_customer_name_high IS NOT NULL THEN
1371:
1372: l_from_clause := l_from_clause || ',
1373: hz_parties party ';
1374: l_where_clause := l_where_clause || '
1375: and cust_acct.party_id = party.party_id ';
1376:
1377: IF pg_in_customer_name_low IS NOT NULL THEN
2350: arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)
2351: invoice_type '|| l_rep_specific_cols || '
2352: from '||l_inv_act_sub_query|| '
2353: hz_cust_accounts cust_acct,
2354: hz_parties party,
2355: hz_cust_site_uses site,
2356: hz_cust_acct_sites acct_site,
2357: hz_party_sites party_site,
2358: hz_locations loc,
2436: bal_segment_value,
2437: '''|| pg_payment_meaning ||''' sort_field2,
2438: '''|| pg_payment_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
2439: from hz_cust_accounts cust_acct,
2440: hz_parties party,
2441: ar_aging_payment_schedules aging,
2442: ar_payment_schedules ps,
2443: hz_cust_site_uses site,
2444: hz_cust_acct_sites acct_site,
2547: bal_segment_value,
2548: '''|| pg_risk_meaning ||''' sort_field2,
2549: '''|| pg_risk_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
2550: from hz_cust_accounts cust_acct,
2551: hz_parties party,
2552: ar_aging_payment_schedules aging,
2553: ar_payment_schedules ps,
2554: hz_cust_site_uses site,
2555: hz_cust_acct_sites acct_site,
2657: bal_segment_value,
2658: arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type
2659: '|| l_rep_specific_cols ||'
2660: from hz_cust_accounts cust_acct,
2661: hz_parties party,
2662: ar_aging_payment_schedules aging,
2663: ar_payment_schedules ps,
2664: hz_cust_site_uses site,
2665: hz_cust_acct_sites acct_site,