[Home] [Help]
467: CURSOR get_NON_OVLY_na_trans(LP_terr_group_account_id NUMBER) IS
468: SELECT ra.access_type
469: FROM
470: jtf_tty_named_acct_rsc nar
471: , jtf_tty_terr_grp_accts tga
472: , jtf_tty_terr_grp_roles tgr
473: , jtf_tty_role_access ra
474: WHERE tga.terr_group_account_id = nar.terr_group_account_id
475: AND nar.terr_group_account_id = LP_terr_group_account_id
479: UNION
480: SELECT ra.access_type
481: FROM
482: jtf_tty_named_acct_rsc nar
483: , jtf_tty_terr_grp_accts tga
484: , jtf_tty_terr_grp_roles tgr
485: , jtf_tty_role_access ra
486: WHERE tga.terr_group_account_id = nar.terr_group_account_id
487: AND nar.terr_group_account_id = LP_terr_group_account_id
554: and b.terr_group_id = lp_terr_group_id
555: AND EXISTS (
556: /* Named Account exists with Salesperson with this role */
557: SELECT NULL
558: FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
559: WHERE tga.terr_group_account_id = nar.terr_group_account_id
560: AND nar.terr_group_account_id = lp_terr_group_account_id
561: AND tga.terr_group_id = b.terr_group_id
562: AND nar.rsc_role_code = b.role_code );
573: /* Named Account Catch-All Customer Keyname values */
574: CURSOR catchall_cust(l_terr_group_id number) IS
575: SELECT distinct b.comparison_operator
576: ,b.value1_char
577: from jtf_tty_terr_grp_accts a
578: , jtf_tty_acct_qual_maps b
579: where a.named_account_id = b.named_account_id
580: and a.terr_group_id = l_terr_group_id
581: and b.qual_usg_id = -1012
586: CURSOR resource_grp(l_terr_group_acct_id number,l_role varchar2) IS
587: SELECT distinct b.resource_id
588: , b.rsc_group_id
589: , b.rsc_resource_type
590: from jtf_tty_terr_grp_accts a
591: , jtf_tty_named_acct_rsc b
592: where a.terr_group_account_id = l_terr_group_acct_id
593: and a.terr_group_account_id = b.terr_group_account_id
594: and b.rsc_role_code = l_role;
605: , c.terr_group_account_id
606: , a.duns_number_c
607: from hz_parties a
608: , jtf_tty_named_accts b
609: , jtf_tty_terr_grp_accts c
610: where c.terr_group_id = LP_terr_group_id
611: and b.named_account_id = c.named_account_id
612: and a.party_id = b.party_id
613: and a.status = 'A'
627: , c.terr_group_account_id
628: , a.duns_number_c
629: from hz_parties a
630: , jtf_tty_named_accts b
631: , jtf_tty_terr_grp_accts c
632: where c.terr_group_id = LP_terr_group_id
633: and b.named_account_id = c.named_account_id
634: and a.party_id = b.party_id
635: and a.status = 'A'
653: , c.terr_group_account_id
654: , a.duns_number_c
655: from hz_parties a
656: , jtf_tty_named_accts b
657: , jtf_tty_terr_grp_accts c
658: where c.terr_group_id = LP_terr_group_id
659: and b.named_account_id = c.named_account_id
660: and a.party_id = b.party_id
661: and a.status = 'A'
681: , c.terr_group_account_id
682: , a.duns_number_c
683: from hz_parties a
684: , jtf_tty_named_accts b
685: , jtf_tty_terr_grp_accts c
686: where c.terr_group_id = LP_terr_group_id
687: and b.named_account_id = c.named_account_id
688: and a.party_id = b.party_id
689: and a.status = 'A'
902: /* does Territory Group have at least 1 Named Account ? */
903: SELECT COUNT(*)
904: INTO l_na_count
905: from jtf_tty_terr_groups g
906: , jtf_tty_terr_grp_accts ga
907: , jtf_tty_named_accts a
908: where g.terr_group_id = ga.terr_group_id
909: AND ga.named_account_id = a.named_account_id
910: AND g.terr_group_id = TERR_GROUP.TERR_GROUP_ID
2274: and b.terr_group_id = TERR_GROUP.TERR_GROUP_ID
2275: AND EXISTS (
2276: /* Named Account exists with Salesperson with this role */
2277: SELECT NULL
2278: FROM jtf_tty_named_acct_rsc nar, jtf_tty_terr_grp_accts tga
2279: WHERE tga.terr_group_account_id = nar.terr_group_account_id
2280: AND tga.terr_group_id = b.terr_group_id
2281: AND nar.rsc_role_code = b.role_code )
2282: AND ROWNUM < 2;