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 2657: update ams_hz_b2b_mapping_v b2b

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

Line 2667: update ams_hz_b2b_mapping_v b2b

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

Line 2673: update ams_hz_b2b_mapping_v

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

Line 2678: update ams_hz_b2b_mapping_v

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

Line 4762: FROM AMS_HZ_B2B_MAPPING_V

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

Line 7734: from ams_hz_b2b_mapping_v where import_source_line_id = p_import_source_line_id;

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

Line 8128: AMS_HZ_B2B_MAPPING_V B2B

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

Line 8896: FROM AMS_HZ_B2B_MAPPING_V

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

Line 10022: FROM ams_hz_b2b_mapping_v

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

Line 11323: FROM AMS_HZ_B2B_MAPPING_V

11319: DECODE(GREATEST(LENGTH(nvl(LTRIM(RTRIM(VEHICLE_RESPONSE_CODE)),'x')),30) - 30,0,' ','VEHICLE_RESPONSE_CODE :'||l_invalid_size||'(30)') VEHICLE_RESPONSE_CODE,
11320: 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,
11321: DECODE(GREATEST(lengthb(nvl(LTRIM(RTRIM(ORIG_SYSTEM_REFERENCE)),'x')),240) -240,0,' ','ORIG_SYSTEM_REFERENCE :'||l_invalid_size
11322: ||'(240)') ORIG_SYSTEM_REFERENCE
11323: FROM AMS_HZ_B2B_MAPPING_V
11324: WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id
11325: and load_status = 'ERROR'
11326: AND
11327: (

Line 13950: update ams_hz_b2b_mapping_v b2b

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

Line 13960: update ams_hz_b2b_mapping_v b2b

13956: and party_id is not null
13957: and org_party_id is null;
13958:
13959: --if party_id is of type organization then update org_party_id with party_id
13960: update ams_hz_b2b_mapping_v b2b
13961: 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
13962: where import_list_header_id = p_import_list_header_id
13963: and party_id is not null
13964: and org_party_id is null;

Line 13966: update ams_hz_b2b_mapping_v

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

Line 13971: update ams_hz_b2b_mapping_v

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

Line 17618: Insert into AMS_HZ_B2B_MAPPING_V(

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

Line 18555: from ams_hz_b2b_mapping_v b2b

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

Line 18566: from ams_hz_b2b_mapping_v b2b

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

Line 18581: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18600: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18616: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

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

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

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

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

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

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

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

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

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

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

Line 18824: from ams_hz_b2b_mapping_v b2b

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

Line 18836: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 18851: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18871: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18888: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

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

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

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

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

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

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

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

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

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

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

Line 19696: from ams_hz_b2b_mapping_v b2b

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

Line 19706: from ams_hz_b2b_mapping_v b2b

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

Line 19719: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 19736: ams_hz_b2b_mapping_v b2b

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

Line 19776: ams_hz_b2b_mapping_v b2b

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

Line 19808: ams_hz_b2b_mapping_v b2b

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

Line 19840: from ams_hz_b2b_mapping_v

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 20074: from ams_hz_b2b_mapping_v b2b

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

Line 20083: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 20100: ams_hz_b2b_mapping_v b2b

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

Line 20140: ams_hz_b2b_mapping_v b2b

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

Line 20172: ams_hz_b2b_mapping_v b2b

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

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

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

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

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

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

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

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

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

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

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

Line 20386: from ams_hz_b2b_mapping_v b2b

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

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

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

Line 20407: from hz_locations loc,ams_hz_b2b_mapping_v b2b

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

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

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

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

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

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

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

Line 20544: from ams_hz_b2b_mapping_v b2b

20540: cursor c_addr_key is
20541: select b2b.import_source_line_id,
20542: 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)
20543: "add_key"
20544: from ams_hz_b2b_mapping_v b2b
20545: where b2b.import_list_header_id = p_import_list_header_id
20546: and b2b.load_status in ('ACTIVE','RELOAD')
20547: and b2b.org_address1 is not null;
20548:

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

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

Line 20565: from hz_locations loc,ams_hz_b2b_mapping_v b2b

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

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

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

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

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

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

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

Line 21190: ams_hz_b2b_mapping_v

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

Line 21292: from ams_hz_b2b_mapping_v

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

Line 21557: update ams_hz_b2b_mapping_v

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

Line 21651: update ams_hz_b2b_mapping_v

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