eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QPR_DML_PVT

Source


1 PACKAGE BODY QPR_DML_PVT as
2 /* $Header: QPRDMLSB.pls 120.3 2007/12/03 15:09:11 bhuchand noship $ */
3  procedure LOADDMLPROG(awname in varchar2) is
4 str1 varchar2(10000);
5 str2 varchar2(10000);
6 str3 varchar2(10000);
7 
8 begin
9 FND_FILE.PUT_LINE(FND_FILE.LOG,'Load DML Programs in AW '||awname);
10 
11 dbms_aw.execute('aw attach '||awname||' rw');
12 
13 FND_FILE.PUT_LINE(FND_FILE.LOG,'AW attached in RW Mode ');
14 
15 str1 := 'define regression program;program;';
16 str1 := str1 || 'argument pricePlan text;';
17 str1 := str1 || 'argument prd text;';
18 str1 := str1 || 'argument cus text;';
19 str1 := str1 || 'argument psg text;';
20 str1 := str1 || 'variable prdDim text;';
21 str1 := str1 || 'variable cusDim text;';
22 str1 := str1 || 'variable timDim text;';
23 str1 := str1 || 'variable psgDim text;';
24 str1 := str1 || 'variable ordDim text;';
25 str1 := str1 || 'variable ordlevel text;';
26 str1 := str1 || 'variable salesDataCube text;';
27 str1 := str1 || 'variable ordQtyMeas text;';
28 str1 := str1 || 'variable grossRevMeas text;';
29 str1 := str1 || 'variable counter integer;';
30 str1 := str1 || 'variable quantity decimal;';
31 str1 := str1 || 'variable revenue decimal;';
32 str1 := str1 || 'variable qtySum decimal;';
33 str1 := str1 || 'variable revSum decimal;';
34 str1 := str1 || 'variable qtySqr decimal;';
35 str1 := str1 || 'variable qtyRevPrd decimal;';
36 str1 := str1 || 'variable slope decimal;';
37 str1 := str1 || 'variable intercept decimal;';
38 str1 := str1 || 'counter = 0;';
39 str1 := str1 || 'qtySum = 0;';
40 str2 := 'revSum = 0;';
41 str2 := str2 || 'qtySqr = 0;';
42 str2 := str2 || 'qtyRevPrd = 0;';
43 str2 := str2 || 'slope = 0;';
44 str2 := str2 || 'intercept = 0;';
45 str2 := str2 || 'SQL SELECT dim_code FROM qpr_dimensions WHERE price_plan_id = to_number(:pricePlan) and  dim_ppa_code = ''TIM'' into :timDim;';
46 str2 := str2 || 'SQL SELECT dim_code FROM qpr_dimensions WHERE price_plan_id  = to_number(:pricePlan) and dim_ppa_code = ''PRD'' into :prdDim;';
47 str2 := str2 || 'SQL SELECT dim_code FROM qpr_dimensions WHERE price_plan_id  = to_number(:pricePlan) and dim_ppa_code = ''CUS'' into :cusDim;';
48 str2 := str2 || 'SQL SELECT dim_code FROM qpr_dimensions WHERE price_plan_id  = to_number(:pricePlan) and dim_ppa_code = ''PSG'' into :psgDim;';
49 str2 := str2 || 'SQL SELECT dim_code FROM qpr_dimensions WHERE price_plan_id = to_number(:pricePlan) and dim_ppa_code = ''ORD'' into :ordDim;';
50 str2 := str2 || 'SQL SELECT  cube_code FROM  qpr_cubes WHERE price_plan_id  = to_number(:pricePlan) and  cube_ppa_code = ''PRICE_SALES'' into :salesDataCube;';
51 str2 := str2 || 'ordQtyMeas = joinchars(salesDataCube, ''_QPR_AO_Q_P'');';
52 str2 := str2 || 'grossRevMeas = joinchars(salesDataCube, ''_QPR_SP'');';
53 str2 := str2 || 'ordlevel = joinchars(ordDim, ''_LEVELREL'');';
54 str2 := str2 || 'allstat;';
55 str2 := str2 || 'limit &prdDim to prd;';
56 str2 := str2 || 'limit &timDim to last 1;';
57 str2 := str2 || 'limit &psgDim to psg;' ;
58 str2 := str2 || 'limit &ordDim to (&ordlevel eq ''ORDER_LINE'');';
59 str3 := 'for &ordDim;';
60 str3 := str3 || 'do;';
61 str3 := str3 || 'quantity = nafill(&ordQtyMeas, 0);';
62 str3 := str3 || 'revenue = nafill(&grossRevMeas, 0);';
63 str3 := str3 || 'qtySum = qtySum + quantity;';
64 str3 := str3 || 'revSum = revSum + revenue;';
65 str3 := str3 || 'qtySqr = qtySqr +(quantity * quantity);';
66 str3 := str3 || 'qtyRevPrd = qtyRevPrd + (revenue * quantity);';
67 str3 := str3 || ' if(&ordQtyMeas ne NA); ';
68 str3 := str3 || ' then do; ';
69 str3 := str3 || '   counter = counter+1;';
70 str3 := str3 || ' doend;';
71 str3 := str3 || 'doend;';
72 str3 := str3 || 'IF(qtySum ne 0);';
73 str3 := str3 || 'then do;';
74 str3 := str3 || 'slope = ((counter * qtyRevPrd) - (qtySum * revSum)) / (counter * qtySqr - (qtySum * qtySum));';
75 str3 := str3 || 'intercept = (revSum - (slope * qtySum)) / counter;';
76 str3 := str3 || 'doend;';
77 str3 := str3 || 'ALLSTAT;';
78 str3 := str3 || 'return(joinchars(slope,''_'',intercept));end;';
79 
80 
81 dbms_aw.execute(str1 || str2 || str3);
82 
83 FND_FILE.PUT_LINE(FND_FILE.LOG,'Created Regression DML Program ..... ');
84 
85 
86 
87 str1 := 'define retoffinv program;program;';
88 str1 := str1 || 'argument priceplan text;';
89 str1 := str1 || 'argument offinv_type text;';
90 str1 := str1 || 'variable oadPerMeas text;';
91 str1 := str1 || 'variable oadDim text;';
92 str1 := str1 || 'SQL select dim_code from qpr_dimensions where price_plan_id = to_number(:priceplan) and dim_ppa_code = ''OAD'' into :oadDim;';
93 str1 := str1 || 'if(isvalue(&oadDim,offinv_type) eq yes);';
94 str1 := str1 || 'then do;';
95 str1 := str1 || 'limit &oadDim to offinv_type;';
96 str1 := str1 || 'SQL select cube_code from qpr_cubes where price_plan_id = to_number(:priceplan) and cube_ppa_code = ''OFF_INV_ADJ'' into :oadPerMeas;';
97 str1 := str1 || 'oadPerMeas = joinchars(oadPerMeas,''_QPR_AR_PRC'');';
98 str1 := str1 || 'return nvl(&oadPerMeas,0);';
99 str1 := str1 || 'doend;';
100 str1 := str1 || 'else do;';
101 str1 := str1 || 'return 0;';
102 str1 := str1 || 'doend;end;';
103 
104 dbms_aw.execute(str1);
105 
106 FND_FILE.PUT_LINE(FND_FILE.LOG,'Created RetOffInv DML Program ..... ');
107 
108 dbms_aw.execute('update;commit;');
109 FND_FILE.PUT_LINE(FND_FILE.LOG,'UPDATE and COMMIT');
110 
111 dbms_aw.execute('aw detach '||awname);
112 
113 FND_FILE.PUT_LINE(FND_FILE.LOG,'AW Detached');
114 
115 EXCEPTION
116 when others then
117    null;
118   FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in LOAD DML');
119 end LOADDMLPROG;
120 end QPR_DML_PVT;