1 PACKAGE BODY OM_REPORTS_COMMON_PKG AS
2 /* $Header: OEXCMPKB.pls 115.9 2003/10/20 06:51:03 appldev ship $ */
3
4 FUNCTION OEXOEORS_GET_WORKFLOW_DATE (n_line_id number) RETURN DATE IS
5 ans_date DATE;
6
7 BEGIN
8
9 SELECT nvl(wfas.end_date,sysdate)
10 INTO ans_date
11 FROM wf_item_activity_statuses wfas, wf_process_activities wpa
12 WHERE wfas.item_key = TO_CHAR(n_line_id) and
13 wfas.item_type = 'OEOL' and
14 wfas.process_activity = wpa.instance_id and
15 wpa.activity_item_type = 'OEOL' and
16 wpa.activity_name = 'RMA_WAIT_FOR_INSPECTION';
17
18 RETURN ans_date;
19
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 ans_date := sysdate;
23 RETURN ans_date;
24 WHEN OTHERS THEN
25 ans_date := sysdate;
26 RETURN ans_date;
27
28 END OEXOEORS_GET_WORKFLOW_DATE;
29
30
31 FUNCTION OEXOESOS_LINE_AMOUNT_TOTAL (p_header number) RETURN NUMBER IS
32 ans number;
33 BEGIN
34
35 -- SELECT sum(nvl(l.ordered_quantity,0)*nvl(unit_selling_price,0)) Changed for the bug 3087563
36 SELECT sum(decode(l.line_category_code,'RETURN',(nvl(l.ordered_quantity,0)*nvl(l.unit_selling_price,0) * (-1)),nvl(l.ordered_quantity,0)*nvl(l.unit_selling_price,0)))
37 INTO ans
38 FROM oe_order_lines_all l
39 WHERE l.header_id=p_header and
40 l.line_id not in (
41 select s.line_id from oe_sales_credits s
42 where s.header_id=p_header
43 and s.line_id is not null);
44 RETURN ans;
45
46 END OEXOESOS_LINE_AMOUNT_TOTAL;
47
48 FUNCTION OEXCRDIS_GET_LOT_SERIAL_CTL (n_inv_item_id number, n_org_id number) RETURN NUMBER IS
49 lot_serial_control NUMBER;
50
51 BEGIN
52
53 SELECT
54 decode(
55 ( decode( lot_control_code, NULL, 'N', 1, 'N', 'Y') ||
56 decode(serial_number_control_code, NULL, 'N', 1, 'N', 'Y') ),
57 'YY', 3,
58 'NY', 1,
59 'YN', 2,
60 0 )
61 INTO lot_serial_control
62 FROM mtl_system_items
63 WHERE inventory_item_id = n_inv_item_id
64 AND organization_id = n_org_id;
65 RETURN lot_serial_control;
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 lot_serial_control := 0;
70
71 RETURN lot_serial_control;
72
73 END OEXCRDIS_GET_LOT_SERIAL_CTL;
74
75 FUNCTION DF_VALIDATE_FLEX(dff_name VARCHAR2,values_or_ids VARCHAR2) RETURN BOOLEAN IS
76 BEGIN
77 RETURN FND_FLEX_DESCVAL.validate_desccols('ONT',dff_name,values_or_ids);
78 END DF_VALIDATE_FLEX;
79
80 Procedure DF_SET_COLUMN_VALUE(column_name VARCHAR2,column_value VARCHAR2) IS
81 BEGIN
82 FND_FLEX_DESCVAL.set_column_value(column_name,column_value);
83 END DF_SET_COLUMN_VALUE;
84
85 Procedure DF_SET_CONTEXT(context VARCHAR2) IS
86 BEGIN
87 FND_FLEX_DESCVAL.set_context_value(context);
88 END DF_SET_CONTEXT;
89
90 FUNCTION DF_CONCATENATED_VALUES RETURN VARCHAR2 IS
91 BEGIN
92 RETURN FND_FLEX_DESCVAL.concatenated_values;
93 END DF_CONCATENATED_VALUES;
94
95 FUNCTION DF_CONCATENATED_DESCRIPTIONS RETURN VARCHAR2 IS
96 BEGIN
97 RETURN FND_FLEX_DESCVAL.concatenated_descriptions;
98 END DF_CONCATENATED_DESCRIPTIONS;
99
100 END OM_REPORTS_COMMON_PKG;