[Home] [Help]
PACKAGE: APPS.PO_CHARGES_GRP
Source
1 PACKAGE PO_CHARGES_GRP AS
2 /* $Header: POXGFSCS.pls 120.4 2006/04/10 21:26:16 rahujain noship $ */
3
4 TYPE charge_table_type IS TABLE OF po_rcv_charges%ROWTYPE INDEX BY PLS_INTEGER;
5 TYPE charge_allocation_table_type IS TABLE OF po_rcv_charge_allocations%ROWTYPE INDEX BY PLS_INTEGER;
6
7 --
8 -- API name : capture_QP_charges
9 -- Type : Group
10 -- Function : Populate charge tables with QP estimated charges
11 -- Pre-reqs :
12 -- Parameters :
13 -- IN :
14 -- OUT :
15 -- Version : Initial version 1.0
16 -- Notes : Note text
17 --
18 PROCEDURE Capture_QP_Charges
19 ( p_api_version IN NUMBER
20 , p_init_msg_list IN VARCHAR2
21 , x_return_status OUT NOCOPY VARCHAR2
22 , x_msg_count OUT NOCOPY NUMBER
23 , x_msg_data OUT NOCOPY VARCHAR2
24 , p_group_id IN NUMBER
25 , p_request_id IN NUMBER
26 );
27
28 --
29 -- API name : capture_FTE_charges
30 -- Type : Group
31 -- Function : Populate charge tables with FTE estimated charges
32 -- Pre-reqs :
33 -- Parameters :
34 -- IN :
35 -- OUT :
36 -- Version : Initial version 1.0
37 -- Notes : Note text
38 --
39 PROCEDURE Capture_FTE_Estimated_Charges
40 ( p_api_version IN NUMBER
41 , p_init_msg_list IN VARCHAR2
42 , x_return_status OUT NOCOPY VARCHAR2
43 , x_msg_count OUT NOCOPY NUMBER
44 , x_msg_data OUT NOCOPY VARCHAR2
45 , p_group_id IN NUMBER
46 , p_request_id IN NUMBER
47 );
48
49 --
50 -- API name : Record_FTE_Actual_Charges
51 -- Type : Group
52 -- Function : Populate charge tables with FTE actual charges
53 -- Pre-reqs :
54 -- Parameters :
55 -- IN :
56 -- OUT :
57 -- Version : Initial version 1.0
58 -- Notes : Note text
59
60 PROCEDURE Capture_FTE_Actual_Charges
61 ( p_api_version IN NUMBER
62 , p_init_msg_list IN VARCHAR2
63 , x_return_status OUT NOCOPY VARCHAR2
64 , x_msg_count OUT NOCOPY NUMBER
65 , x_msg_data OUT NOCOPY VARCHAR2
66 , p_fte_actual_charge IN po_rcv_charges%rowtype
67 );
68
69 -- API name : Extract_AP_Actual_Charges
70 -- Type : Group
71 -- Function : Capture the actual charges from AP system
72 -- Pre-reqs :
73 -- Parameters :
74 -- IN :
75 -- OUT :
76 -- Version : Initial version 1.0
77 -- Notes : Concurrent program
78
79 PROCEDURE Extract_AP_Actual_Charges
80 ( errbuf OUT NOCOPY VARCHAR2
81 , retcode OUT NOCOPY VARCHAR2
82 );
83
84
85 cursor ap_po_charge_distributions_csr is
86 SELECT decode(ail_charge_lines.line_type_lookup_code, 'MISCELLANEOUS', 'ORACLE_AP_MISC',
87 'FREIGHT', 'ORACLE_AP_FREIGHT') cost_factor_code
88 ,nvl(ail_charge_lines.cost_factor_id,0) cost_factor_id
89 , nvl(aid_charge.amount,0) amount
90 , rsl.shipment_header_id shipment_header_id
91 , rsl.shipment_line_id shipment_line_id
92 , ai_non_fte_invoices.vendor_id vendor_id
93 , ai_non_fte_invoices.vendor_site_id vendor_site_id
94 , aid_charge.invoice_distribution_id invoice_distribution_id
95 , ai_non_fte_invoices.invoice_currency_code currency_code
96 , 0 rec_tax
97 , 0 nonrec_tax
98 FROM ap_invoice_distributions_all aid_charge,
99 ap_invoice_distributions_all aid_matched_items,
100 ap_invoice_lines_all ail_charge_lines,
101 ap_invoices_all ai_non_fte_invoices,
102 po_distributions_all pod,
103 po_line_locations_all poll,
104 rcv_shipment_lines rsl
105 WHERE aid_charge.charge_applicable_to_dist_id = aid_matched_items.invoice_distribution_id
106 AND NVL(aid_charge.rcv_charge_addition_flag, 'N') = 'N'
107 --AND nvl(aid_charge.posted_flag, 'N') = 'Y'
108 AND aid_matched_items.po_distribution_id is not null
109 AND aid_charge.invoice_id = ail_charge_lines.invoice_id
110 AND aid_charge.invoice_line_number = ail_charge_lines.line_number
111 AND ail_charge_lines.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
112 AND ail_charge_lines.invoice_id = ai_non_fte_invoices.invoice_id
113 AND ai_non_fte_invoices.source <> 'FTE'
114 AND aid_matched_items.po_distribution_id = pod.po_distribution_id
115 and poll.line_location_id= pod.line_location_id
116 and poll.line_location_id= rsl.po_line_location_id --added for bug 5024597
117 and ( rsl.quantity_received >= rsl.quantity_shipped OR
118 poll.quantity_received >= poll.quantity OR
119 rsl.amount_received >= rsl.amount OR
120 poll.amount_received >= poll.amount
121 )
122 -- latest receipt for this PO
123 AND rsl.shipment_line_id = ( SELECT MAX(rsl2.shipment_line_id)
124 FROM rcv_shipment_lines rsl2
125 WHERE rsl2.po_line_location_id = poll.line_location_id );
126
127 cursor ap_rcv_charge_distr_csr is
128 SELECT decode(ail_charge_lines.line_type_lookup_code, 'MISCELLANEOUS', 'ORACLE_AP_MISC',
129 'FREIGHT', 'ORACLE_AP_FREIGHT') cost_factor_code
130 ,nvl(ail_charge_lines.cost_factor_id,0) cost_factor_id
131 , nvl(aid_charge.amount,0) amount
132 , rsl.shipment_header_id shipment_header_id
133 , rsl.shipment_line_id shipment_line_id
134 , ai_non_fte_invoices.vendor_id vendor_id
135 , ai_non_fte_invoices.vendor_site_id vendor_site_id
136 , aid_charge.invoice_distribution_id invoice_distribution_id
137 , ai_non_fte_invoices.invoice_currency_code currency_code
138 , 0 rec_tax
139 , 0 nonrec_tax
140 FROM ap_invoice_distributions_all aid_charge,
141 ap_invoice_lines_all ail_charge_lines,
142 ap_invoices_all ai_non_fte_invoices,
143 rcv_transactions rt,
144 rcv_shipment_lines rsl
145 WHERE aid_charge.rcv_transaction_id is NOT NULL
146 AND NVL(aid_charge.rcv_charge_addition_flag, 'N') = 'N'
147 --AND nvl(aid_charge.posted_flag, 'N') = 'Y'
148 AND aid_charge.invoice_id = ail_charge_lines.invoice_id
149 AND aid_charge.invoice_line_number = ail_charge_lines.line_number
150 AND ail_charge_lines.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
151 AND ail_charge_lines.invoice_id = ai_non_fte_invoices.invoice_id
152 AND ai_non_fte_invoices.source <> 'FTE'
153 AND aid_charge.rcv_transaction_id = rt.transaction_id
154 AND rt.shipment_line_id = rsl.shipment_line_id
155 AND (rsl.quantity_received >= rsl.quantity_shipped or
156 rsl.amount_received >= rsl.amount_shipped);
157
158 PROCEDURE Process_AP_Actual_Charges
159 (
160 l_ap_charge_distribution IN OUT NOCOPY po_charges_grp.ap_po_charge_distributions_csr%ROWTYPE,
161 l_charge_table IN OUT NOCOPY CHARGE_TABLE_TYPE,
162 l_charge_alloc_table IN OUT NOCOPY CHARGE_ALLOCATION_TABLE_TYPE,
163 k IN OUT NOCOPY number
164 );
165
166 END PO_CHARGES_GRP;