1 PACKAGE pmi_sales_pkg AUTHID CURRENT_USER as
2 /* $Header: PMISAANS.pls 115.5 2003/02/19 04:46:55 srpuri ship $ */
3 /* #############################################################################
4 Function PMISA_GET_TOP_N
5 This function returns Top customers/Item from op_ordr_dtl
6 with group by on Organization and Item/Customer for a fiven period
7 with respect to margin made. The list is calculated and populated in a
8 PL/SQL package table which is referred if the passed parameters are same
9
10
11 Inputs
12 Sales Organization
13 Cost method to calculate cost
14 Period Start Date
15 Period End Date
16 Item Id
17 Customer Id
18 top N to calcluate the TOP N
19
20 Description
21 This function accepts the given parameters and calculates the top N list
22 and populates a PL/SQL Table
23 ################################################################################# */
24 /*Package Variable for Top N Products */
25
26 Pv_pmisa_tp_Sales_orgn sy_orgn_mst.orgn_code%TYPE;
27 pv_pmisa_tp_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
28 Pv_pmisa_tp_Prd_start_dt DATE;
29 Pv_pmisa_tp_Prd_end_dt DATE;
30 Pv_pmisa_tp_top_n number;
31
32 /*Package Variable for Top N customers */
33
34 Pv_pmisa_tp_cust_Sales_orgn sy_orgn_mst.orgn_code%TYPE;
35 pv_pmisa_tp_cust_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
36 Pv_pmisa_tp_cust_Prd_start_dt DATE;
37 Pv_pmisa_tp_cust_Prd_end_dt DATE;
38 Pv_pmisa_tp_cust_top_n number;
39
40
41 /* Added new variable to support OMSO which are at OU Level for Top N Products */
42
43 Pv_pmisa_tp_om_OU_ID hr_operating_units.ORGANIZATION_ID%TYPE;
44 pv_pmisa_tp_om_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
45 Pv_pmisa_tp_om_Prd_start_dt DATE;
46 Pv_pmisa_tp_om_Prd_end_dt DATE;
47 Pv_pmisa_tp_om_top_n number;
48
49 /* Added new variable to support OMSO which are at OU Level for Top N Customers */
50
51 Pv_pmisa_tp_om_cust_OU_ID hr_operating_units.ORGANIZATION_ID%TYPE;
52 pv_pmisa_tp_om_cust_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
53 Pv_pmisa_tp_om_cust_Prd_st_dt DATE;
54 Pv_pmisa_tp_om_cust_Prd_end_dt DATE;
55 Pv_pmisa_tp_om_cust_top_n number;
56
57
58 /* Package Table to hold Top n customers */
59 TYPE pv_pmisa_top_n_cust_type is table of NUMBER INDEX BY BINARY_INTEGER;
60 pv_pmisa_top_n_cust pv_pmisa_top_n_cust_type;
61 /* Package Table to hold Top n Items */
62 TYPE pv_pmisa_top_n_item_type is table of NUMBER INDEX BY BINARY_INTEGER;
63 pv_pmisa_top_n_item pv_pmisa_top_n_item_type;
64
65 /* Added new variable to support OMSO which are at OU Level */
66 /* Package Table to hold Top n customers By OU*/
67 pv_pmisa_top_n_cust_by_ou pv_pmisa_top_n_cust_type;
68
69 /* Package Table to hold Top n Items by OU */
70 pv_pmisa_top_n_item_by_ou pv_pmisa_top_n_item_type;
71
72
73 FUNCTION PMISA_GET_TOP_N(P_Sales_orgn IN sy_orgn_mst.orgn_code%TYPE,
74 P_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
75 P_Prd_start_date IN DATE,
76 P_Prd_end_date IN DATE,
77 P_item_id IN ic_item_mst.item_id%TYPE DEFAULT NULL,
78 P_customer_id IN op_ordr_hdr.billcust_id%TYPE DEFAULT NULL,
79 P_top_n IN NUMBER
80 )
81 RETURN NUMBER;
82
83
84 FUNCTION PMISA_GET_TOP_N_BY_OU(p_OU_ID IN hr_operating_units.ORGANIZATION_ID%TYPE,
85 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
86 p_Prd_start_date IN DATE,
87 p_Prd_end_date IN DATE,
88 p_item_id IN ic_item_mst.item_id%TYPE DEFAULT NULL,
89 P_customer_id IN hz_parties.PARTY_ID%TYPE DEFAULT NULL,
90 p_top_n IN NUMBER
91 )
92 RETURN NUMBER;
93
94 /* #############################################################################
95 Function PMISA_VALIDATE_DATE
96 This function validate the Actual ship date for a gicen period
97
98 Inputs
99 Sales Company
100 From Period
101 To Period
102 From Fiscal Year
103 To Fiscal Year
104 Actual Ship Date
105
106 Description
107 The function accepts the parameter and return 1 if the actual ship date falls
108 between the given period
109 ################################################################################# */
110
111 /*Package Variable for PMISA_VALIDATE_DATE*/
112 pv_pmisa_vd_actual_date DATE;
113 Pv_pmisa_vd_from_year number;
114 Pv_pmisa_vd_to_year number;
115 Pv_pmisa_vd_from_period pmi_gl_calendar_v.period_name%TYPE;
116 Pv_pmisa_vd_to_period pmi_gl_calendar_v.period_name%TYPE;
117 pv_pmisa_vd_start_date DATE;
118 pv_pmisa_vd_end_date DATE;
119 pv_pmisa_vd_company sy_orgn_mst.co_code%TYPE;
120
121 FUNCTION PMISA_VALIDATE_DATE(p_Sales_company IN sy_orgn_mst.co_code%TYPE,
122 p_From_year IN number,
123 p_To_year IN number,
124 p_From_period IN pmi_gl_calendar_v.period_name%TYPE,
125 p_To_period IN pmi_gl_calendar_v.period_name%TYPE,
126 P_Actual_date IN DATE
127 )
128 RETURN NUMBER;
129
130 /* #############################################################################
131 Function PMISA_GET_CHARGE
132 This function is to get charges from given order line
133
134 Inputs
135 ORDER_ID
136 LINE_ID
137 EXTENDED_PRICE
138 BASE_CURRENCY
139 Exchange Rate
140 MUL_DIV_SIGN
141
142 Description
143 The following assumptions are made to get the charges
144 1. We should consider only those Charges where CHARGE_TYPE = 20 or 30 ( 20= Discount, 30= Allowances )
145 2. We will exclude CHARGE_TYPE = 0 or 1 or 10 ( 0 = Miscellaneous, 1 = Freight, 10 = Tax ) from this calculation.
146 3. OP_ORDR_CHG.EXTENDED_AMOUNT is the Total Charge (in Base Currency) for the order Line or the whole
147 Order.
148 4. In the table OP_ORDR_CHG, if LINE_ID is not NULL ( i.e. Charge specified for the Order Line ) then
149 EXTENDED_AMOUNT will be deducted from the Extended price to calculate the net Revenue of the Order Line.
150 5. In the table OP_ORDR_CHG, if LINE_ID is NULL ( i.e. Charge specified for the whole Order ) then calculate the
151 charge for a particular order line using the formula (Extended Price of the order line / Total Order Value ) *
152 EXTENDED_AMOUNT
153 6. If No Charge is found the function returns 0
154
155 ################################################################################# */
156
157 FUNCTION PMISA_GET_CHARGE ( p_Order_id IN op_ordr_dtl.order_id%TYPE,
158 p_line_id IN op_ordr_dtl.line_id%TYPE,
159 p_extended_price IN op_ordr_dtl.extended_price%TYPE,
160 p_Billing_Currency IN op_ordr_dtl.billing_currency%TYPE,
161 p_Base_Currency IN op_ordr_dtl.BASE_CURRENCY%TYPE,
162 p_exchange_Rate IN op_ordr_dtl.EXCHANGE_RATE%TYPE,
163 p_mul_div_sign IN op_ordr_dtl.mul_div_sign%TYPE
164 )
165 RETURN NUMBER;
166 /*
167 PMIOM_GET_CHARGE is the Order Management equivalent of PMISA_GET_CHARGE. Instead of identifying charge_type on a charge line basis, there are pricing lists that have list_type_codes at the header (QP_LIST_HEADERS_B) and line (QP_LIST_LINES_ALL) levels.
168
169 At the header level, list_type_code has the following values:
170
171 PRL = Price List
172 DLT = Discount List
173 SLT = Surcharge List
174 PML = Price Modifier List
175 DEL = Deal
176 PRO = Promotion
177 CHARGES = Freight and Special Charges List
178 AGR = Agreement Price List
179
180 As per Price Table Mapping.doc, only DLT is considered the equivalent of a charge in Order Fulfillment.
181
182 At the line level, list_type_code has the following values:
183
184 PLL = Price List Line
185 PBH = Price Break Header
186 DIS = Discount
187 SUR = Surcharge
188 PMR = Price Modifier
189 OID = Other Item Discount
190 PRG = Promotional Goods
191 TSN = Terms Substitution
192 IUE = Item Upgrade
193 CIE = Coupon Issue
194 FRIEND_CHARGE = Freight / Special Charge
195
196 As per Price Table Mapping.doc, only DIS is considered the equivalent of a charge in Order Fulfillment
197
198 */
199 FUNCTION PMIOM_GET_CHARGE ( p_header_id IN oe_order_lines_all.header_id%TYPE,
200 p_line_id IN oe_order_lines_all.line_id%TYPE,
201 p_extended_price IN oe_order_lines_all.unit_selling_price%TYPE,
202 p_Billing_Currency IN oe_order_headers_all.transactional_curr_code%TYPE,
203 p_Base_Currency IN gl_sets_of_books.CURRENCY_CODE%TYPE,
204 p_exchange_Rate IN oe_order_headers_all.CONVERSION_RATE%TYPE,
205 p_ordered_quantity IN oe_order_lines_all.ordered_quantity%TYPE
206 )
207 RETURN NUMBER;
208
209 END;
[Home] [Help]