[Home] [Help]
4504: , c.terr_group_account_id
4505: , c.start_date
4506: , c.end_date
4507: FROM hz_parties a
4508: , jtf_tty_named_accts b
4509: , jtf_tty_terr_grp_accts c
4510: WHERE c.terr_group_account_id = LP_terr_group_acct_id
4511: AND b.named_account_id = c.named_account_id
4512: AND a.party_id = b.party_id
4520:
4521: /* get the PARTY_NAME + POSTAL_CODE for the Named Account:
4522: ** used for NAMED ACCOUNT territory creation */
4523: CURSOR get_party_name(LP_terr_group_acct_id NUMBER) IS
4524: SELECT /*+ index(b JTF_TTY_NAMED_ACCTS_U1) */ SUBSTR(a.party_name, 1, 45) || ': ' || a.postal_code name
4525: , b.named_account_id
4526: , c.terr_group_account_id
4527: , a.duns_number_c
4528: , c.start_date
4527: , a.duns_number_c
4528: , c.start_date
4529: , c.end_date
4530: FROM hz_parties a
4531: , jtf_tty_named_accts b
4532: , jtf_tty_terr_grp_accts c
4533: WHERE c.terr_group_account_id = LP_terr_group_acct_id
4534: AND b.named_account_id = c.named_account_id
4535: AND a.party_id = b.party_id
4553: SELECT SUBSTR(a.party_name, 1, 45) || ': ' || a.postal_code name
4554: , b.named_account_id
4555: , c.terr_group_account_id
4556: FROM hz_parties a
4557: , jtf_tty_named_accts b
4558: , jtf_tty_terr_grp_accts c
4559: WHERE c.terr_group_account_id = LP_terr_group_acct_id
4560: AND b.named_account_id = c.named_account_id
4561: AND a.party_id = b.party_id
4580: , b.named_account_id
4581: , c.terr_group_account_id
4582: , a.duns_number_c
4583: FROM hz_parties a
4584: , jtf_tty_named_accts b
4585: , jtf_tty_terr_grp_accts c
4586: WHERE c.terr_group_account_id = LP_terr_group_acct_id
4587: AND b.named_account_id = c.named_account_id
4588: AND a.party_id = b.party_id
4625: SELECT DECODE(l_matching_rule_code, '4', -1129, '2', -1120, '3', -1120, '5',-1005, -1001) qual_usg_id
4626: , '=' comparison_operator
4627: , DECODE(l_matching_rule_code, '4', hzp.party_number, '2', hzp.duns_number_c, '3', hzp.duns_number_c) value1_char
4628: , DECODE(l_matching_rule_code, '5', na.party_site_id, hzp.party_id) value1_num
4629: FROM hz_parties hzp, jtf_tty_named_accts na
4630: WHERE hzp.status = 'A'
4631: AND hzp.party_id = na.party_id
4632: AND na.named_account_id = l_na_id;
4633:
4758: /* does Territory Group Account have at least 1 Named Account? */
4759: SELECT COUNT(*)
4760: INTO l_na_count
4761: FROM jtf_tty_terr_grp_accts ga
4762: , jtf_tty_named_accts a
4763: WHERE ga.named_account_id = a.named_account_id
4764: AND ga.terr_group_account_id = p_terr_grp_acct_id(x)
4765: AND ROWNUM < 2;
4766:
7222: , c.attribute13
7223: , c.attribute14
7224: , c.attribute15
7225: FROM hz_parties a
7226: , jtf_tty_named_accts b
7227: , jtf_tty_terr_grp_accts c
7228: WHERE c.terr_group_id = LP_terr_group_id
7229: AND b.named_account_id = c.named_account_id
7230: AND a.party_id = b.party_id
7238:
7239: /* get the PARTY_NAME + POSTAL_CODE for the Named Account:
7240: ** used for NAMED ACCOUNT territory creation */
7241: CURSOR get_party_name(LP_terr_group_id NUMBER) IS
7242: SELECT /*+ index(b JTF_TTY_NAMED_ACCTS_U1) */ SUBSTR(a.party_name, 1, 45) || ': ' || a.postal_code name
7243: , b.named_account_id
7244: , c.terr_group_account_id
7245: , a.duns_number_c
7246: , c.start_date
7261: , c.attribute13
7262: , c.attribute14
7263: , c.attribute15
7264: FROM hz_parties a
7265: , jtf_tty_named_accts b
7266: , jtf_tty_terr_grp_accts c
7267: WHERE c.terr_group_id = LP_terr_group_id
7268: AND b.named_account_id = c.named_account_id
7269: AND a.party_id = b.party_id
7300: , c.attribute13
7301: , c.attribute14
7302: , c.attribute15
7303: FROM hz_parties a
7304: , jtf_tty_named_accts b
7305: , jtf_tty_terr_grp_accts c
7306: WHERE c.terr_group_id = LP_terr_group_id
7307: AND b.named_account_id = c.named_account_id
7308: AND a.party_id = b.party_id
7343: , c.attribute13
7344: , c.attribute14
7345: , c.attribute15
7346: FROM hz_parties a
7347: , jtf_tty_named_accts b
7348: , jtf_tty_terr_grp_accts c
7349: WHERE c.terr_group_id = LP_terr_group_id
7350: AND b.named_account_id = c.named_account_id
7351: AND a.party_id = b.party_id
7389: SELECT DECODE(l_matching_rule_code, '4', -1129, '2', -1120, '3', -1120, '5',-1005, -1001) qual_usg_id
7390: , '=' comparison_operator
7391: , DECODE(l_matching_rule_code, '4', hzp.party_number, '2', hzp.duns_number_c, '3', hzp.duns_number_c) value1_char
7392: , DECODE(l_matching_rule_code, '5', na.party_site_id, hzp.party_id) value1_num
7393: FROM hz_parties hzp, jtf_tty_named_accts na
7394: WHERE hzp.status = 'A'
7395: AND hzp.party_id = na.party_id
7396: AND na.named_account_id = l_na_id;
7397:
7568: /*SELECT COUNT(*)
7569: INTO l_na_count
7570: FROM jtf_tty_terr_groups g
7571: , jtf_tty_terr_grp_accts ga
7572: , jtf_tty_named_accts a
7573: WHERE g.terr_group_id = ga.terr_group_id
7574: AND ga.named_account_id = a.named_account_id
7575: AND g.terr_group_id = p_terr_group_id(x)
7576: AND ROWNUM < 2;*/
11993:
11994: -- enable all the triggers
11995: alter_triggers(p_status => 'ENABLE');
11996:
11997: -- truncate the table jtf_tty_named_accts so that next incremental runs donot process the rows processed in total mode
11998: l_stmt := 'truncate table '||g_jtf_schema||'.'|| 'jtf_tty_named_acct_changes';
11999: EXECUTE IMMEDIATE l_stmt;
12000:
12001: EXCEPTION