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;