[Home] [Help]
PACKAGE BODY: APPS.OKE_FIX_MERGE_PUB
Source
1 PACKAGE BODY OKE_FIX_MERGE_PUB AS
2 /*$Header: OKEPMRGB.pls 120.1 2006/01/30 10:46:22 ausmani noship $ */
3
4
5 FUNCTION merge_cust_acct
6 (merge_from_acct VARCHAR2,record_date DATE,program_date DATE)
7 RETURN VARCHAR2 IS
8
9 CURSOR retrieve_account IS
10 SELECT CUSTOMER_ID,DUPLICATE_ID,PROGRAM_UPDATE_DATE
11 FROM RA_CUSTOMER_MERGES
12 WHERE DUPLICATE_ID=merge_from_acct
13 AND PROGRAM_UPDATE_DATE > record_date
14 AND PROGRAM_UPDATE_DATE > program_date
15 ORDER BY PROGRAM_UPDATE_DATE;
16
17
18 l_merge_row retrieve_account%rowtype;
19
20 BEGIN
21
22 OPEN retrieve_account;
23 FETCH retrieve_account INTO l_merge_row;
24 IF retrieve_account%NOTFOUND
25 THEN
26 CLOSE retrieve_account;
27 RETURN merge_from_acct;
28 ELSE
29 CLOSE retrieve_account;
30 RETURN merge_cust_acct(l_merge_row.CUSTOMER_ID,
31 record_date,l_merge_row.PROGRAM_UPDATE_DATE);
32 END IF;
33 END merge_cust_acct;
34
35 FUNCTION merge_cust_acct_site_use
36 (merge_from_site_use VARCHAR2,record_date DATE,program_date DATE)
37 RETURN VARCHAR2 IS
38
39 CURSOR retrieve_account_site_use IS
40 SELECT CUSTOMER_SITE_ID,DUPLICATE_SITE_ID,PROGRAM_UPDATE_DATE
41 FROM RA_CUSTOMER_MERGES
42 WHERE DUPLICATE_SITE_ID=merge_from_site_use
43 AND PROGRAM_UPDATE_DATE > record_date
44 AND PROGRAM_UPDATE_DATE > program_date
45 ORDER BY PROGRAM_UPDATE_DATE;
46
47
48 l_merge_row retrieve_account_site_use%rowtype;
49
50 BEGIN
51
52 OPEN retrieve_account_site_use;
53 FETCH retrieve_account_site_use INTO l_merge_row;
54 IF retrieve_account_site_use%NOTFOUND
55 THEN
56 CLOSE retrieve_account_site_use;
57 RETURN merge_from_site_use;
58 ELSE
59 CLOSE retrieve_account_site_use;
60 RETURN merge_cust_acct_site_use(l_merge_row.CUSTOMER_SITE_ID,
61 record_date,l_merge_row.PROGRAM_UPDATE_DATE);
62 END IF;
63 END merge_cust_acct_site_use;
64
65 PROCEDURE fix_merge_for_contract(k_header_id NUMBER) IS
66
67 CURSOR list_of_parties (k_header_id NUMBER) IS
68 SELECT id,jtot_object1_code,
69 object1_id1,object1_id2,last_update_date
70 FROM okc_k_party_roles_b
71 WHERE DNZ_CHR_ID=k_header_id
72 AND JTOT_OBJECT1_CODE IN
73 ('OKE_CUST_KADMIN','OKE_BILLTO','OKE_CUSTACCT','OKE_MARKFOR','OKE_SHIPTO');
74
75 l_party_role list_of_parties%rowtype;
76 l_final_id VARCHAR2(80);
77
78 BEGIN
79
80 -- iterate through rows in OKC_K_PARTY_ROLES_B
81 -- for each contract
82 OPEN list_of_parties(k_header_id);
83 LOOP
84 FETCH list_of_parties INTO l_party_role;
85 EXIT WHEN list_of_parties%NOTFOUND;
86
87 IF l_party_role.JTOT_OBJECT1_CODE = 'OKE_CUSTACCT' THEN
88 l_final_id:=merge_cust_acct(l_party_role.object1_id1,
89 l_party_role.last_update_date,to_date('01-01-1990','MM-DD-YYYY'));
90
91 UPDATE OKC_K_PARTY_ROLES_B
92 SET OBJECT1_ID1 = l_final_id,
93 LAST_UPDATE_DATE = SYSDATE,
94 LAST_UPDATED_BY = -1
95 WHERE ID = l_party_role.id;
96
97 ELSE
98 l_final_id:=merge_cust_acct_site_use(l_party_role.object1_id1,
99 l_party_role.last_update_date,to_date('01-01-1990','MM-DD-YYYY'));
100
101 UPDATE OKC_K_PARTY_ROLES_B
102 SET OBJECT1_ID1 = l_final_id,
103 LAST_UPDATE_DATE = SYSDATE,
104 LAST_UPDATED_BY = -1
105 WHERE ID = l_party_role.id;
106
107 END IF;
108
109 END LOOP;
110 CLOSE list_of_parties;
111
112 END;
113
114
115 END OKE_FIX_MERGE_PUB;