[Home] [Help]
PACKAGE BODY: APPS.PA_INV_ON_ACCOUNT_CM
Source
1 PACKAGE BODY PA_INV_ON_ACCOUNT_CM AS
2 /* $Header: PAIOACMB.pls 120.0 2011/08/31 12:04:15 rdegala noship $ */
3 --
4 PROCEDURE CREATE_ON_ACCOUNT_CM( p_project_id IN NUMBER,
5 p_request_id IN NUMBER)
6 IS
7 l_create_on_account_cm varchar2(1);
8 l_balance_inv_curr NUMBER := 0;
9 l_balance_acct_curr NUMBER := 0;
10 l_return_status VARCHAR2(2);
11 l_msg_count NUMBER := 0;
12 l_msg_data VARCHAR2(2000);
13
14 CURSOR c_cm IS
15 SELECT pdi_cm.project_id,
16 pdi_cm.draft_invoice_num,
17 ra.interface_line_attribute1,
18 ra.interface_line_attribute2,
19 ra.batch_source_name,
20 pdi.system_reference,
21 NVL(rbs.gen_line_level_bal_flag,'N') line_level_bal_flag,
22 sum(pdii_cm.inv_amount) cm_amount,
23 rctt.allow_overapplication_flag
24 FROM ra_interface_lines ra,
25 pa_draft_invoices pdi,
26 pa_draft_invoices pdi_cm,
27 pa_implementations pi,
28 pa_projects pa,
29 ra_batch_sources rbs,
30 pa_draft_invoice_items pdii_cm,
31 ra_customer_trx rct,
32 ra_cust_trx_types rctt
33 WHERE pdi_cm.project_id = pdi.project_id
34 AND pdi_cm.draft_invoice_num_credited = pdi.draft_invoice_num
35 AND pdi_cm.project_id = pa.project_id
36 AND rbs.batch_source_id = pi.invoice_batch_source_id
37 AND rbs.name = ra.batch_source_name
38 AND pdi_cm.request_id = p_request_id
39 AND pdi_cm.project_id = p_project_id
40 AND SUBSTR(pa.segment1,1,25) = ra.interface_line_attribute1
41 AND TO_CHAR(pdi_cm.draft_invoice_num) = ra.interface_line_attribute2
42 AND pdii_cm.project_id = pdi_cm.project_id
43 AND pdii_cm.draft_invoice_num = pdi_cm.draft_invoice_num
44 AND pdi.system_reference = rct.customer_trx_id
45 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
46 GROUP BY pdi_cm.project_id,
47 pdi_cm.draft_invoice_num,
48 ra.interface_line_attribute1,
49 ra.interface_line_attribute2,
50 ra.batch_source_name,
51 pdi.system_reference,
52 NVL(rbs.gen_line_level_bal_flag,'N'),
53 rctt.allow_overapplication_flag;
54
55 CURSOR c_cm_lines(c_project_id NUMBER, c_draft_invoice_num NUMBER) IS
56 SELECT pdii.project_id,
57 pdii.line_num,
58 pdii.draft_inv_line_num_credited line_num_credited,
59 pdii.inv_amount
60 FROM pa_draft_invoice_items pdii
61 WHERE pdii.project_id = c_project_id
62 AND pdii.draft_invoice_num = c_draft_invoice_num
63 AND inv_amount <> 0 ;
64
65 BEGIN
66
67 PA_MCB_INVOICE_PKG.log_message('Entering PA_INV_ON_ACCOUNT_CM.CREATE_ON_ACCOUNT_CM');
68
69 SELECT NVL(create_on_account_cm,'N')
70 INTO l_create_on_account_cm
71 FROM pa_implementations;
72
73 IF (l_create_on_account_cm <> 'N') THEN
74
75 FOR c_cm_rec IN c_cm LOOP
76
77 PA_MCB_INVOICE_PKG.log_message('Project_id :'||c_cm_rec.project_id );
78 PA_MCB_INVOICE_PKG.log_message('Draft Invoice:'||c_cm_rec.draft_invoice_num);
79
80
81 IF (c_cm_rec.line_level_bal_flag = 'N') THEN
82 PA_MCB_INVOICE_PKG.log_message('Checking Invoice Level Balances');
83 PA_MCB_INVOICE_PKG.log_message('System Reference:'||c_cm_rec.system_reference);
84
85 AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE
86 (p_api_version => '1.0',
87 p_customer_trx_id => c_cm_rec.system_reference,
88 x_amount_due_remaining => l_balance_inv_curr,
89 x_acctd_amount_due_remaining => l_balance_acct_curr,
90 x_return_status => l_return_status,
91 x_msg_count => l_msg_count,
92 x_msg_data => l_msg_data);
93
94 PA_MCB_INVOICE_PKG.log_message('Balance in Invoice currency:'||l_balance_inv_curr);
95 PA_MCB_INVOICE_PKG.log_message('Balance in Accounted currency:'||l_balance_acct_curr);
96
97 PA_MCB_INVOICE_PKG.log_message('CM Amount in Invoice currency:'||c_cm_rec.cm_amount);
98 PA_MCB_INVOICE_PKG.log_message('Over Application Flag of Invoice:' || c_cm_rec.allow_overapplication_flag);
99
100 IF (((c_cm_rec.cm_amount * -1) > l_balance_inv_curr ) AND
101 c_cm_rec.allow_overapplication_flag = 'N' )THEN
102
103 UPDATE ra_interface_lines ra
104 SET Reference_Line_Attribute1 = NULL,
105 Reference_Line_Attribute2 = NULL,
106 Reference_Line_Attribute3 = NULL,
107 Reference_Line_Attribute4 = NULL,
108 Reference_Line_Attribute5 = NULL,
109 Reference_Line_Attribute6 = NULL,
110 Reference_Line_Attribute7 = NULL,
111 Reference_Line_Attribute8 = NULL,
112 Reference_Line_Context = NULL
113 WHERE ra.batch_source_name = c_cm_rec.batch_source_name
114 AND ra.interface_line_attribute1 = c_cm_rec.interface_line_attribute1
115 AND ra.interface_line_attribute2 = c_cm_rec.interface_line_attribute2;
116
117 PA_MCB_INVOICE_PKG.log_message('Converted Credit Memo to On Account Credit Memo');
118 ELSE
119 PA_MCB_INVOICE_PKG.log_message('Cancel Converting');
120 END IF;
121
122 ELSE
123 FOR c_cm_lines_rec IN c_cm_lines(c_cm_rec.project_id,c_cm_rec.draft_invoice_num) LOOP
124
125 PA_MCB_INVOICE_PKG.log_message('Checking Invoice Line Level Balances');
126 PA_MCB_INVOICE_PKG.log_message('System Reference:'||c_cm_rec.system_reference);
127 PA_MCB_INVOICE_PKG.log_message('interface_line_attribute6:'||lpad(c_cm_lines_rec.line_num_credited,10));
128
129 AR_INVOICE_API_PUB.GET_TRXN_LINE_BALANCE
130 (p_api_version => '1.0',
131 p_customer_trx_id => c_cm_rec.system_reference,
132 p_interface_line_attribute6 => lpad(c_cm_lines_rec.line_num_credited,10),
133 x_amount_due_remaining => l_balance_inv_curr,
134 x_acctd_amount_due_remaining => l_balance_acct_curr,
135 x_return_status => l_return_status,
136 x_msg_count => l_msg_count,
137 x_msg_data => l_msg_data);
138
139 PA_MCB_INVOICE_PKG.log_message('Balance in Invoice currency:'||l_balance_inv_curr);
140 PA_MCB_INVOICE_PKG.log_message('Balance in Accounted currency:'||l_balance_acct_curr);
141
142 PA_MCB_INVOICE_PKG.log_message('CM Line Amount in Invoice currency:'||c_cm_lines_rec.inv_amount);
143
144 IF ((c_cm_lines_rec.inv_amount * -1) > l_balance_inv_curr ) THEN
145
146 UPDATE ra_interface_lines ra
147 SET Reference_Line_Attribute1 = NULL,
148 Reference_Line_Attribute2 = NULL,
149 Reference_Line_Attribute3 = NULL,
150 Reference_Line_Attribute4 = NULL,
151 Reference_Line_Attribute5 = NULL,
152 Reference_Line_Attribute6 = NULL,
153 Reference_Line_Attribute7 = NULL,
154 Reference_Line_Attribute8 = NULL,
155 Reference_Line_Context = NULL
156 WHERE ra.batch_source_name = c_cm_rec.batch_source_name
157 AND ra.interface_line_attribute1 = c_cm_rec.interface_line_attribute1
158 AND ra.interface_line_attribute2 = c_cm_rec.interface_line_attribute2;
159
160 PA_MCB_INVOICE_PKG.log_message('Converted Credit Memo to On Account Credit Memo');
161
162 EXIT;
163 ELSE
164 PA_MCB_INVOICE_PKG.log_message('Cancel Converting');
165 END IF;
166
167
168 END LOOP;
169
170 END IF;
171 END LOOP;
172 END IF;
173 PA_MCB_INVOICE_PKG.log_message('Exiting PA_INV_ON_ACCOUNT_CM.CREATE_ON_ACCOUNT_CM');
174
175 END;
176
177
178 END PA_INV_ON_ACCOUNT_CM;