[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| based on the parameters selected. |
| |
+===========================================================================*/
PROCEDURE POPULATE_DATA (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_party_type IN VARCHAR2 ,
p_from_party_code IN VARCHAR2 ,
p_to_party_code IN VARCHAR2 ,
p_dummy IN NUMBER DEFAULT NULL,
p_site_use_code IN VARCHAR2 ,
p_created_n_days IN NUMBER ,
p_updated_n_days IN NUMBER
)IS
l_SQLQuery VARCHAR2(12000);
l_CustSQLQuery VARCHAR2(12000) := ' SELECT '||
'site_uses.site_use_id source_id,'||
'party.party_id party_id, '||
'party_site.party_site_id party_site_id, '||
'acct_site.cust_account_id custAcctId,'||
'site_uses.site_use_code partnrtype,'||
'party.party_name party_name,'||
'decode(party.party_type,''ORGANIZATION'',party.organization_name_phonetic,person_profile.person_name_phonetic) alternate_name,'||
'party.tax_reference tax_reference,'||
'loc.address1 address1,'||
'loc.address2 address2,'||
'loc.address3 address3,'||
'loc.address4 address4,'||
'loc.city city,'||
'loc.state state,'||
'loc.country country,'||
'loc.postal_code postal_code,'||
'acct.account_number acct_number,'||
'acct.account_name acct_name,'||
'party_site.party_site_number site_number,'||
'acct.attribute1 acct_attribute1,'||
'acct.attribute2 acct_attribute2,'||
'acct.attribute3 acct_attribute3,'||
'acct.attribute4 acct_attribute4,'||
'acct.attribute5 acct_attribute5,'||
'acct.attribute6 acct_attribute6,'||
'acct.attribute7 acct_attribute7,'||
'acct.attribute8 acct_attribute8,'||
'acct.attribute9 acct_attribute9,'||
'acct.attribute10 acct_attribute10,'||
'acct.attribute11 acct_attribute11,'||
'acct.attribute12 acct_attribute12,'||
'acct.attribute13 acct_attribute13,'||
'acct.attribute14 acct_attribute14,'||
'acct.attribute15 acct_attribute15,'||
'acct_site.attribute1 acct_site_attribute1,'||
'acct_site.attribute2 acct_site_attribute2,'||
'acct_site.attribute3 acct_site_attribute3,'||
'acct_site.attribute4 acct_site_attribute4,'||
'acct_site.attribute5 acct_site_attribute5,'||
'acct_site.attribute6 acct_site_attribute6,'||
'acct_site.attribute7 acct_site_attribute7,'||
'acct_site.attribute8 acct_site_attribute8,'||
'acct_site.attribute9 acct_site_attribute9,'||
'acct_site.attribute10 acct_site_attribute10,'||
'acct_site.attribute11 acct_site_attribute11,'||
'acct_site.attribute12 acct_site_attribute12,'||
'acct_site.attribute13 acct_site_attribute13,'||
'acct_site.attribute14 acct_site_attribute14,'||
'acct_site.attribute15 acct_site_attribute15, ' ||
'acct_site.org_id operating_unit,' ||
'site_uses.primary_flag address_type ' ||
' FROM '||
'hz_cust_site_uses_all site_uses,'||
'hz_cust_acct_sites_all acct_site,'||
'hz_cust_accounts acct,'||
'hz_party_sites party_site,'||
'hz_parties party,'||
'hz_locations loc ,'||
'hz_Person_profiles person_profile '||
'WHERE '||
' site_uses.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID'||
' AND acct_site.PARTY_SITE_ID = party_site.PARTY_SITE_ID'||
' AND party_site.PARTY_ID = party.PARTY_ID'||
' AND loc.LOCATION_ID = party_site.LOCATION_ID'||
' AND acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID'||
' AND acct.STATUS = ''A'' '||
' AND party.PARTY_ID = person_profile.PARTY_ID(+) ';
'( SELECT hr.object_id FROM hz_relationships hr,' ||
' hz_Parties hp,' ||
'hz_contact_points hcp,' ||
' hz_org_contacts hoc' ||
' WHERE '||
' hr.subject_table_name = ''HZ_PARTIES'' ' ||
' AND hr.object_table_name =''HZ_PARTIES'' ' ||
' AND hr.directional_flag = ''F'' ' ||
' AND hr.relationship_type = ''CONTACT_OF'' '||
' AND hr.subject_id = hp.party_id '||
' AND hcp.owner_table_id = hr.party_id '||
' AND hoc.party_relationship_id = hr.relationship_id '; --TCA view removal ends
l_CarrierSQLQuery VARCHAR2(12000) := ' SELECT '||
'to_number(null) source_id,'||
'party.party_id party_id, '||
'party_site.party_site_id party_site_id, '||
'to_number(null) custAcctId,'||
'''CARRIER'' partnrtype,'||
'party.party_name party_name,'||
'party.organization_name_phonetic alternate_name,'||
'party.tax_reference tax_reference,'||
'loc.address1 address1,'||
'loc.address2 address2,'||
'loc.address3 address3,'||
'loc.address4 address4,'||
'loc.city city,'||
'loc.state state,'||
'loc.country country,'||
'loc.postal_code postal_code,'||
'to_char(null) acct_number,'||
'to_char(null) acct_name,'||
'party_site.party_site_number site_number,'||
'party.attribute1 acct_attribute1,'||
'party.attribute2 acct_attribute2,'||
'party.attribute3 acct_attribute3,'||
'party.attribute4 acct_attribute4,'||
'party.attribute5 acct_attribute5,'||
'party.attribute6 acct_attribute6,'||
'party.attribute7 acct_attribute7,'||
'party.attribute8 acct_attribute8,'||
'party.attribute9 acct_attribute9,'||
'party.attribute10 acct_attribute10,'||
'party.attribute11 acct_attribute11,'||
'party.attribute12 acct_attribute12,'||
'party.attribute13 acct_attribute13,'||
'party.attribute14 acct_attribute14,'||
'party.attribute15 acct_attribute15,'||
'party_site.attribute1 acct_site_attribute1,'||
'party_site.attribute2 acct_site_attribute2,'||
'party_site.attribute3 acct_site_attribute3,'||
'party_site.attribute4 acct_site_attribute4,'||
'party_site.attribute5 acct_site_attribute5,'||
'party_site.attribute6 acct_site_attribute6,'||
'party_site.attribute7 acct_site_attribute7,'||
'party_site.attribute8 acct_site_attribute8,'||
'party_site.attribute9 acct_site_attribute9,'||
'party_site.attribute10 acct_site_attribute10,'||
'party_site.attribute11 acct_site_attribute11,'||
'party_site.attribute12 acct_site_attribute12,'||
'party_site.attribute13 acct_site_attribute13,'||
'party_site.attribute14 acct_site_attribute14,'||
'party_site.attribute15 acct_site_attribute15,' ||
' null, ' ||
'party_site.identifying_address_flag address_type ' ||
' FROM '||
'hz_party_sites party_site,'||
'hz_parties party,'||
'hz_locations loc ,'||
'wsh_carriers_v wsh_car '||
'WHERE '||
' party_site.party_id = party.party_id'||
' AND wsh_car.ACTIVE = ''A'' '||
' AND party.party_id = wsh_car.carrier_id'||
' AND loc.location_id = party_site.location_id';
SELECT
HP.party_name
FROM
hz_parties HP,
HZ_CUST_ACCOUNTS HC
WHERE
HP.PARTY_ID = HC.PARTY_ID AND
hc.cust_account_id = TO_NUMBER(c_account_id);
SELECT
HP.PARTY_NAME
FROM
WSH_CARRIERS WC,
HZ_PARTIES HP
WHERE
HP.PARTY_ID = WC.CARRIER_ID AND
wc.freight_code = c_freight_code;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATED_N_DAYS',P_UPDATED_N_DAYS);
IF p_updated_n_days IS NOT NULL THEN
IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
l_Party_Condn4Tab(1).g_number_val := p_updated_n_days;
l_Party_Condn4Tab(1).g_Bind_Literal := ':b_updated_n_days';
l_Carrier_Condn3Tab(1).g_number_val := p_updated_n_days;
l_Carrier_Condn3Tab(1).g_Bind_Literal := ':b_updated_n_days';
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND (SITE_USES.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, l_ContactQuery || ' and ( hoc.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Party_Condn4Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND (PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, l_ContactQuery || ' and ( hoc.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ',l_Carrier_Condn3Tab, 'NUMBER');
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Carrier_Condn3Tab, 'NUMBER');
select WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
into
l_num_ReqCtrl_tab(j)
from dual;
select WSH_ITM_PARTIES_S.NEXTVAL
into
l_num_itmpartyID_tab(l_num_itmpartyID_tab.COUNT)
from dual;
SELECT LANGUAGE_CODE INTO l_LanguageCode
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B';
WSH_DEBUG_SV.LOG (l_module_name, 'Number of Request Controls to be inserted : ' , l_num_ReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
INSERT INTO WSH_ITM_REQUEST_CONTROL(
REQUEST_CONTROL_ID,
APPLICATION_ID,
LANGUAGE_CODE,
PROCESS_FLAG,
SERVICE_TYPE_CODE,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES(
l_num_ReqCtrl_tab(i),
222,
l_LanguageCode,
0,
'PARTY_SYNC',
l_varchar_rcAttrib1_tab(i),
l_varchar_rcAttrib2_tab(i),
l_varchar_rcAttrib3_tab(i),
l_varchar_rcAttrib4_tab(i),
l_varchar_rcAttrib5_tab(i),
l_varchar_rcAttrib6_tab(i),
l_varchar_rcAttrib7_tab(i),
l_varchar_rcAttrib8_tab(i),
l_varchar_rcAttrib9_tab(i),
l_varchar_rcAttrib10_tab(i),
l_varchar_rcAttrib11_tab(i),
l_varchar_rcAttrib12_tab(i),
l_varchar_rcAttrib13_tab(i),
l_varchar_rcAttrib14_tab(i),
l_varchar_rcAttrib15_tab(i),
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);
WSH_DEBUG_SV.LOG (l_module_name, 'Number of Parties to be inserted : ' , l_num_PartyReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
INSERT INTO WSH_ITM_PARTIES (
PARTY_ID,
CUST_SITE_USE_ID,
REQUEST_CONTROL_ID,
PARTY_NAME,
ALTERNATE_NAME,
TAX_REFERENCE,
PARTY_TYPE,
PARTY_ADDRESS1,
PARTY_ADDRESS2,
PARTY_ADDRESS3,
PARTY_ADDRESS4,
PARTY_CITY,
PARTY_STATE,
PARTY_COUNTRY_CODE,
POSTAL_CODE,
ORIGINAL_SYSTEM_REFERENCE,
ACCOUNT_NUMBER,
ACCOUNT_NAME,
PARTY_SITE_NUMBER,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OPERATING_UNIT,
ADDRTYPE
)
VALUES(
l_num_itmpartyID_tab(i),
l_num_sourceID_tab(i),
l_num_PartyReqCtrl_tab(i),
l_varchar_PartyName_tab(i),
l_varchar_AlternateName_tab(i),
l_varchar_TaxRef_tab(i),
l_varchar_PartnrType_tab(i),
l_varchar_Address1_tab(i),
l_varchar_Address2_tab(i),
l_varchar_Address3_tab(i),
l_varchar_Address4_tab(i),
l_varchar_City_tab(i),
l_varchar_State_tab(i),
l_varchar_Country_tab(i),
l_varchar_PostalCode_tab(i),
l_num_hzpartySiteID_tab(i),
l_varchar_AcctNumber_tab(i),
l_varchar_AcctName_tab(i),
l_varchar_SiteNumber_tab(i),
l_varchar_Attrib1_tab(i),
l_varchar_Attrib2_tab(i),
l_varchar_Attrib3_tab(i),
l_varchar_Attrib4_tab(i),
l_varchar_Attrib5_tab(i),
l_varchar_Attrib6_tab(i),
l_varchar_Attrib7_tab(i),
l_varchar_Attrib8_tab(i),
l_varchar_Attrib9_tab(i),
l_varchar_Attrib10_tab(i),
l_varchar_Attrib11_tab(i),
l_varchar_Attrib12_tab(i),
l_varchar_Attrib13_tab(i),
l_varchar_Attrib14_tab(i),
l_varchar_Attrib15_tab(i),
--num_orgId_tab(i),
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_operating_unit(i),
l_address_type(i)
);
/* Bug 7297690 Added check to determine whether Contacts for this party site is already being inserted into wsh_itm_party_contacts
- For each party site this query will be fired n no of times, n is no of usage of this site (billto, shipto etc)
- This needs to be reduced to one, since when the party site is queried first time, contacts will be added
and for all other hits contact will not be inserted since it is inserted first time itself. */
IF NOT l_party_site_id_cache.Exists(l_num_hzpartySiteID_tab(i)) THEN
l_party_site_id_cache(l_num_hzpartySiteID_tab(i)) := 'Y';
SELECT DISTINCT hr.party_id,
hp.party_name,
nvl(hoc.job_title,hoc.job_title_code),
hoc.org_contact_id, -- Added in Bug 7297690
hcar.attribute1,
hcar.attribute2,
hcar.attribute3,
hcar.attribute4,
hcar.attribute5,
hcar.attribute6,
hcar.attribute7,
hcar.attribute8,
hcar.attribute9,
hcar.attribute10,
hcar.attribute11,
hcar.attribute12,
hcar.attribute13,
hcar.attribute14,
hcar.attribute15
BULK COLLECT INTO
l_num_partyrel_tab,
l_varchar_CtPartyName_tab,
l_varchar_CtPointType_tab,
l_org_contact_id_tab, -- Added in Bug 7297690
l_varchar_Attrib1_tab,
l_varchar_Attrib2_tab,
l_varchar_Attrib3_tab,
l_varchar_Attrib4_tab,
l_varchar_Attrib5_tab,
l_varchar_Attrib6_tab,
l_varchar_Attrib7_tab,
l_varchar_Attrib8_tab,
l_varchar_Attrib9_tab,
l_varchar_Attrib10_tab,
l_varchar_Attrib11_tab,
l_varchar_Attrib12_tab,
l_varchar_Attrib13_tab,
l_varchar_Attrib14_tab,
l_varchar_Attrib15_tab
from hz_relationships hr, --TCA view removal Starts
hz_Parties hp,
hz_org_contacts hoc,
hz_cust_account_roles hcar
where hr.subject_table_name = 'HZ_PARTIES'
and hr.object_table_name = 'HZ_PARTIES'
and hr.Directional_flag = 'F'
and object_id = l_num_hzpartyID_tab(i)
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = hp.party_id
and hoc.party_relationship_id = hr.relationship_id
and nvl(hoc.party_site_id,l_num_hzpartySiteID_tab(i)) = l_num_hzpartySiteID_tab(i)
and hcar.party_id(+) = hr.party_id; -- TCA view removal Ends
WSH_DEBUG_SV.LOG (l_module_name,'Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
SELECT email_address into l_varchar_ctEmail_tab(j) FROM
hz_contact_points where contact_point_type ='EMAIL'
and owner_table_id = l_num_partyrel_tab(j)
--added for bug 6391747
and owner_table_name = 'HZ_PARTIES'
and primary_flag ='Y';
SELECT phone_number into l_varchar_ctPhone_tab(j) FROM
hz_contact_points where contact_point_type ='PHONE'
AND owner_table_id = l_num_partyrel_tab(j)
--added for bug 6391747
AND owner_table_name = 'HZ_PARTIES'
AND phone_line_type= 'GEN'
and primary_flag ='Y';
SELECT phone_number into l_varchar_ctFax_tab(j) FROM
hz_contact_points where contact_point_type ='PHONE'
AND owner_table_id = l_num_partyrel_tab(j)
--added for bug 6391747
AND owner_table_name = 'HZ_PARTIES'
and phone_line_type= 'FAX'
and primary_flag ='Y';
WSH_DEBUG_SV.LOG (l_module_name,' Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
WSH_DEBUG_SV.LOG (l_module_name,'Party Contact being inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
insert into wsh_itm_party_contacts
(
PARTY_ID ,
NAME ,
JOB_TITLE ,
EMAIL ,
PHONE,
FAX ,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_num_itmpartyID_tab(i),
l_varchar_CtPartyName_tab(j),
l_varchar_CtPointType_tab(j),
l_varchar_ctEmail_tab(j),
l_varchar_ctPhone_tab(j),
l_varchar_ctFax_tab(j),
l_varchar_Attrib1_tab(j),
l_varchar_Attrib2_tab(j),
l_varchar_Attrib3_tab(j),
l_varchar_Attrib4_tab(j),
l_varchar_Attrib5_tab(j),
l_varchar_Attrib6_tab(j),
l_varchar_Attrib7_tab(j),
l_varchar_Attrib8_tab(j),
l_varchar_Attrib9_tab(j),
l_varchar_Attrib10_tab(j),
l_varchar_Attrib11_tab(j),
l_varchar_Attrib12_tab(j),
l_varchar_Attrib13_tab(j),
l_varchar_Attrib14_tab(j),
l_varchar_Attrib15_tab(j),
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);