[Home] [Help]
131:
132: --Cursor to get NA id and TGA id for specified party and terr group: Single Row
133: CURSOR c_get_all_ids(l_party_id NUMBER, l_party_site_id NUMBER, l_tg_id NUMBER) IS
134: SELECT na.named_account_id, tga.terr_group_account_id
135: FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
136: WHERE na.party_id = l_party_id
137: AND na.party_site_id = l_party_site_id
138: AND tga.named_account_id = na.named_account_id
139: AND tga.terr_group_id = l_tg_id;
290: WHERE terr_group_account_id = p_terr_grp_acct_id;
291:
292: --Delete this named account if it has no other TG references
293:
294: DELETE from JTF_TTY_NAMED_ACCTS
295: WHERE named_account_id = P_NAMED_ACCT_ID
296: AND NOT EXISTS (SELECT named_account_id
297: from JTF_TTY_TERR_GRP_ACCTS a
298: where a.named_account_id = P_NAMED_ACCT_ID);
301:
302: DELETE from JTF_TTY_ACCT_QUAL_MAPS
303: WHERE named_account_id = P_NAMED_ACCT_ID
304: AND NOT EXISTS (SELECT named_account_id
305: from JTF_TTY_NAMED_ACCTS a
306: where a.named_account_id = P_NAMED_ACCT_ID); --Delete named account if it has no other TG references
307:
308: l_user_id := fnd_global.user_id;
309: l_login_id := fnd_global.login_id;
640:
641: --Cursor to check if TGA already exists for specified party and terr group: Single Row
642: CURSOR c_check_tga_exists(l_party_id NUMBER, l_party_site_id NUMBER, l_tg_id NUMBER) IS
643: SELECT na.named_account_id, tga.terr_group_account_id
644: FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
645: WHERE na.party_id = l_party_id
646: AND na.party_site_id = l_party_site_id
647: AND tga.named_account_id = na.named_account_id
648: AND tga.terr_group_id = l_tg_id;
649:
650: --Cursor to check if given party is already a named account : Single Row
651: CURSOR c_check_na_exists(l_party_id NUMBER, l_party_site_id NUMBER) IS
652: SELECT named_account_id, mapping_complete_flag
653: FROM jtf_tty_named_accts
654: WHERE party_id = l_party_id
655: and party_site_id = l_party_site_id ;
656:
657:
767:
768: --If named account does not exist, then create a named account for the party
769: IF c_check_na_exists%NOTFOUND THEN
770:
771: SELECT jtf_tty_named_accts_s.nextval INTO l_na_id FROM dual;
772: -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :3' );
773:
774: INSERT INTO jtf_tty_named_accts
775: (NAMED_ACCOUNT_ID,
770:
771: SELECT jtf_tty_named_accts_s.nextval INTO l_na_id FROM dual;
772: -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :3' );
773:
774: INSERT INTO jtf_tty_named_accts
775: (NAMED_ACCOUNT_ID,
776: OBJECT_VERSION_NUMBER,
777: PARTY_ID,
778: PARTY_SITE_ID,
1734: -- QUERY MODIFIED FOR TERR_GROUP_ACCOUNT_ID IN/OUT
1735: cursor c_subsidiaries(cl_terr_group_account_id number) is
1736: select distinct gao.terr_group_account_id
1737: from hz_relationships hzr,
1738: jtf_tty_named_accts nai,
1739: jtf_tty_terr_grp_accts gai,
1740: jtf_tty_named_accts nao,
1741: jtf_tty_terr_grp_accts gao
1742: where gao.named_account_id = nao.named_account_id
1736: select distinct gao.terr_group_account_id
1737: from hz_relationships hzr,
1738: jtf_tty_named_accts nai,
1739: jtf_tty_terr_grp_accts gai,
1740: jtf_tty_named_accts nao,
1741: jtf_tty_terr_grp_accts gao
1742: where gao.named_account_id = nao.named_account_id
1743: and nao.party_id = hzr.object_id -- these are the subsidiary parties
1744: and hzr.subject_table_name = 'HZ_PARTIES'
1752: -- subsidiaries that are owned by user
1753: and exists( select 'Y'
1754: from jtf_tty_named_acct_rsc narsc ,
1755: jtf_tty_my_resources_v repdn
1756: -- jtf_tty_named_accts na,
1757: -- jtf_tty_terr_grp_accts ga
1758: where narsc.terr_group_account_id = gao.terr_group_account_id
1759: -- and ga.named_account_id = na.named_account_id
1760: and narsc.resource_id = repdn.resource_id