[Home] [Help]
1686: WHERE id1 = p_contact_id;
1687:
1688: CURSOR l_customer_csr(l_billto_siteuse_id IN VARCHAR2) IS
1689: SELECT CA.CUST_ACCOUNT_ID
1690: FROM HZ_CUST_ACCT_SITES_ALL CA, HZ_CUST_SITE_USES_ALL CS
1691: WHERE CS.SITE_USE_ID = l_billto_siteuse_id
1692: AND CS.SITE_USE_CODE = 'BILL_TO'
1693: AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
1694:
2945: SELECT c.party_id,
2946: a.party_site_id,
2947: a.cust_account_id,
2948: a.org_id
2949: FROM hz_cust_acct_sites_all a,
2950: hz_cust_site_uses_all b,
2951: hz_party_sites c
2952: WHERE a.cust_acct_site_id = b.cust_acct_site_id
2953: AND c.party_site_id = a.party_site_id
3054: CURSOR cust_acct_csr(p_site_use_id NUMBER) IS
3055: SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID
3056: FROM
3057: HZ_CUST_SITE_USES_ALL S_SHIP,
3058: HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
3059: WHERE S_SHIP.SITE_USE_ID = p_site_use_id
3060: AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id
3061: ;
3062:
3765: IS
3766: SELECT prt.party_name
3767: FROM oe_hold_sources_all ohs,
3768: HZ_PARTIES prt,
3769: HZ_PARTY_SITES PS, HZ_CUST_ACCT_SITES_ALL CA, HZ_CUST_SITE_USES_ALL CS,
3770: okc_k_headers_all_b rl
3771: WHERE ohs.hold_entity_code = 'C'
3772: AND ohs.released_flag = 'N'
3773: AND ohs.org_id = okc_context.get_okc_org_id
3796: FROM oe_hold_sources_all ohs,
3797: oe_hold_definitions ohd,
3798: HZ_PARTIES prt,
3799: HZ_PARTY_SITES PS,
3800: HZ_CUST_ACCT_SITES_ALL CA,
3801: HZ_CUST_SITE_USES_ALL CS,
3802: okc_k_headers_all_b rl
3803: WHERE ohs.hold_entity_code = 'C'
3804: AND ohs.released_flag = 'N'
3828: FROM oe_hold_sources_all ohs,
3829: oe_hold_definitions ohd,
3830: HZ_PARTIES prt,
3831: HZ_PARTY_SITES PS,
3832: HZ_CUST_ACCT_SITES_ALL CA,
3833: HZ_CUST_SITE_USES_ALL CS,
3834: okc_k_headers_all_b rl
3835: WHERE ohs.hold_entity_code = 'B'
3836: AND ohs.released_flag = 'N'
3942: ******/
3943:
3944: CURSOR check_site (l_cust_acct_site_id NUMBER) IS
3945: SELECT CA.STATUS STATUS
3946: FROM HZ_CUST_ACCT_SITES_ALL CA
3947: WHERE CA.CUST_ACCT_SITE_ID = l_cust_acct_site_id;
3948:
3949: /******
3950: -- checks bill to address for top lines.
3969: ca.status site_status,
3970: 'BILL_TO' use_code
3971: FROM okc_k_lines_b rl,
3972: hz_cust_site_uses_all cs,
3973: hz_cust_acct_sites_all ca
3974: WHERE rl.dnz_chr_id = p_chr_id
3975: AND rl.chr_id = p_chr_id
3976: AND rl.cle_id IS NULL
3977: AND rl.lse_id IN (1,12, 14, 19, 46)
3989: ca.status site_status,
3990: 'SHIP_TO' use_code
3991: FROM okc_k_lines_b rl,
3992: hz_cust_site_uses_all cs,
3993: hz_cust_acct_sites_all ca
3994: WHERE rl.dnz_chr_id = p_chr_id
3995: AND rl.chr_id = p_chr_id
3996: AND rl.cle_id IS NULL
3997: AND rl.lse_id IN (1,12, 14, 19, 46)
4005:
4006: TYPE chr150_tbl_type IS TABLE OF okc_k_lines_b.line_number%TYPE INDEX BY BINARY_INTEGER;
4007: TYPE chr7_tbl_type IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
4008: TYPE chr1a_tbl_type IS TABLE OF hz_cust_site_uses_all.status%TYPE INDEX BY BINARY_INTEGER;
4009: TYPE chr1b_tbl_type IS TABLE OF hz_cust_acct_sites_all.status%TYPE INDEX BY BINARY_INTEGER;
4010: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4011:
4012: l_id_tbl num_tbl_type;
4013: l_line_number_tbl chr150_tbl_type;
4929: CURSOR cust_account_csr(p_site_use_id VARCHAR2, p_site_use_code VARCHAR2) IS
4930: SELECT accts.party_id,
4931: sites.cust_account_id
4932: FROM hz_cust_site_uses_all site_uses,
4933: hz_cust_acct_sites_all sites,
4934: hz_cust_accounts accts
4935: WHERE site_uses.site_use_id = p_site_use_id
4936: AND site_uses.site_use_code = p_site_use_code
4937: AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
6706:
6707: -- gets ship to customber account id
6708: CURSOR get_ship_to_acct(l_site_use_id NUMBER) IS
6709: SELECT CA.cust_account_id
6710: FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
6711: WHERE CS.SITE_USE_ID = l_site_use_id
6712: AND CS.site_use_code = 'SHIP_TO'
6713: AND CS.cust_acct_site_id = CA.cust_acct_site_id;
6714: