[Home] [Help]
PACKAGE BODY: APPS.CN_AGGRT_TRX_PKG
Source
1 PACKAGE BODY cn_aggrt_trx_pkg AS
2 -- $Header: cnagtrxb.pls 120.1 2005/10/13 10:39:01 ymao noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_AGGRT_TRX_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnagtrxb.pls';
6
7 G_LAST_UPDATE_DATE DATE := sysdate;
8 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
9 G_CREATION_DATE DATE := sysdate;
10 G_CREATED_BY NUMBER := fnd_global.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
12
13 G_ROWID VARCHAR2(30);
14 G_PROGRAM_TYPE VARCHAR2(30);
15 g_system_rollup_flag VARCHAR2(1);
16 g_roll_sum_trx_flag VARCHAR2(1);
17 g_srp_validation_flag VARCHAR2(1);
18 g_mode VARCHAR2(30);
19 g_event_log_id NUMBER(15);
20
21 type num_tbl_type is table of number index by binary_integer;
22 type date_tbl_type is table of date index by binary_integer;
23 type str_tbl_type is table of varchar2(30) index by binary_integer;
24
25
26 PROCEDURE aggregate_trx(p_physical_batch_id IN NUMBER) IS
27 g_intel_calc_flag VARCHAR2(1);
28 rep_ids num_tbl_type;
29 header_ids num_tbl_type;
30 rollup_dates date_tbl_type;
31 group_ids num_tbl_type;
32 rev_class_ids num_tbl_type;
33 trx_types str_tbl_type;
34 amounts num_tbl_type;
35 units num_tbl_type;
36 processed_dates date_tbl_type;
37 period_ids num_tbl_type;
38
39 l_start_date DATE;
40 l_end_date DATE;
41 l_start_period_id NUMBER;
42 l_org_id NUMBER;
43
44 CURSOR intel_calc_flag IS
45 SELECT nvl(intelligent_flag, 'N'), org_id
46 FROM cn_calc_submission_batches_all
47 WHERE logical_batch_id = (SELECT logical_batch_id
48 FROM cn_process_batches_all
49 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1);
50
51 cursor sum_trxs is
52 select ch.direct_salesrep_id,
53 ch.processed_period_id,
54 ch.processed_date,
55 nvl(ch.rollup_date, ch.processed_date),
56 ch.comp_group_id,
57 ch.revenue_class_id,
58 ch.trx_type,
59 sum(ch.transaction_amount),
60 sum(ch.quantity)
61 from cn_commission_headers_all ch,
62 cn_process_batches_all pb
63 WHERE pb.physical_batch_id = p_physical_batch_id
64 AND ch.direct_salesrep_id = pb.salesrep_id
65 AND ch.org_id = pb.org_id
66 AND ch.processed_date BETWEEN pb.start_date AND pb.end_date
67 AND ((g_intel_calc_flag = 'N' AND ch.status = 'CLS') OR (g_intel_calc_flag = 'Y' AND ch.status = 'CLS' AND ch.parent_header_id IS NULL))
68 group by ch.direct_salesrep_id,
69 ch.processed_period_id,
70 ch.processed_date,
71 nvl(ch.rollup_date, ch.processed_date),
72 ch.comp_group_id,
73 ch.revenue_class_id,
74 ch.trx_type;
75 BEGIN
76 OPEN intel_calc_flag;
77 FETCH intel_calc_flag INTO g_intel_calc_flag, l_org_id;
78 CLOSE intel_calc_flag;
79
80 g_intel_calc_flag := nvl(g_intel_calc_flag, 'Y');
81
82 open sum_trxs;
83 fetch sum_trxs bulk collect into rep_ids, period_ids, processed_dates, rollup_dates, group_ids, rev_class_ids, trx_types, amounts, units;
84 close sum_trxs;
85
86 IF rep_ids.count > 0 THEN
87 forall i in rep_ids.first..rep_ids.last
88 insert into cn_commission_headers_all
89 (commission_header_id,
90 direct_salesrep_id,
91 processed_date,
92 processed_period_id,
93 trx_type,
94 status,
95 rollup_date,
96 comp_group_id,
97 revenue_class_id,
98 transaction_amount,
99 quantity,
100 pre_processed_code,
101 parent_header_id,
102 creation_date,
103 created_by,
104 org_id)
105 values
106 (cn_commission_headers_s.nextval,
107 rep_ids(i),
108 processed_dates(i),
109 period_ids(i),
110 trx_types(i),
111 'CLS_SUM',
112 rollup_dates(i),
113 group_ids(i),
114 rev_class_ids(i),
115 amounts(i),
116 units(i),
117 'CRPC',
118 -1,
119 sysdate,
120 g_created_by,
121 l_org_id)
122 returning commission_header_id bulk collect INTO header_ids;
123
124 forall i IN rep_ids.first..rep_ids.last
125 UPDATE cn_commission_headers_all
126 SET parent_header_id = header_ids(i),
127 -- clku, update the last updated info
128 last_update_date = G_LAST_UPDATE_DATE,
129 last_updated_by = G_LAST_UPDATED_BY,
130 last_update_login = G_LAST_UPDATE_LOGIN
131 WHERE direct_salesrep_id = rep_ids(i)
132 AND processed_period_id= period_ids(i)
133 AND processed_date = processed_dates(i)
134 AND nvl(rollup_date, processed_date) = rollup_dates(i)
135 AND nvl(comp_group_id, -999999) = nvl(group_ids(i), -999999)
136 AND revenue_class_id = rev_class_ids(i)
137 AND trx_type = trx_types(i)
138 AND ((g_intel_calc_flag = 'N' AND status = 'CLS') OR (g_intel_calc_flag = 'Y' AND status = 'CLS' AND parent_header_id IS NULL))
139 AND org_id = l_org_id;
140 END IF;
141 end;
142
143 END CN_AGGRT_TRX_PKG;