[Home] [Help]
498: , ASS.INSTALL_SITE_ID
499: , HPS.PARTY_SITE_ID
500: FROM OKL_ASSETS_B ASS
501: , OKL_LEASE_QUOTES_B LSQ
502: , OKL_LEASE_APPLICATIONS_B LAP
503: , HZ_PARTY_SITE_USES HPSU
504: , HZ_PARTY_SITES HPS
505: WHERE ASS.PARENT_OBJECT_CODE = 'LEASEQUOTE'
506: AND LSQ.ID = ASS.PARENT_OBJECT_ID
3931: /*-------------------------------------------------------------
3932: | PROCEDURE
3933: | LAP_ACCOUNT_MERGE
3934: | DESCRIPTION :
3935: | Account merge procedure for the table, OKL_LEASE_APPLICATIONS_B
3936: |
3937: | NOTES:
3938: |--------------------------------------------------------------*/
3939: PROCEDURE LAP_ACCOUNT_MERGE (
3951: RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3952: INDEX BY BINARY_INTEGER;
3953: MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3954:
3955: TYPE ID_LIST_TYPE IS TABLE OF OKL_LEASE_APPLICATIONS_B.ID%TYPE
3956: INDEX BY BINARY_INTEGER;
3957: PRIMARY_KEY_ID_LIST ID_LIST_TYPE;
3958:
3959: TYPE CUST_ACCT_ID_LIST_TYPE IS TABLE OF OKL_LEASE_APPLICATIONS_B.CUST_ACCT_ID%TYPE
3955: TYPE ID_LIST_TYPE IS TABLE OF OKL_LEASE_APPLICATIONS_B.ID%TYPE
3956: INDEX BY BINARY_INTEGER;
3957: PRIMARY_KEY_ID_LIST ID_LIST_TYPE;
3958:
3959: TYPE CUST_ACCT_ID_LIST_TYPE IS TABLE OF OKL_LEASE_APPLICATIONS_B.CUST_ACCT_ID%TYPE
3960: INDEX BY BINARY_INTEGER;
3961: NUM_COL1_ORIG_LIST CUST_ACCT_ID_LIST_TYPE;
3962: NUM_COL1_NEW_LIST CUST_ACCT_ID_LIST_TYPE;
3963:
3961: NUM_COL1_ORIG_LIST CUST_ACCT_ID_LIST_TYPE;
3962: NUM_COL1_NEW_LIST CUST_ACCT_ID_LIST_TYPE;
3963:
3964: TYPE PROSPECT_ADDRESS_ID_LIST_TYPE IS TABLE OF
3965: OKL_LEASE_APPLICATIONS_B.PROSPECT_ADDRESS_ID%TYPE INDEX BY BINARY_INTEGER;
3966: PROSPECT_ADDRESS_ID_LIST PROSPECT_ADDRESS_ID_LIST_TYPE;
3967: PROSPECT_ADD_OLD_ID_LIST PROSPECT_ADDRESS_ID_LIST_TYPE;
3968:
3969: TYPE PROSPECT_ID_LIST_TYPE IS TABLE OF
3966: PROSPECT_ADDRESS_ID_LIST PROSPECT_ADDRESS_ID_LIST_TYPE;
3967: PROSPECT_ADD_OLD_ID_LIST PROSPECT_ADDRESS_ID_LIST_TYPE;
3968:
3969: TYPE PROSPECT_ID_LIST_TYPE IS TABLE OF
3970: OKL_LEASE_APPLICATIONS_B.PROSPECT_ID%TYPE INDEX BY BINARY_INTEGER;
3971: PROSPECT_ID_LIST PROSPECT_ID_LIST_TYPE;
3972: PROSPECT_OLD_ID_LIST PROSPECT_ID_LIST_TYPE;
3973:
3974: l_profile_val VARCHAR2(30);
3975: CURSOR merged_records IS
3976: SELECT distinct CUSTOMER_MERGE_HEADER_ID
3977: , ID
3978: , CUST_ACCT_ID
3979: FROM OKL_LEASE_APPLICATIONS_B yt, ra_customer_merges m
3980: WHERE (yt.CUST_ACCT_ID = m.DUPLICATE_ID)
3981: AND m.process_flag = 'N'
3982: AND m.request_id = req_id
3983: AND m.set_number = set_num;
4012: IF process_mode='LOCK' THEN
4013: NULL;
4014: ELSE
4015: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
4016: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OKL_LEASE_APPLICATIONS_B',FALSE);
4017: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
4018: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
4019:
4020: OPEN merged_records;
4043: SELECT PROSPECT_ID
4044: , PROSPECT_ADDRESS_ID
4045: INTO PROSPECT_OLD_ID_LIST(I)
4046: , PROSPECT_ADD_OLD_ID_LIST(I)
4047: FROM OKL_LEASE_APPLICATIONS_B
4048: WHERE ID = PRIMARY_KEY_ID_LIST(I);
4049: IF(PROSPECT_ADD_OLD_ID_LIST(I) IS NOT NULL)
4050: THEN
4051: PROSPECT_ADDRESS_ID_LIST(I) := get_new_party_site(PROSPECT_ADD_OLD_ID_LIST(I), NUM_COL1_NEW_LIST(I));
4166: LAST_UPDATE_DATE,
4167: LAST_UPDATED_BY
4168: )VALUES(
4169: HZ_CUSTOMER_MERGE_LOG_s.nextval,
4170: 'OKL_LEASE_APPLICATIONS_B',
4171: MERGE_HEADER_ID_LIST(I),
4172: PRIMARY_KEY_ID_LIST(I),
4173: NUM_COL1_ORIG_LIST(I),
4174: NUM_COL1_NEW_LIST(I),
4185: hz_utility_pub.LAST_UPDATED_BY);
4186:
4187: END IF;
4188: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
4189: UPDATE OKL_LEASE_APPLICATIONS_B yt SET
4190: CUST_ACCT_ID=NUM_COL1_NEW_LIST(I)
4191: , PROSPECT_ID = PROSPECT_ID_LIST(I)
4192: , PROSPECT_ADDRESS_ID = PROSPECT_ADDRESS_ID_LIST(I)
4193: , LAST_UPDATE_DATE=SYSDATE