eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMS_LISTIMPORT_PVT dependencies on AMS_HZ_B2B_MAPPING_V

Line 767: FROM AMS_HZ_B2B_MAPPING_V

763: ADDRESS_ATTRIBUTE17,
764: ADDRESS_ATTRIBUTE18,
765: ADDRESS_ATTRIBUTE19,
766: ADDRESS_ATTRIBUTE20
767: FROM AMS_HZ_B2B_MAPPING_V
768: WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id
769: and request_id = l_request_id
770: AND load_status in ('ACTIVE','RELOAD');
771:

Line 2656: update ams_hz_b2b_mapping_v b2b

2652:
2653: if l_imp_type = 'B2B' then
2654: --if party_id is provided and if its of type relationship
2655: --update org_party_id and ocont_party_id
2656: update ams_hz_b2b_mapping_v b2b
2657: set (org_party_id, ocont_party_id) = (select object_id, subject_id from hz_relationships
2658: where party_id = b2b.party_id and object_type = 'ORGANIZATION'
2659: and subject_type = 'PERSON' and relationship_type = 'CONTACT'
2660: and nvl(status,'A') = 'A')

Line 2666: update ams_hz_b2b_mapping_v b2b

2662: and party_id is not null
2663: and org_party_id is null;
2664:
2665: --if party_id is of type organization then update org_party_id with party_id
2666: update ams_hz_b2b_mapping_v b2b
2667: set org_party_id = party_id
2668: where import_list_header_id = p_import_list_header_id
2669: and party_id is not null
2670: and org_party_id is null;

Line 2672: update ams_hz_b2b_mapping_v

2668: where import_list_header_id = p_import_list_header_id
2669: and party_id is not null
2670: and org_party_id is null;
2671:
2672: update ams_hz_b2b_mapping_v
2673: set org_exist = 'Y'
2674: where import_list_header_id = p_import_list_header_id
2675: and org_party_id is not null;
2676:

Line 2677: update ams_hz_b2b_mapping_v

2673: set org_exist = 'Y'
2674: where import_list_header_id = p_import_list_header_id
2675: and org_party_id is not null;
2676:
2677: update ams_hz_b2b_mapping_v
2678: set ocont_exist = 'Y'
2679: where import_list_header_id = p_import_list_header_id
2680: and ocont_party_id is not null;
2681:

Line 4761: FROM AMS_HZ_B2B_MAPPING_V

4757: ORG_CONTACT_ATTRIBUTE_CATEGORY,
4758: ADDRESS_ATTRIBUTE_CATEGORY ,
4759: PARTY_SITE_USE
4760:
4761: FROM AMS_HZ_B2B_MAPPING_V
4762: WHERE party_id = p_party_id
4763: and load_status = 'SUCCESS';
4764:
4765: -- Cursor B2C Party Type

Line 7733: from ams_hz_b2b_mapping_v where import_source_line_id = p_import_source_line_id;

7729:
7730: cursor c_b2bxmlrec is
7731: select org_imp_xml_element_id, add_imp_xml_element_id, ocont_imp_xml_element_id,
7732: cp_imp_xml_element_id, em_imp_xml_element_id
7733: from ams_hz_b2b_mapping_v where import_source_line_id = p_import_source_line_id;
7734:
7735: cursor c_b2cxmlrec is
7736: select per_imp_xml_element_id, add_imp_xml_element_id,cp_imp_xml_element_id, em_imp_xml_element_id
7737: from ams_hz_b2c_mapping_v where import_source_line_id = p_import_source_line_id;

Line 8127: AMS_HZ_B2B_MAPPING_V B2B

8123: RELATIONSHIP_CODE,
8124: RELATIONSHIP_TYPE,
8125: l_invalid_value
8126: FROM
8127: AMS_HZ_B2B_MAPPING_V B2B
8128: WHERE
8129: IMPORT_LIST_HEADER_ID=p_import_list_header_id and
8130: ( PERSON_FIRST_NAME IS NOT NULL OR PERSON_LAST_NAME IS NOT NULL )
8131: AND (( RELATIONSHIP_CODE is not null

Line 8895: FROM AMS_HZ_B2B_MAPPING_V

8891: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(ORG_PS_ATTRIBUTE18)),'x')),150) - 150,0,' ','ORG_PS_ATTRIBUTE18 :'||l_invalid_size||'(150)') ORG_PS_ATTRIBUTE18,
8892: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(ORG_PS_ATTRIBUTE19)),'x')),150) - 150,0,' ','ORG_PS_ATTRIBUTE19 :'||l_invalid_size||'(150)') ORG_PS_ATTRIBUTE19,
8893: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(ORG_PS_ATTRIBUTE20)),'x')),150) - 150,0,' ','ORG_PS_ATTRIBUTE20 :'||l_invalid_size||'(150)') ORG_PS_ATTRIBUTE20,
8894: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(NATIVE_LANGUAGE)),'x')),4) - 4,0,' ','LANGUAGE :'||l_invalid_size||'(4)') NATIVE_LANGUAGE
8895: FROM AMS_HZ_B2B_MAPPING_V
8896: WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id
8897: and load_status in ('ACTIVE','RELOAD')
8898: AND
8899: (

Line 10021: FROM ams_hz_b2b_mapping_v

10017: DATE_OF_BIRTH,
10018: DATE_OF_DEATH,
10019: DEBARTMENTS_DATE,
10020: MARITAL_STATUS_EFFECTIVE_DATE
10021: FROM ams_hz_b2b_mapping_v
10022: WHERE import_list_header_id = p_import_list_header_id
10023: and load_status in ('ACTIVE','RELOAD')
10024: AND (ADDRESS_EFFECTIVE_DATE is not null or ADDRESS_EXPIRATION_DATE is not null or
10025: DATE_OF_BIRTH is not null or DATE_OF_DEATH is not null or DEBARTMENTS_DATE is not null

Line 11322: FROM AMS_HZ_B2B_MAPPING_V

11318: DECODE(GREATEST(LENGTH(nvl(LTRIM(RTRIM(VEHICLE_RESPONSE_CODE)),'x')),30) - 30,0,' ','VEHICLE_RESPONSE_CODE :'||l_invalid_size||'(30)') VEHICLE_RESPONSE_CODE,
11319: DECODE(GREATEST(LENGTH(nvl(LTRIM(RTRIM(SALES_AGENT_EMAIL_ADDRESS)),'x')),2000) - 2000,0,' ','SALES_AGENT_EMAIL_ADDRESS:'||l_invalid_size||'(2000)') SALES_AGENT_EMAIL_ADDRESS,
11320: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(ORIG_SYSTEM_REFERENCE)),'x')),240) -240,0,' ','ORIG_SYSTEM_REFERENCE :'||l_invalid_size
11321: ||'(240)') ORIG_SYSTEM_REFERENCE
11322: FROM AMS_HZ_B2B_MAPPING_V
11323: WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id
11324: and load_status = 'ERROR'
11325: AND
11326: (

Line 13949: update ams_hz_b2b_mapping_v b2b

13945:
13946: if l_import_type = 'B2B' then
13947: --if party_id is provided and if its of type relationship
13948: --update org_party_id and ocont_party_id
13949: update ams_hz_b2b_mapping_v b2b
13950: set (org_party_id, ocont_party_id) = (select object_id, subject_id from hz_relationships
13951: where party_id = b2b.party_id and object_type = 'ORGANIZATION'
13952: and subject_type = 'PERSON' and relationship_type = 'CONTACT'
13953: and nvl(status,'A') = 'A')

Line 13959: update ams_hz_b2b_mapping_v b2b

13955: and party_id is not null
13956: and org_party_id is null;
13957:
13958: --if party_id is of type organization then update org_party_id with party_id
13959: update ams_hz_b2b_mapping_v b2b
13960: set org_party_id = (select party_id from hz_parties where party_type = 'ORGANIZATION' and party_id = b2b.party_id and nvl(status,'A') = 'A') -- bug 5100612
13961: where import_list_header_id = p_import_list_header_id
13962: and party_id is not null
13963: and org_party_id is null;

Line 13965: update ams_hz_b2b_mapping_v

13961: where import_list_header_id = p_import_list_header_id
13962: and party_id is not null
13963: and org_party_id is null;
13964:
13965: update ams_hz_b2b_mapping_v
13966: set org_exist = 'Y'
13967: where import_list_header_id = p_import_list_header_id
13968: and org_party_id is not null;
13969:

Line 13970: update ams_hz_b2b_mapping_v

13966: set org_exist = 'Y'
13967: where import_list_header_id = p_import_list_header_id
13968: and org_party_id is not null;
13969:
13970: update ams_hz_b2b_mapping_v
13971: set ocont_exist = 'Y'
13972: where import_list_header_id = p_import_list_header_id
13973: and ocont_party_id is not null;
13974:

Line 17617: Insert into AMS_HZ_B2B_MAPPING_V(

17613: open c_batchID;
17614: fetch c_batchID into l_batch_id;
17615: close c_batchID;
17616: FOR i IN 1..p_cust_b2b_tbl.COUNT LOOP
17617: Insert into AMS_HZ_B2B_MAPPING_V(
17618: IMPORT_SOURCE_LINE_ID,
17619: LAST_UPDATED_BY,
17620: LAST_UPDATE_DATE,
17621: CREATION_DATE,

Line 18554: from ams_hz_b2b_mapping_v b2b

18550:
18551: cursor c_org_key is
18552: select b2b.party_name, 'N',
18553: hz_fuzzy_pub.Generate_Key('ORGANIZATION',b2b.party_name,null,null,null,null,null,null,null) "org_key"
18554: from ams_hz_b2b_mapping_v b2b
18555: where b2b.import_list_header_id = p_import_list_header_id
18556: and b2b.load_status in ('ACTIVE','RELOAD')
18557: and b2b.party_name is not null
18558: and b2b.org_key is null

Line 18565: from ams_hz_b2b_mapping_v b2b

18561:
18562:
18563: cursor c_customer_exists is
18564: select b2b.org_key, 'Y'
18565: from ams_hz_b2b_mapping_v b2b
18566: where b2b.import_list_header_id = 40236
18567: -- and nvl(b2b.org_exist,'N') = 'N'
18568: and b2b.org_key is not null
18569: and b2b.load_status in ('ACTIVE','RELOAD')

Line 18580: hz_parties party, ams_hz_b2b_mapping_v b2b

18576:
18577: cursor c_address_country is
18578: select b2b.import_source_line_id, max(psite.party_id)
18579: from hz_party_sites psite, hz_locations loc,
18580: hz_parties party, ams_hz_b2b_mapping_v b2b
18581: where b2b.import_list_header_id = p_import_list_header_id
18582: and b2b.load_status in ('ACTIVE','RELOAD')
18583: and b2b.org_exist = 'Y'
18584: and b2b.address1 is not null

Line 18599: hz_parties party, ams_hz_b2b_mapping_v b2b

18595:
18596: cursor c_country is
18597: select b2b.import_source_line_id, max(psite.party_id)
18598: from hz_party_sites psite, hz_locations loc,
18599: hz_parties party, ams_hz_b2b_mapping_v b2b
18600: where b2b.import_list_header_id = p_import_list_header_id
18601: and b2b.load_status in ('ACTIVE','RELOAD')
18602: and b2b.org_exist = 'Y'
18603: and b2b.country is not null

Line 18615: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18611: group by b2b.import_source_line_id;
18612:
18613: cursor c_max_party is
18614: select b2b.import_source_line_id, max(pty. party_id)
18615: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18616: where b2b.import_list_header_id = p_import_list_header_id
18617: and b2b.load_status in ('ACTIVE','RELOAD')
18618: and b2b.org_exist = 'Y'
18619: and b2b.org_party_id is null

Line 18644: FORALL i IN L_PARTY_NAME.FIRST..L_PARTY_NAME.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18640:
18641: IF L_PARTY_NAME.COUNT = 0 AND l_last_fetch THEN
18642: EXIT;
18643: END IF;
18644: FORALL i IN L_PARTY_NAME.FIRST..L_PARTY_NAME.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18645: SET org_key = L_ORG_KEY(I),
18646: org_exist = L_ORG_EXIST(I)
18647: WHERE party_name = L_PARTY_NAME(i)
18648: and import_list_header_id = p_import_list_header_id ;

Line 18676: FORALL i IN L_ORG_KEY.FIRST..L_ORG_KEY.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18672:
18673: IF L_ORG_KEY.COUNT = 0 AND l_last_fetch THEN
18674: EXIT;
18675: END IF;
18676: FORALL i IN L_ORG_KEY.FIRST..L_ORG_KEY.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18677: SET org_exist = L_ORG_EXIST(I)
18678: WHERE org_key = L_ORG_KEY(i)
18679: -- and org_key is null
18680: and import_list_header_id = p_import_list_header_id ;

Line 18707: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18703:
18704: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
18705: EXIT;
18706: END IF;
18707: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18708: SET org_party_id = L_ORG_PARTY_ID(I)
18709: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
18710:
18711: if l_last_fetch then

Line 18737: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18733:
18734: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
18735: EXIT;
18736: END IF;
18737: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18738: SET org_party_id = L_ORG_PARTY_ID(I)
18739: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
18740:
18741: if l_last_fetch then

Line 18767: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18763:
18764: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
18765: EXIT;
18766: END IF;
18767: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18768: SET org_party_id = L_ORG_PARTY_ID(I)
18769: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
18770:
18771: if l_last_fetch then

Line 18823: from ams_hz_b2b_mapping_v b2b

18819:
18820: cursor c_org_key is
18821: select b2b.party_name, 'N',
18822: hz_fuzzy_pub.Generate_Key('ORGANIZATION',b2b.party_name,null,null,null,null,null,null,null) "org_key"
18823: from ams_hz_b2b_mapping_v b2b
18824: where b2b.import_list_header_id = p_import_list_header_id
18825: and b2b.load_status in ('ACTIVE','RELOAD')
18826: and b2b.party_name is not null
18827: and b2b.org_key is null

Line 18835: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18831:
18832:
18833: cursor c_customer_exists is
18834: select b2b.org_key, 'Y'
18835: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18836: where b2b.import_list_header_id = p_import_list_header_id
18837: and pty.customer_key = b2b.org_key
18838: and b2b.org_key is not null
18839: and b2b.org_party_id is null

Line 18850: hz_parties party, ams_hz_b2b_mapping_v b2b

18846:
18847: cursor c_address_country is
18848: select b2b.import_source_line_id, max(psite.party_id)
18849: from hz_party_sites psite, hz_locations loc,
18850: hz_parties party, ams_hz_b2b_mapping_v b2b
18851: where b2b.import_list_header_id = p_import_list_header_id
18852: and b2b.load_status in ('ACTIVE','RELOAD')
18853: and b2b.org_exist = 'Y'
18854: and b2b.address1 is not null

Line 18870: hz_parties party, ams_hz_b2b_mapping_v b2b

18866:
18867: cursor c_country is
18868: select b2b.import_source_line_id, max(psite.party_id)
18869: from hz_party_sites psite, hz_locations loc,
18870: hz_parties party, ams_hz_b2b_mapping_v b2b
18871: where b2b.import_list_header_id = p_import_list_header_id
18872: and b2b.load_status in ('ACTIVE','RELOAD')
18873: and b2b.org_exist = 'Y'
18874: and b2b.country is not null

Line 18887: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18883: group by b2b.import_source_line_id;
18884:
18885: cursor c_max_party is
18886: select b2b.import_source_line_id, max(pty. party_id)
18887: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18888: where b2b.import_list_header_id = p_import_list_header_id
18889: and b2b.load_status in ('ACTIVE','RELOAD')
18890: and b2b.org_exist = 'Y'
18891: and b2b.org_party_id is null

Line 18916: FORALL i IN L_PARTY_NAME.FIRST..L_PARTY_NAME.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18912:
18913: IF L_PARTY_NAME.COUNT = 0 AND l_last_fetch THEN
18914: EXIT;
18915: END IF;
18916: FORALL i IN L_PARTY_NAME.FIRST..L_PARTY_NAME.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18917: SET org_key = L_ORG_KEY(I),
18918: org_exist = L_ORG_EXIST(I)
18919: WHERE party_name = L_PARTY_NAME(i)
18920: and import_list_header_id = p_import_list_header_id ;

Line 18948: FORALL i IN L_ORG_KEY.FIRST..L_ORG_KEY.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18944:
18945: IF L_ORG_KEY.COUNT = 0 AND l_last_fetch THEN
18946: EXIT;
18947: END IF;
18948: FORALL i IN L_ORG_KEY.FIRST..L_ORG_KEY.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18949: SET org_exist = L_ORG_EXIST(I)
18950: WHERE org_key = L_ORG_KEY(i)
18951: and org_key is null
18952: and import_list_header_id = p_import_list_header_id ;

Line 18979: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

18975:
18976: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
18977: EXIT;
18978: END IF;
18979: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
18980: SET org_party_id = L_ORG_PARTY_ID(I)
18981: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
18982:
18983: if l_last_fetch then

Line 19009: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19005:
19006: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19007: EXIT;
19008: END IF;
19009: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19010: SET org_party_id = L_ORG_PARTY_ID(I)
19011: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19012:
19013: if l_last_fetch then

Line 19039: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19035:
19036: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19037: EXIT;
19038: END IF;
19039: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19040: SET org_party_id = L_ORG_PARTY_ID(I)
19041: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19042:
19043: if l_last_fetch then

Line 19695: from ams_hz_b2b_mapping_v b2b

19691: cursor c_per_key is
19692: select b2b.import_source_line_id, 'N',
19693: hz_fuzzy_pub.Generate_Key('PERSON',null,null,null,null,null,null,b2b.PERSON_FIRST_NAME,b2b.PERSON_LAST_NAME)
19694: "per_key"
19695: from ams_hz_b2b_mapping_v b2b
19696: where b2b.import_list_header_id = p_import_list_header_id
19697: and b2b.load_status in ('ACTIVE','RELOAD')
19698: and b2b.PERSON_FIRST_NAME is not null
19699: and b2b.PERSON_LAST_NAME is not null

Line 19705: from ams_hz_b2b_mapping_v b2b

19701: and b2b.OCONT_PARTY_ID is null;
19702:
19703: cursor c_person_exists is
19704: SELECT b2b.import_source_line_id, 'Y'
19705: from ams_hz_b2b_mapping_v b2b
19706: where b2b.import_list_header_id = p_import_list_header_id
19707: and b2b.person_key is not null
19708: and b2b.load_status in ('ACTIVE','RELOAD')
19709: and b2b.OCONT_PARTY_ID is null

Line 19718: from hz_parties pty, ams_hz_b2b_mapping_v b2b

19714: and pty.party_type = 'PERSON'
19715: and pty.status = 'A' );
19716: /*
19717: select b2b.import_source_line_id, 'Y'
19718: from hz_parties pty, ams_hz_b2b_mapping_v b2b
19719: where b2b.import_list_header_id = p_import_list_header_id
19720: and pty.customer_key = b2b.person_key
19721: and b2b.person_key is not null
19722: and b2b.load_status in ('ACTIVE','RELOAD')

Line 19735: ams_hz_b2b_mapping_v b2b

19731: hz_parties per,
19732: hz_relationships rel,
19733: hz_contact_points cpoint,
19734: hz_contact_points cpoint1,
19735: ams_hz_b2b_mapping_v b2b
19736: where b2b.import_list_header_id = p_import_list_header_id
19737: and b2b.load_status in ('ACTIVE','RELOAD')
19738: and b2b.ocont_exist = 'Y'
19739: and b2b.phone_number is not null

Line 19775: ams_hz_b2b_mapping_v b2b

19771: hz_parties org,
19772: hz_parties per,
19773: hz_relationships rel,
19774: hz_contact_points cpoint,
19775: ams_hz_b2b_mapping_v b2b
19776: where b2b.import_list_header_id = p_import_list_header_id
19777: and b2b.load_status in ('ACTIVE','RELOAD')
19778: and b2b.ocont_exist = 'Y'
19779: and b2b.email_address is not null

Line 19807: ams_hz_b2b_mapping_v b2b

19803: hz_parties org,
19804: hz_parties per,
19805: hz_relationships rel,
19806: hz_contact_points cpoint,
19807: ams_hz_b2b_mapping_v b2b
19808: where b2b.import_list_header_id = p_import_list_header_id
19809: and b2b.load_status in ('ACTIVE','RELOAD')
19810: and b2b.ocont_exist = 'Y'
19811: and b2b.phone_number is not null

Line 19839: from ams_hz_b2b_mapping_v

19835:
19836: cursor c_cont_dedup is
19837: select max(import_source_line_id), org_key,person_key,email_address,transposed_phone_number ,
19838: 'DUPLICATE' status
19839: from ams_hz_b2b_mapping_v
19840: where import_list_header_id = p_import_list_header_id
19841: -- and ocont_party_id is null
19842: and load_status in ('ACTIVE','RELOAD')
19843: group by org_key,person_key,email_address,transposed_phone_number;

Line 19863: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19859:
19860: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19861: EXIT;
19862: END IF;
19863: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19864: SET person_key = L_person_KEY(I),
19865: ocont_exist = L_ocont_EXIST(I)
19866: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19867:

Line 19893: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19889:
19890: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19891: EXIT;
19892: END IF;
19893: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19894: SET ocont_exist = L_ocont_EXIST(I)
19895: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19896:
19897: if l_last_fetch then

Line 19921: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19917:
19918: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19919: EXIT;
19920: END IF;
19921: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19922: SET ocont_party_id = L_ocont_PARTY_ID(I)
19923: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19924:
19925: if l_last_fetch then

Line 19950: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19946:
19947: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19948: EXIT;
19949: END IF;
19950: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19951: SET ocont_party_id = L_ocont_PARTY_ID(I)
19952: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19953:
19954: if l_last_fetch then

Line 19979: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

19975:
19976: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
19977: EXIT;
19978: END IF;
19979: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
19980: SET ocont_party_id = L_ocont_PARTY_ID(I)
19981: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
19982:
19983: if l_last_fetch then

Line 20012: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20008:
20009: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20010: EXIT;
20011: END IF;
20012: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20013: SET load_status = L_ocont_status(i)
20014: WHERE import_list_header_id = p_import_list_header_id
20015: and IMPORT_SOURCE_LINE_ID < L_IMPORT_SOURCE_LINE_ID(i)
20016: and load_status in ('ACTIVE','RELOAD')

Line 20073: from ams_hz_b2b_mapping_v b2b

20069: cursor c_per_key is
20070: select b2b.import_source_line_id, 'N',
20071: hz_fuzzy_pub.Generate_Key('PERSON',null,null,null,null,null,null,b2b.PERSON_FIRST_NAME,b2b.PERSON_LAST_NAME)
20072: "per_key"
20073: from ams_hz_b2b_mapping_v b2b
20074: where b2b.import_list_header_id = p_import_list_header_id
20075: and b2b.load_status in ('ACTIVE','RELOAD')
20076: and b2b.PERSON_FIRST_NAME is not null
20077: and b2b.PERSON_LAST_NAME is not null

Line 20082: from hz_parties pty, ams_hz_b2b_mapping_v b2b

20078: and b2b.org_party_id is not null;
20079:
20080: cursor c_person_exists is
20081: select b2b.import_source_line_id, 'Y'
20082: from hz_parties pty, ams_hz_b2b_mapping_v b2b
20083: where b2b.import_list_header_id = p_import_list_header_id
20084: and pty.customer_key = b2b.person_key
20085: and b2b.person_key is not null
20086: and b2b.load_status in ('ACTIVE','RELOAD')

Line 20099: ams_hz_b2b_mapping_v b2b

20095: hz_parties per,
20096: hz_relationships rel,
20097: hz_contact_points cpoint,
20098: hz_contact_points cpoint1,
20099: ams_hz_b2b_mapping_v b2b
20100: where b2b.import_list_header_id = p_import_list_header_id
20101: and b2b.load_status in ('ACTIVE','RELOAD')
20102: and b2b.ocont_exist = 'Y'
20103: and b2b.phone_number is not null

Line 20139: ams_hz_b2b_mapping_v b2b

20135: hz_parties org,
20136: hz_parties per,
20137: hz_relationships rel,
20138: hz_contact_points cpoint,
20139: ams_hz_b2b_mapping_v b2b
20140: where b2b.import_list_header_id = p_import_list_header_id
20141: and b2b.load_status in ('ACTIVE','RELOAD')
20142: and b2b.ocont_exist = 'Y'
20143: and b2b.email_address is not null

Line 20171: ams_hz_b2b_mapping_v b2b

20167: hz_parties org,
20168: hz_parties per,
20169: hz_relationships rel,
20170: hz_contact_points cpoint,
20171: ams_hz_b2b_mapping_v b2b
20172: where b2b.import_list_header_id = p_import_list_header_id
20173: and b2b.load_status in ('ACTIVE','RELOAD')
20174: and b2b.ocont_exist = 'Y'
20175: and b2b.phone_number is not null

Line 20218: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20214:
20215: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20216: EXIT;
20217: END IF;
20218: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20219: SET person_key = L_person_KEY(I),
20220: ocont_exist = L_ocont_EXIST(I)
20221: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20222:

Line 20248: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20244:
20245: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20246: EXIT;
20247: END IF;
20248: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20249: SET ocont_exist = L_ocont_EXIST(I)
20250: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20251:
20252: if l_last_fetch then

Line 20276: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20272:
20273: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20274: EXIT;
20275: END IF;
20276: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20277: SET ocont_party_id = L_ocont_PARTY_ID(I)
20278: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20279:
20280: if l_last_fetch then

Line 20305: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20301:
20302: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20303: EXIT;
20304: END IF;
20305: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20306: SET ocont_party_id = L_ocont_PARTY_ID(I)
20307: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20308:
20309: if l_last_fetch then

Line 20334: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20330:
20331: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20332: EXIT;
20333: END IF;
20334: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20335: SET ocont_party_id = L_ocont_PARTY_ID(I)
20336: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20337:
20338: if l_last_fetch then

Line 20385: from ams_hz_b2b_mapping_v b2b

20381: cursor c_addr_key is
20382: select b2b.import_source_line_id,
20383: hz_fuzzy_pub.Generate_Key('ADDRESS',null,b2b.address1,b2b.address2,b2b.address3,b2b.address4,b2b.postal_code,null,null)
20384: "add_key"
20385: from ams_hz_b2b_mapping_v b2b
20386: where b2b.import_list_header_id = p_import_list_header_id
20387: and b2b.load_status in ('ACTIVE','RELOAD')
20388: and b2b.address1 is not null;
20389:

Line 20392: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b

20388: and b2b.address1 is not null;
20389:
20390: cursor c_addr_ps is
20391: select b2b.import_source_line_id,max(loc.location_id)
20392: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b
20393: where b2b.import_list_header_id = p_import_list_header_id
20394: and b2b.load_status in ('ACTIVE','RELOAD')
20395: and b2b.address_key is not null
20396: and b2b.org_party_id is not null

Line 20406: from hz_locations loc,ams_hz_b2b_mapping_v b2b

20402: group by b2b.import_source_line_id;
20403:
20404: cursor c_addr is
20405: select b2b.import_source_line_id,max(loc.location_id)
20406: from hz_locations loc,ams_hz_b2b_mapping_v b2b
20407: where b2b.import_list_header_id = p_import_list_header_id
20408: and b2b.load_status in ('ACTIVE','RELOAD')
20409: and b2b.address_key is not null
20410: and b2b.party_location_id is null

Line 20433: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20429:
20430: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20431: EXIT;
20432: END IF;
20433: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20434: SET address_key = L_address_KEY(I)
20435: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20436:
20437: if l_last_fetch then

Line 20461: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20457:
20458: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20459: EXIT;
20460: END IF;
20461: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20462: SET party_location_id = L_PARTY_location_ID(I)
20463: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20464:
20465: if l_last_fetch then

Line 20490: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20486:
20487: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20488: EXIT;
20489: END IF;
20490: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20491: SET party_location_id = L_PARTY_location_ID(I)
20492: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20493:
20494: if l_last_fetch then

Line 20543: from ams_hz_b2b_mapping_v b2b

20539: cursor c_addr_key is
20540: select b2b.import_source_line_id,
20541: hz_fuzzy_pub.Generate_Key('ADDRESS',null,b2b.org_address1,b2b.org_address2,b2b.org_address3,b2b.org_address4,b2b.org_postal_code,null,null)
20542: "add_key"
20543: from ams_hz_b2b_mapping_v b2b
20544: where b2b.import_list_header_id = p_import_list_header_id
20545: and b2b.load_status in ('ACTIVE','RELOAD')
20546: and b2b.org_address1 is not null;
20547:

Line 20550: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b

20546: and b2b.org_address1 is not null;
20547:
20548: cursor c_addr_ps is
20549: select b2b.import_source_line_id,max(loc.location_id)
20550: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b
20551: where b2b.import_list_header_id = p_import_list_header_id
20552: and b2b.load_status in ('ACTIVE','RELOAD')
20553: and b2b.org_address_key is not null
20554: and b2b.org_party_id is not null

Line 20564: from hz_locations loc,ams_hz_b2b_mapping_v b2b

20560: group by b2b.import_source_line_id;
20561:
20562: cursor c_addr is
20563: select b2b.import_source_line_id,max(loc.location_id)
20564: from hz_locations loc,ams_hz_b2b_mapping_v b2b
20565: where b2b.import_list_header_id = p_import_list_header_id
20566: and b2b.load_status in ('ACTIVE','RELOAD')
20567: and b2b.org_address_key is not null
20568: and b2b.org_location_id is null

Line 20591: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20587:
20588: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20589: EXIT;
20590: END IF;
20591: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20592: SET org_address_key = L_org_address_KEY(I)
20593: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20594:
20595: if l_last_fetch then

Line 20619: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20615:
20616: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20617: EXIT;
20618: END IF;
20619: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20620: SET org_location_id = L_org_location_ID(I)
20621: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20622:
20623: if l_last_fetch then

Line 20648: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V

20644:
20645: IF L_IMPORT_SOURCE_LINE_ID.COUNT = 0 AND l_last_fetch THEN
20646: EXIT;
20647: END IF;
20648: FORALL i IN L_IMPORT_SOURCE_LINE_ID.FIRST..L_IMPORT_SOURCE_LINE_ID.LAST UPDATE AMS_HZ_B2B_MAPPING_V
20649: SET org_location_id = L_org_location_ID(I)
20650: WHERE IMPORT_SOURCE_LINE_ID = L_IMPORT_SOURCE_LINE_ID(i);
20651:
20652: if l_last_fetch then

Line 21189: ams_hz_b2b_mapping_v

21185: cursor c_b2b_ph_no is
21186: select IMPORT_SOURCE_LINE_ID ,
21187: hz_phone_number_pkg.transpose(PHONE_COUNTRY_CODE||PHONE_AREA_CODE||PHONE_NUMBER) "TRANS_PHONE_NUMBER"
21188: from
21189: ams_hz_b2b_mapping_v
21190: where import_list_header_id = p_import_list_header_id
21191: and phone_number is not null
21192: and TRANSPOSED_PHONE_NUMBER is null
21193: and load_status in ('ACTIVE','RELOAD');

Line 21291: from ams_hz_b2b_mapping_v

21287:
21288: cursor c_b2bxmlrec is
21289: select org_imp_xml_element_id, add_imp_xml_element_id, ocont_imp_xml_element_id,
21290: cp_imp_xml_element_id, em_imp_xml_element_id
21291: from ams_hz_b2b_mapping_v
21292: where import_list_header_id = p_import_list_header_id
21293: and load_status = 'RELOAD';
21294:
21295: cursor c_b2cxmlrec is

Line 21556: update ams_hz_b2b_mapping_v

21552: p_log_used_by_id => l_import_list_header_id,
21553: p_msg_data => 'LANGUAGE_ENCODING = '||l_LANGUAGE_ENCODING,
21554: p_msg_type => 'DEBUG');
21555:
21556: update ams_hz_b2b_mapping_v
21557: set CUSTOM_PROCESSING_STATUS = 'pre-processing'
21558: where import_list_header_id = l_import_list_header_id;
21559:
21560: return 'SUCCESS';

Line 21650: update ams_hz_b2b_mapping_v

21646: p_log_used_by_id => l_import_list_header_id,
21647: p_msg_data => 'LANGUAGE_ENCODING = '||l_LANGUAGE_ENCODING,
21648: p_msg_type => 'DEBUG');
21649: /*
21650: update ams_hz_b2b_mapping_v
21651: set CUSTOM_PROCESSING_STATUS = 'pre-processing'
21652: where import_list_header_id = l_import_list_header_id;
21653: */
21654: return 'SUCCESS';