APPS.BIM_I_LEAD_FACTS_PKG dependencies on HZ_PARTIES

Line 467: and b.OWNER_TABLE_NAME='HZ_PARTIES'

463: set stg.cust_category
464: = (select b.class_code from hz_code_assignments b
465: where
466: stg.customer_id=b.OWNER_TABLE_ID
467: and b.OWNER_TABLE_NAME='HZ_PARTIES'
468: and b.Primary_flag = 'Y'
469: and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
470: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
471: and b.status='A')

Line 477: and b.OWNER_TABLE_NAME='HZ_PARTIES'

473: exists
474: (select b.class_code from hz_code_assignments b
475: where
476: stg.customer_id=b.OWNER_TABLE_ID
477: and b.OWNER_TABLE_NAME='HZ_PARTIES'
478: and b.Primary_flag = 'Y'
479: and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
480: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
481: and b.status='A');

Line 489: and b.OWNER_TABLE_NAME='HZ_PARTIES'

485: set stg.cust_category
486: = (select b.class_code from hz_code_assignments b
487: where
488: stg.customer_id=b.OWNER_TABLE_ID
489: and b.OWNER_TABLE_NAME='HZ_PARTIES'
490: and b.Primary_flag = 'Y'
491: and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
492: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
493: and b.status='A'--Active

Line 497: c.OWNER_TABLE_NAME='HZ_PARTIES'

493: and b.status='A'--Active
494: and b.START_DATE_ACTIVE =
495: ( select max(START_DATE_ACTIVE) from hz_code_assignments c
496: where
497: c.OWNER_TABLE_NAME='HZ_PARTIES'
498: and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
499: and c.Primary_flag = 'Y'
500: and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
501: and c.status='A'

Line 509: and b.OWNER_TABLE_NAME='HZ_PARTIES'

505: exists
506: (select b.class_code from hz_code_assignments b
507: where
508: stg.customer_id=b.OWNER_TABLE_ID
509: and b.OWNER_TABLE_NAME='HZ_PARTIES'
510: and b.Primary_flag = 'Y'
511: and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
512: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
513: and b.status='A'--Active

Line 517: c.OWNER_TABLE_NAME='HZ_PARTIES'

513: and b.status='A'--Active
514: and b.START_DATE_ACTIVE =
515: ( select max(START_DATE_ACTIVE) from hz_code_assignments c
516: where
517: c.OWNER_TABLE_NAME='HZ_PARTIES'
518: and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
519: and c.Primary_flag = 'Y'
520: and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
521: and c.status='A'

Line 532: (SELECT 1 from HZ_CUST_ACCOUNTS a,hz_parties b

528:
529: UPDATE bim_i_lead_facts_stg stg SET CUSTOMER_FLAG='Y'
530: WHERE
531: EXISTS
532: (SELECT 1 from HZ_CUST_ACCOUNTS a,hz_parties b
533: WHERE a.party_id=stg.customer_id
534: AND stg.lead_creation_date >= trunc(a.creation_date)
535: AND a.party_id=b.party_id
536: AND b.certification_level is not null);

Line 1087: and b.OWNER_TABLE_NAME='HZ_PARTIES'

1083: set stg.cust_category
1084: = (select b.class_code from hz_code_assignments b
1085: where
1086: stg.customer_id=b.OWNER_TABLE_ID
1087: and b.OWNER_TABLE_NAME='HZ_PARTIES'
1088: and b.Primary_flag = 'Y'
1089: and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1090: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1091: and b.status='A'--Active

Line 1095: c.OWNER_TABLE_NAME='HZ_PARTIES'

1091: and b.status='A'--Active
1092: and b.START_DATE_ACTIVE =
1093: ( select max(START_DATE_ACTIVE) from hz_code_assignments c
1094: where
1095: c.OWNER_TABLE_NAME='HZ_PARTIES'
1096: and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1097: and c.Primary_flag = 'Y'
1098: and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1099: and c.status='A'

Line 1107: and b.OWNER_TABLE_NAME='HZ_PARTIES'

1103: exists
1104: (select b.class_code from hz_code_assignments b
1105: where
1106: stg.customer_id=b.OWNER_TABLE_ID
1107: and b.OWNER_TABLE_NAME='HZ_PARTIES'
1108: and b.Primary_flag = 'Y'
1109: and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1110: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1111: and b.status='A'--Active

Line 1115: c.OWNER_TABLE_NAME='HZ_PARTIES'

1111: and b.status='A'--Active
1112: and b.START_DATE_ACTIVE =
1113: ( select max(START_DATE_ACTIVE) from hz_code_assignments c
1114: where
1115: c.OWNER_TABLE_NAME='HZ_PARTIES'
1116: and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1117: and c.Primary_flag = 'Y'
1118: and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1119: and c.status='A'

Line 1129: (select 1 from HZ_CUST_ACCOUNTS a,hz_parties b

1125:
1126: update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
1127: where
1128: exists
1129: (select 1 from HZ_CUST_ACCOUNTS a,hz_parties b
1130: where a.party_id=stg.customer_id
1131: and stg.lead_creation_date >= trunc(a.creation_date)
1132: and a.party_id=b.party_id
1133: and b.certification_level is not null);