1 PACKAGE arh_dqm_cust_helper AUTHID CURRENT_USER AS
2 /*$Header: ARHDQMAS.pls 115.1 2002/03/28 11:26:15 pkm ship $*/
3
4 FUNCTION Is_cust_role_rel_dqm_pty
5 ( p_ctx_id IN NUMBER,
6 p_cust_account_role_id IN NUMBER,
7 p_status IN VARCHAR2)
8 RETURN VARCHAR2;
9
10 FUNCTION is_cust_acct_in_pty_gt
11 ------------------------------------------------------------------------
12 -- Return Y if the P_CUST_ACCOUNT_ID is already in HZ_MATCHED_PARTIES_GT
13 -- Otherwise N
14 ------------------------------------------------------------------------
15 ( p_ctx_id IN NUMBER,
16 p_cust_account_id IN NUMBER,
17 p_status IN VARCHAR2)
18 RETURN VARCHAR2;
19
20
21 FUNCTION is_cust_role_in_ct_gt
22 ---------------------------------------------------------------------------------------
23 -- Return Y if the P_CUST_ACCOUNR_ROLE_ID is already inserted in HZ_MATCHED_CONTACTS_GT
24 -- Otherwise N
25 ---------------------------------------------------------------------------------------
26 ( p_ctx_id IN NUMBER,
27 p_cust_account_role_id IN NUMBER,
28 p_cust_account_id IN NUMBER,
29 p_status IN VARCHAR2)
30 RETURN VARCHAR2;
31
32
33 FUNCTION Is_acct_site_in_ps_gt
34 ---------------------------------------------------------------------------------------
35 -- Return Y if the P_CUST_ACCT_SITE_ID is already inserted in HZ_MATCHED_PARTY_SITES_GT
36 -- Otherwise N
37 ---------------------------------------------------------------------------------------
38 ( p_ctx_id IN NUMBER,
39 p_cust_acct_site_id IN NUMBER,
40 p_cust_account_id IN NUMBER,
41 p_cur_all IN VARCHAR2,
42 p_status IN VARCHAR2)
43 RETURN VARCHAR2;
44
45 FUNCTION is_as_rel_dqm_pty
46 --------------------------------------------------------------------------------------------
47 -- Return Y if the P_CUST_ACCT_SITE_D is associated with a party_id in HZ_MATCHED_PARTIES_GT
48 -- Otherwise N
49 --------------------------------------------------------------------------------------------
50 ( p_ctx_id IN NUMBER,
51 p_cust_account_id IN NUMBER,
52 p_cust_acct_site_id IN NUMBER,
53 p_cur_all IN VARCHAR2,
54 p_status IN VARCHAR2)
55 RETURN VARCHAR2;
56
57 FUNCTION score_of_rel_ps
58 -------------------------------------------------------------------------------------------------------
59 -- Return the score of the party_site related to a cust_acct_site in HZ_MATCHED_PARTY_SITES_GT if found
60 -- Otherwise -99999
61 -------------------------------------------------------------------------------------------------------
62 ( p_ctx_id IN NUMBER,
63 p_cust_acct_site_id IN NUMBER,
64 p_cur_all IN VARCHAR2,
65 p_status IN VARCHAR2)
66 RETURN NUMBER;
67
68 PROCEDURE ins_as_in_ps_gt
69 -------------------------------------------------------------------------
70 -- Insert in CUST_ACCT_SITE_ID in HZ_MATCHED_PARTY_SITES_GT
71 -- If 1) the cust_acct_site_id is related to a matched party
72 -- 2) the cust_acct_site_id is not yet in HZ_MATCHED_PARTY_SITES_GT
73 -------------------------------------------------------------------------
74 -- CUST_ACCOUNT_ID CUST_ACCT_SITE_ID -PSscore(-1) SEARCH_CONTEXT_ID
75 -------------------------------------------------------------------------
76 ( p_ctx_id IN NUMBER,
77 p_cust_account_id IN NUMBER,
78 p_cust_acct_site_id IN NUMBER,
79 p_cur_all IN VARCHAR2,
80 p_status IN VARCHAR2);
81
82 PROCEDURE ins_ca_car_in_gt
83 -------------------------------------------------------------------
84 -- Treatement for HZ_CUST_ACCOUNT_ROLES
85 -------------------------------------------------------------------
86 ( p_ctx_id IN NUMBER,
87 p_org_contact_id IN NUMBER,
88 p_cur_all IN VARCHAR2,
89 p_status IN VARCHAR2);
90
91 FUNCTION score_rel_party
92 ( p_ctx_id IN NUMBER,
93 p_cust_account_id IN NUMBER,
94 p_status IN VARCHAR2)
95 RETURN NUMBER;
96
97 PROCEDURE find_as_rel_ps
98 -----------------------------------------------------------------------------------------------
99 -- INSERT all the CUST_ACCT_SITE_ID related to the P_PARTY_SITE_ID in HZ_MATCHED_PARTY_SITES_GT
100 -- If necesary insert also the CUST_ACCOUNT_ID related in HZ_MATCHED_PARTIES_GT
101 -----------------------------------------------------------------------------------------------
102 ( p_ctx_id IN NUMBER,
103 p_party_site_id IN NUMBER,
104 p_score IN NUMBER,
105 p_cur_all IN VARCHAR2,
106 p_status IN VARCHAR2);
107
108 FUNCTION is_as_rel_ps_in_ps_gt
109 ------------------------------------------------------------------------------------------------------
110 -- Return Y if the CUST_ACCT_SITE_ID related to the P_PARTY_SITE_ID exist in HZ_MATCHED_PARTY_SITES_GT
111 ------------------------------------------------------------------------------------------------------
112 ( p_ctx_id IN NUMBER,
113 p_party_site_id IN NUMBER,
114 p_cur_all IN VARCHAR2,
115 p_status IN VARCHAR2)
116 RETURN VARCHAR2;
117
118 PROCEDURE as_ps_treatment
119 ------------------------------------------------------------------------
120 -- Cust Account Site / Party Site treatment in HZ_MATCHED_PARTY_SITES_GT
121 ------------------------------------------------------------------------
122 ( p_ctx_id IN NUMBER ,
123 p_cur_all IN VARCHAR2,
124 p_status IN VARCHAR2 );
125
126 PROCEDURE ac_pty_treatment
127 -------------------------------------------------------
128 -- Account / Party Treatement in HZ_MATCHED_PARTIES_GT
129 -------------------------------------------------------
130 ( p_ctx_id IN NUMBER,
131 p_cur_all IN VARCHAR2,
132 p_status IN VARCHAR2);
133
134 PROCEDURE find_all_account_for_party
135 -------------------------------------------------------------------------
136 -- INSERT all_cust_account related to P_PARTY_ID in HZ_MATCHED_PARTIES_GT
137 -------------------------------------------------------------------------
138 ( p_ctx_id IN NUMBER,
139 p_party_id IN NUMBER,
140 p_score IN NUMBER,
141 p_cur_all IN VARCHAR2,
142 p_status IN VARCHAR2);
143
144 PROCEDURE car_oc_treatment
145 ---------------------------------------------------------------
146 -- Treatment for CUST_ACCOUNT_ROLE_ID in HZ_MATCHED_CONTACTS_GT
147 -- Affect HZ_MATCHED_PARTY_SITES_GT and HZ_MATCHED_PARTIES_GT
148 ---------------------------------------------------------------
149 ( p_ctx_id IN NUMBER,
150 p_cur_all IN VARCHAR2,
151 p_status IN VARCHAR2);
152
153 FUNCTION is_ac_rel_pty_in_p_gt
154 ------------------------------------------------------------------------------------------------
155 -- RETURN Y if the P_PARTY_ID has at leat one CUST_ACCT_ID related to it in HZ_MATCHED_PARIES_GT
156 -- Otherwise N
157 ------------------------------------------------------------------------------------------------
158 ( p_ctx_id IN NUMBER,
159 p_party_id IN NUMBER,
160 p_status IN VARCHAR2)
161 RETURN VARCHAR2;
162
163 END;