1 Package AS_SC_DENORM AUTHID CURRENT_USER AS
2 /* $Header: asxopdps.pls 115.28 2003/05/23 23:44:24 xding ship $ */
3
4 --
5 -- HISTORY
6 -- 04/07/2000 NACHARYA Created
7 -- 12/22/2000 SOLIN Bug 1549115
8 -- Add a new column BUSINESS_GROUP_NAME in
9 -- AS_SALES_CREDITS_DENORM
10 -- 06/20/2001 SMALLINA Bug 1836786
11
12 -- Constants
13 G_PKG_NAME Constant VARCHAR2(30):='AS_SC_REFRESH_DENORM';
14 G_FILE_NAME Constant VARCHAR2(12):='asxopdps.pls';
15 G_COMMIT_SIZE Constant Number := 10000;
16
17 -- The following two variables are used to indicate debug message is
18 -- written to message stack(G_DEBUG_TRIGGER) or to log/output file
19 -- (G_DEBUG_CONCURRENT).
20 G_DEBUG_CONCURRENT CONSTANT NUMBER := 1;
21 G_DEBUG_TRIGGER CONSTANT NUMBER := 2;
22
23 -- Global variables
24 G_Debug Boolean := True;
25 G_LANG VARCHAR2(10) := userenv('LANG');
26 G_PREFERRED_CURRENCY as_period_rates.TO_CURRENCY%Type := FND_PROFILE.VALUE('AS_PREFERRED_CURRENCY');
27 G_CONVERSION_TYPE as_period_rates.conversion_type%Type :=FND_PROFILE.VALUE('AS_MC_DAILY_CONVERSION_TYPE');
28 G_PERIOD_TYPE as_period_rates.PERIOD_TYPE%Type := FND_PROFILE.VALUE('AS_DEFAULT_PERIOD_TYPE');
29
30 Type sales_credit_id_list is Table of as_sales_credits_denorm.sales_credit_id%type Index by Binary_integer;
31 Type sales_group_name_list is Table of as_sales_credits_denorm.sales_group_name%type Index by Binary_integer;
32 Type sales_rep_name_list is Table of as_sales_credits_denorm.sales_rep_name%type Index by Binary_integer;
33 Type employee_number_list is Table of as_sales_credits_denorm.employee_number%type Index by Binary_integer;
34 Type customer_name_list is Table of as_sales_credits_denorm.customer_name%type Index by Binary_integer;
35 Type competitor_name_list is Table of as_sales_credits_denorm.competitor_name%type Index by Binary_integer;
36 Type owner_person_name_list is Table of as_sales_credits_denorm.owner_person_name%type Index by Binary_integer;
37 Type owner_last_name_list is Table of as_sales_credits_denorm.owner_last_name%type Index by Binary_integer;
38 Type owner_first_name_list is Table of as_sales_credits_denorm.owner_first_name%type Index by Binary_integer;
39 Type owner_group_name_list is Table of as_sales_credits_denorm.owner_group_name%type Index by Binary_integer;
40 Type customer_category_list is Table of as_sales_credits_denorm.customer_category%type Index by Binary_integer;
41 Type customer_category_code_list is Table of as_sales_credits_denorm.customer_category_code%type Index by Binary_integer;
42 Type sales_stage_list is Table of as_sales_credits_denorm.sales_stage%type Index by Binary_integer;
43 Type status_list is Table of as_sales_credits_denorm.status%type Index by Binary_integer;
44 Type last_name_list is Table of as_sales_credits_denorm.last_name%type Index by Binary_integer;
45 Type first_name_list is Table of as_sales_credits_denorm.first_name%type Index by Binary_integer;
46 Type interest_type_list is Table of as_sales_credits_denorm.interest_type%type Index by Binary_integer;
47 Type primary_interest_code_list is Table of as_sales_credits_denorm.primary_interest_code%type Index by Binary_integer;
48 Type secondary_interest_code_list is Table of as_sales_credits_denorm.secondary_interest_code%type Index by Binary_integer;
49 Type uom_description_list is Table of as_sales_credits_denorm.uom_description%type Index by Binary_integer;
50 Type item_description_list is Table of as_sales_credits_denorm.item_description%type Index by Binary_integer;
51 Type party_type_list is Table of as_sales_credits_denorm.party_type%type Index by Binary_integer;
52 Type partner_cust_name_list is Table of as_sales_credits_denorm.partner_customer_name%type Index by Binary_integer;
53 Type opp_created_name_list is Table of as_sales_credits_denorm.opportunity_created_name%type Index by Binary_integer;
54 Type opp_last_upd_name_list is Table of as_sales_credits_denorm.opportunity_last_updated_name%type Index by Binary_integer;
55 Type close_reason_men_list is Table of Varchar2(80) Index by Binary_integer;
56 Type business_group_name_list is Table of Varchar2(60) Index by Binary_integer;
57 Type close_reason_list is Table of as_leads_all.close_reason%type Index by Binary_integer;
58 Type rolling_frcst_flg_list is Table of as_sales_credits_denorm.rolling_forecast_flag%type Index by Binary_integer;
59 Type frcst_date_list is Table of as_sales_credits_denorm.forecast_date%type Index by Binary_integer;
60 Type opp_creation_date_list is Table of as_leads_all.creation_date%type Index by Binary_integer;
61 Type opp_created_by_list is Table of as_leads_all.created_by%type Index by Binary_integer;
62 Type attribute_category_list is Table of Varchar2(30) Index by Binary_integer;
63 Type attribute_list is Table of Varchar2(150) Index by Binary_integer;
64
65 scd_attribute_category attribute_category_list;
66 scd_attribute1 attribute_list;
67 scd_attribute2 attribute_list;
68 scd_attribute3 attribute_list;
69 scd_attribute4 attribute_list;
70 scd_attribute5 attribute_list;
71 scd_attribute6 attribute_list;
72 scd_attribute7 attribute_list;
73 scd_attribute8 attribute_list;
74 scd_attribute9 attribute_list;
75 scd_attribute10 attribute_list;
76 scd_attribute11 attribute_list;
77 scd_attribute12 attribute_list;
78 scd_attribute13 attribute_list;
79 scd_attribute14 attribute_list;
80 scd_attribute15 attribute_list;
81 scd_close_reason close_reason_list;
82 scd_frcst_date frcst_date_list;
83 scd_rolling_frcst_flg rolling_frcst_flg_list;
84 scd_opp_creation_date opp_creation_date_list;
85 scd_opp_created_by opp_created_by_list;
86 scd_opp_created_name opp_created_name_list;
87 scd_opp_last_upd_name opp_created_name_list;
88 scd_opp_last_upd_date opp_creation_date_list;
89 scd_opp_last_upd_by opp_created_by_list;
90
91 scd_sales_credit_id sales_credit_id_list;
92 scd_close_reason_men close_reason_men_list;
93 scd_business_group_name business_group_name_list;
94 scd_partner_cust_name partner_cust_name_list;
95 scd_sales_group_name sales_group_name_list;
96 scd_sales_rep_name sales_rep_name_list;
97 scd_employee_number employee_number_list;
98 scd_customer_name customer_name_list;
99 scd_competitor_name competitor_name_list;
100 scd_owner_group_name owner_group_name_list;
101 scd_owner_person_name owner_person_name_list;
102 scd_owner_last_name owner_last_name_list;
103 scd_owner_first_name owner_first_name_list;
104 scd_customer_category customer_category_list;
105 scd_customer_category_code customer_category_code_list;
106 scd_sales_stage sales_stage_list;
107 scd_status status_list;
108 scd_last_name last_name_list;
109 scd_first_name first_name_list;
110 scd_interest_type interest_type_list;
111 scd_primary_interest_code primary_interest_code_list;
112 scd_secondary_interest_code secondary_interest_code_list;
113 scd_uom_description uom_description_list;
114 scd_item_description item_description_list;
115 scd_party_type party_type_list;
116
117 Procedure Populate_as_period_days(
118 ERRBUF OUT NOCOPY VARCHAR2,
119 RETCODE OUT NOCOPY VARCHAR2,
120 p_debug_mode IN VARCHAR2,
121 p_trace_mode IN VARCHAR2);
122
123 Procedure insert_scd (ERRBUF OUT NOCOPY Varchar2,
124 RETCODE OUT NOCOPY Varchar2,
125 p_cnt OUT NOCOPY Number);
126
127 Procedure Main(ERRBUF OUT NOCOPY Varchar2,
128 RETCODE OUT NOCOPY Varchar2,
129 p_mode IN Number,
130 p_debug_mode IN Varchar2,
131 p_trace_mode IN Varchar2);
132
133 Procedure Clear_snapshots;
134
135 End AS_SC_DENORM;