[Home] [Help]
469: CURSOR get_NON_OVLY_na_trans(LP_terr_group_account_id NUMBER) IS
470: SELECT ra.access_type
471: FROM
472: jtf_tty_named_acct_rsc nar
473: , jtf_tty_terr_grp_accts tga
474: , jtf_tty_terr_grp_roles tgr
475: , jtf_tty_role_access ra
476: WHERE tga.terr_group_account_id = nar.terr_group_account_id
477: AND nar.terr_group_account_id = LP_terr_group_account_id
481: UNION
482: SELECT ra.access_type
483: FROM
484: jtf_tty_named_acct_rsc nar
485: , jtf_tty_terr_grp_accts tga
486: , jtf_tty_terr_grp_roles tgr
487: , jtf_tty_role_access ra
488: WHERE tga.terr_group_account_id = nar.terr_group_account_id
489: AND nar.terr_group_account_id = LP_terr_group_account_id
556: and b.terr_group_id = lp_terr_group_id
557: AND EXISTS (
558: /* Named Account exists with Salesperson with this role */
559: SELECT NULL
560: FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
561: WHERE tga.terr_group_account_id = nar.terr_group_account_id
562: AND nar.terr_group_account_id = lp_terr_group_account_id
563: AND tga.terr_group_id = b.terr_group_id
564: AND nar.rsc_role_code = b.role_code );
575: /* Named Account Catch-All Customer Keyname values */
576: CURSOR catchall_cust(l_terr_group_id number) IS
577: SELECT distinct b.comparison_operator
578: ,b.value1_char
579: from jtf_tty_terr_grp_accts a
580: , jtf_tty_acct_qual_maps b
581: where a.named_account_id = b.named_account_id
582: and a.terr_group_id = l_terr_group_id
583: and b.qual_usg_id = -1012
588: CURSOR resource_grp(l_terr_group_acct_id number,l_role varchar2) IS
589: SELECT distinct b.resource_id
590: , b.rsc_group_id
591: , b.rsc_resource_type
592: from jtf_tty_terr_grp_accts a
593: , jtf_tty_named_acct_rsc b
594: where a.terr_group_account_id = l_terr_group_acct_id
595: and a.terr_group_account_id = b.terr_group_account_id
596: and b.rsc_role_code = l_role;
607: , c.terr_group_account_id
608: , a.duns_number_c
609: from hz_parties a
610: , jtf_tty_named_accts b
611: , jtf_tty_terr_grp_accts c
612: where c.terr_group_id = LP_terr_group_id
613: and b.named_account_id = c.named_account_id
614: and a.party_id = b.party_id
615: and a.status = 'A'
629: , c.terr_group_account_id
630: , a.duns_number_c
631: from hz_parties a
632: , jtf_tty_named_accts b
633: , jtf_tty_terr_grp_accts c
634: where c.terr_group_id = LP_terr_group_id
635: and b.named_account_id = c.named_account_id
636: and a.party_id = b.party_id
637: and a.status = 'A'
655: , c.terr_group_account_id
656: , a.duns_number_c
657: from hz_parties a
658: , jtf_tty_named_accts b
659: , jtf_tty_terr_grp_accts c
660: where c.terr_group_id = LP_terr_group_id
661: and b.named_account_id = c.named_account_id
662: and a.party_id = b.party_id
663: and a.status = 'A'
683: , c.terr_group_account_id
684: , a.duns_number_c
685: from hz_parties a
686: , jtf_tty_named_accts b
687: , jtf_tty_terr_grp_accts c
688: where c.terr_group_id = LP_terr_group_id
689: and b.named_account_id = c.named_account_id
690: and a.party_id = b.party_id
691: and a.status = 'A'
901: /* does Territory Group have at least 1 Named Account? */
902: SELECT COUNT(*)
903: INTO l_na_count
904: from jtf_tty_terr_groups g
905: , jtf_tty_terr_grp_accts ga
906: , jtf_tty_named_accts a
907: where g.terr_group_id = ga.terr_group_id
908: AND ga.named_account_id = a.named_account_id
909: AND g.terr_group_id = TERR_GROUP.TERR_GROUP_ID
2263: and b.terr_group_id = TERR_GROUP.TERR_GROUP_ID
2264: AND EXISTS (
2265: /* Named Account exists with Salesperson with this role */
2266: SELECT NULL
2267: FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
2268: WHERE tga.terr_group_account_id = nar.terr_group_account_id
2269: AND tga.terr_group_id = b.terr_group_id
2270: AND nar.rsc_role_code = b.role_code )
2271: AND ROWNUM < 2;