1 PACKAGE OE_RETROBILL_PVT AUTHID CURRENT_USER AS
2 /* $Header: OEXVRTOS.pls 120.5 2011/11/02 05:58:45 nilegupt ship $ */
3
4 Type retrobill_line_rec is Record
5 (original_line_id NUMBER,
6 original_header_id NUMBER,
7 retrobill_line_id NUMBER,
8 retrobill_header_id NUMBER,
9 retrobill_qty NUMBER,
10 operation VARCHAR2(15)
11 );
12
13 Procedure Get_Retrobilled_Sum(p_header_id IN NUMBER,
14 p_line_id IN NUMBER,
15 p_curr_retro_id IN NUMBER DEFAULT -999,
16 x_usp_sum OUT NOCOPY NUMBER,
17 x_ulp_sum OUT NOCOPY NUMBER);
18
19 Procedure Get_Return_Price(p_header_id IN NUMBER,
20 p_line_id IN NUMBER,
21 p_ordered_qty IN NUMBER,--bug3540728
22 p_pricing_qty IN NUMBER, --bug3540728
23 p_usp IN NUMBER,
24 p_ulp IN NUMBER,
25 x_usp OUT NOCOPY NUMBER,
26 x_ulp OUT NOCOPY NUMBER,
27 x_ulp_ppqty OUT NOCOPY NUMBER,--bug3540728
28 x_usp_ppqty OUT NOCOPY NUMBER); --bug3540728
29
30 Type retrobill_tbl_type is Table of retrobill_line_rec index by Binary_Integer;
31
32 PROCEDURE Process_Retrobill_Request
33 (p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE
34 ,p_retrobill_tbl IN RETROBILL_TBL_TYPE
35 ,x_created_retrobill_request_id OUT NOCOPY NUMBER
36 ,x_msg_count OUT NOCOPY NUMBER
37 ,x_msg_data OUT NOCOPY VARCHAR2
38 ,x_return_status OUT NOCOPY VARCHAR2
39 ,x_retrun_status_text OUT NOCOPY VARCHAR2
40 --bug5003256
41 ,x_error_count OUT NOCOPY NUMBER
42 ,x_ret_code IN OUT NOCOPY NUMBER); --bug12922209
43
44 G_RETROBILL_ORDER_SOURCE_ID NUMBER:=27;
45
46 PROCEDURE Update_Retrobill_Lines(p_operation IN VARCHAR2);
47
48 PROCEDURE Get_Most_Recent_Retro_Adj
49 (p_key_header_id IN NUMBER,
50 p_key_line_id IN NUMBER,
51 p_adjustment_level IN VARCHAR2,
52 x_retro_exists OUT NOCOPY BOOLEAN, --bug3738043
53 x_line_adj_tbl OUT NOCOPY OE_ORDER_PUB.LINE_ADJ_TBL_TYPE);
54
55 PROCEDURE Process_Retrobill_Adjustments(p_operation IN VARCHAR2);
56
57
58
59 PROCEDURE Preprocess_Adjustments(p_orig_sys_document_ref IN NUMBER
60 ,p_orig_sys_line_ref IN NUMBER
61 ,p_header_id IN NUMBER --bug3738043
62 ,p_line_id IN NUMBER);
63
64 PROCEDURE Get_Last_Retro_HdrID(p_header_id IN NUMBER,
65 x_header_id OUT NOCOPY NUMBER);
66
67 PROCEDURE Get_Last_Retro_LinID(p_line_id IN NUMBER,
68 x_line_id OUT NOCOPY NUMBER);
69 --retro{
70
71 PROCEDURE Oe_Build_Retrobill_Tbl(p_request_session_id IN NUMBER,
72 p_retrobill_event IN VARCHAR2,
73 p_description IN VARCHAR2,
74 p_order_type_id IN NUMBER,
75 p_retrobill_request_id IN NUMBER,
76 p_reason_code IN VARCHAR2,
77 p_retrobill_mode IN VARCHAR2,
78 p_sold_to_org_id IN NUMBER,
79 p_inventory_item_id IN NUMBER,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 x_return_status_text OUT NOCOPY VARCHAR2,
84 x_retrobill_request_id OUT NOCOPY NUMBER,
85 --bug5003256
86 x_error_count OUT NOCOPY NUMBER,
87 x_ret_code OUT NOCOPY NUMBER -- bug12922209
88 );
89
90 PROCEDURE Oe_Retrobill_Conc_Pgm(errbuf OUT NOCOPY VARCHAR2,
91 retcode OUT NOCOPY NUMBER,
92 p_request_session_id IN VARCHAR2,
93 p_retrobill_event IN VARCHAR2,
94 p_description IN VARCHAR2,
95 p_order_type_id IN VARCHAR2,
96 p_retrobill_request_id IN VARCHAR2,
97 p_reason_code IN VARCHAR2,
98 p_retrobill_mode IN VARCHAR2,
99 p_sold_to_org_id IN NUMBER,
100 p_inventory_item_id IN NUMBER
101 );
102 FUNCTION Retrobill_Enabled RETURN BOOLEAN;
103
104 PROCEDURE Interface_Retrobilled_RMA
105 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
106 , p_header_rec IN OE_Order_PUB.Header_Rec_Type
107 , x_return_status OUT NOCOPY VARCHAR2
108 , x_result_out OUT NOCOPY VARCHAR2
109 );
110
111 --Procedure for the api based validation template Return Retrobilled Line
112 --skubendr{
113 PROCEDURE Return_Retrobilled_Line_Check
114 ( p_application_id IN NUMBER,
115 p_entity_short_name IN VARCHAR2,
116 p_validation_entity_short_name IN VARCHAR2,
117 p_validation_tmplt_short_name IN VARCHAR2,
118 p_record_set_short_name IN VARCHAR2,
119 p_scope IN VARCHAR2,
120 x_result OUT NOCOPY NUMBER
121 );
122
123 --Procedure for Purging Retrobill Requests and associated Headers/Lines
124 PROCEDURE Oe_Retrobill_Purge
125 ( errbuf OUT NOCOPY VARCHAR2,
126 retcode OUT NOCOPY NUMBER,
127 p_org_id IN VARCHAR2, --rt moac
128 p_retrobill_request_id IN VARCHAR2,
129 p_creation_date_from IN VARCHAR2,
130 p_creation_date_to IN VARCHAR2,
131 p_execution_date_from IN VARCHAR2,
132 p_execution_date_to IN VARCHAR2,
133 p_purge_preview_orders IN VARCHAR2
134 );
135 --skubendr}
136
137 --bug3654144
138 PROCEDURE Update_Invalid_Diff_Adj;
139
140 FUNCTION Invoice_Number(p_order_number IN NUMBER,p_line_id IN NUMBER,p_order_type_id IN NUMBER) RETURN VARCHAR2;
141
142 FUNCTION Get_First_Line_Price_List_Id RETURN NUMBER;
143
144 --bug3738043
145 FUNCTION Get_Retro_Pricing_Phase_Count RETURN NUMBER;
146
147 -- 3661895 Start
148 PROCEDURE Get_Line_Adjustments
149 (p_line_rec IN OE_Order_Pub.Line_Rec_Type
150 ,x_line_adjustments OUT NOCOPY OE_Header_Adj_Util.Line_Adjustments_Tab_Type
151 );
152 -- 3661895 End
153 --skubendr{
154 G_RETROBILL_REQUEST_REC OE_RETROBILL_REQUESTS%ROWTYPE;
155 --skubendr}
156 G_FIRST_LINE_PRICE_LIST_ID NUMBER;
157 G_FIRST_LINE_DELETED varchar2(1);
158 G_FIRST_LINE_PL_ASSIGNED varchar2(1);
159 --retro}
160
161
162 End OE_RETROBILL_PVT;