1 PACKAGE BODY PV_ENRL_REQUEST_ORDER_PUB AS
2 /* $Header: pvxperob.pls 120.1 2005/08/10 17:00:25 appldev ship $ */
3
4 --Comments
5 --kvattiku: Aug 05, 05 Commented out for R12 in Get_payment_type. Directly retrieve from pv_lookups.
6 -- Added prefix ARPS to the AMOUNT_DUE_REMAINING column as it exists in
7 -- both AR_PAYMENT_SCHEDULES_ALL and RA_CUSTOMER_TRX_LINES_ALL
8
9 -- Global constant holding the package name
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_ENRL_REQUEST_ORDER_PUB';
12
13
14 FUNCTION get_Invoice_Balance
15 ( p_order_header_id IN NUMBER
16 ) RETURN NUMBER
17 IS
18 v_balance NUMBER := NULL ;
19 BEGIN
20 RETURN(v_balance);
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 return(NULL);
24 END; -- INVOICE_BALANCE
25
26
27 FUNCTION Get_payment_type
28 ( p_order_header_id IN NUMBER
29 ) RETURN VARCHAR2
30 IS
31 v_payment_type_code varchar2(80) := NULL ;
32 v_po_number varchar2(50) := NULL;
33 v_payment_type varchar2(80) := NULL;
34 BEGIN
35 IF ( p_order_header_id IS NOT NULL ) THEN
36 select h.payment_type_code , h.cust_po_number
37 into v_payment_type_code, v_po_number
38 from oe_order_headers_all h
39 where h.header_id = p_order_header_id;
40
41 --kvattiku: Aug 05, 05 Directly retrieve from pv_lookups.
42 if(v_payment_type_code is null) then
43 select meaning
44 into v_payment_type
45 from pv_lookups
46 where lookup_type ='PV_PAYMENT_TYPE'
47 and lookup_code = 'INVOICE';
48 else
49 select meaning
50 into v_payment_type
51 from pv_lookups
52 where lookup_type ='PV_PAYMENT_TYPE'
53 and lookup_code = v_payment_type_code;
54 end if;
55
56 /*kvattiku: Aug, 05 Commented out for R12.
57 if(v_payment_type_code is null) then
58 if(v_po_number is null) then
59 return(NULL);
60 else
61 select meaning
62 into v_payment_type
63 from fnd_lookup_values_vl
64 where lookup_type ='PV_PAYMENT_TYPE'
65 and lookup_code = 'PURCHASE_ORDER';
66 end if;
67 else
68 IF v_payment_type_code='WIRE_TRANSFER' THEN
69 select meaning
70 into v_payment_type
71 from fnd_lookup_values_vl
72 where lookup_type ='PV_PAYMENT_TYPE'
73 and lookup_code = 'WIRE_TRANSFER';
74 ELSE
75 select meaning
76 into v_payment_type
77 from oe_lookups
78 where lookup_type = 'PAYMENT TYPE'
79 and lookup_code = v_payment_type_code;
80 END IF;
81 end if;
82 */
83
84 END IF;
85
86 RETURN(v_payment_type);
87 EXCEPTION
88 WHEN NO_DATA_FOUND THEN
89 return(NULL);
90 END; -- Get_payment_type
91
92
93
94 PROCEDURE get_invoice_balance(
95 p_order_header_id IN NUMBER
96 ,x_invoice_balance OUT NOCOPY NUMBER
97 ,x_invoice_currency OUT NOCOPY VARCHAR2
98 )
99 IS
100
101 BEGIN
102
103 IF (p_order_header_id IS NOT NULL ) THEN
104
105 --kvattiku: Aug 05, 05 Added prefix ARPS to the AMOUNT_DUE_REMAINING column as it exists in
106 --both AR_PAYMENT_SCHEDULES_ALL and RA_CUSTOMER_TRX_LINES_ALL
107 SELECT NVL(SUM(ARPS.AMOUNT_DUE_REMAINING),0), FCV.NAME
108 into x_invoice_balance, x_invoice_currency
109 FROM AR_PAYMENT_SCHEDULES_ALL ARPS, RA_CUSTOMER_TRX_LINES_ALL RCTL,
110 OE_ORDER_LINES_ALL OOLA, FND_CURRENCIES_VL FCV
111 WHERE OOLA.HEADER_ID = p_order_header_id
112 AND RCTL.INTERFACE_LINE_ATTRIBUTE6 = to_char(OOLA.LINE_ID)
113 AND RCTL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
114 AND RCTL.CUSTOMER_TRX_ID = ARPS.CUSTOMER_TRX_ID
115 AND ARPS.CLASS = 'INV'
116 AND ARPS.INVOICE_CURRENCY_CODE = FCV.CURRENCY_CODE
117 group by RCTL.CUSTOMER_TRX_ID, FCV.NAME ;
118
119 END IF;
120
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 x_invoice_balance := NULL;
124 x_invoice_currency := NULL;
125 END get_invoice_balance; -- GET_INVOICE_BALANCE
126
127
128 PROCEDURE get_invoice_details(
129 p_order_line_id IN VARCHAR2
130 ,x_invoice_balance OUT NOCOPY NUMBER
131 ,x_invoice_currency OUT NOCOPY VARCHAR2
132 ,x_invoice_number OUT NOCOPY VARCHAR2
133 ) IS
134 BEGIN
135 --kvattiku: Aug 05, 05 Added prefix ARPS to the AMOUNT_DUE_REMAINING column as it exists in
136 --both AR_PAYMENT_SCHEDULES_ALL and RA_CUSTOMER_TRX_LINES_ALL
137 IF (p_order_line_id IS NOT NULL ) THEN
138 SELECT NVL(SUM(ARPS.AMOUNT_DUE_REMAINING),0), FCV.name, RCT.TRX_NUMBER
139 into x_invoice_balance, x_invoice_currency, x_invoice_number
140 FROM AR_PAYMENT_SCHEDULES_ALL ARPS, RA_CUSTOMER_TRX_LINES_ALL RCTL,
141 FND_CURRENCIES_VL FCV, RA_CUSTOMER_TRX_ALL RCT
142 WHERE RCTL.INTERFACE_LINE_ATTRIBUTE6 = p_order_line_id
143 AND RCTL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
144 AND RCTL.CUSTOMER_TRX_ID = ARPS.CUSTOMER_TRX_ID
145 AND ARPS.CLASS = 'INV'
146 AND ARPS.INVOICE_CURRENCY_CODE = FCV.CURRENCY_CODE
147 AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
148 group by RCTL.CUSTOMER_TRX_ID, FCV.name, RCT.TRX_NUMBER;
149
150 END IF;
151
152
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 x_invoice_balance := NULL;
156 x_invoice_currency := NULL;
157 x_invoice_number := NULL;
158 END get_invoice_details; -- GET_INVOICE_BALANCE
159
160 END PV_ENRL_REQUEST_ORDER_PUB;
[Home] [Help]