[Home] [Help]
1: PACKAGE BODY AP_INVOICE_DISTRIBUTIONS_PKG AS
2: /* $Header: apiindib.pls 120.112.12020000.11 2013/03/12 06:26:25 cmaredup ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_INVOICE_DISTRIBUTIONS_PKG';
5: G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
1: PACKAGE BODY AP_INVOICE_DISTRIBUTIONS_PKG AS
2: /* $Header: apiindib.pls 120.112.12020000.11 2013/03/12 06:26:25 cmaredup ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_INVOICE_DISTRIBUTIONS_PKG';
5: G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6: G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7: G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8: G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
16: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20: G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_INVOICE_DISTRIBUTIONS_PKG.';
21:
22:
23: --Invoice Lines: Distributions. Added the parameter X_INVOICE_LINE_NUMBER
24: -----------------------------------------------------------------------
38:
39: -- Update the calling sequence
40: --
41: current_calling_sequence :=
42: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHECK_UNIQUE<-'||X_Calling_Sequence;
43:
44: debug_info := 'Select from ap_invoice_distributions';
45:
46: select count(1)
40: --
41: current_calling_sequence :=
42: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHECK_UNIQUE<-'||X_Calling_Sequence;
43:
44: debug_info := 'Select from ap_invoice_distributions';
45:
46: select count(1)
47: into dummy
48: from ap_invoice_distributions
44: debug_info := 'Select from ap_invoice_distributions';
45:
46: select count(1)
47: into dummy
48: from ap_invoice_distributions
49: where (invoice_id = X_INVOICE_ID AND
50: invoice_line_number = X_INVOICE_LINE_NUMBER AND
51: distribution_line_number = X_DISTRIBUTION_LINE_NUMBER)
52: and ((X_ROWID is null) or (rowid <> X_ROWID));
239:
240: -- Update the calling sequence
241: --
242: current_calling_sequence :=
243: 'AP_INVOICE_DISTRIBUTIONS_PKG.Select_Summary<-'||X_Calling_Sequence;
244:
245: debug_info := 'Select from ap_invoice_distributions';
246:
247: -- eTax Uptake.
241: --
242: current_calling_sequence :=
243: 'AP_INVOICE_DISTRIBUTIONS_PKG.Select_Summary<-'||X_Calling_Sequence;
244:
245: debug_info := 'Select from ap_invoice_distributions';
246:
247: -- eTax Uptake.
248: -- This select should return the distribution total. Prepayment and
249: -- Prepayment Tax amount should be included if the flag
251: --Bug4539547 Added IF statement
252: If (X_LINE_NUMBER is null) then
253: SELECT NVL(SUM(aid.amount), 0)
254: INTO X_Total
255: FROM ap_invoice_distributions_all aid,
256: ap_invoice_lines_all ail
257: WHERE ail.invoice_id = X_Invoice_Id
258: AND ail.invoice_id = aid.invoice_id
259: AND ail.line_number = aid.invoice_line_number
264: OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
265: else
266: SELECT NVL(SUM(aid.amount), 0)
267: INTO X_Total
268: FROM ap_invoice_distributions_all aid,
269: ap_invoice_lines_all ail
270: WHERE ail.invoice_id = X_Invoice_Id
271: AND ail.invoice_id = aid.invoice_id
272: AND ail.line_number = aid.invoice_line_number
323: --
324: current_calling_sequence :=
325: 'AP_INVOICE_DISTRIBUTION_PKG.Set_Inv_Packet_Id<-'||X_Calling_Sequence;
326:
327: debug_info := 'Select from ap_invoice_distributions';
328:
329: select decode(count(distinct(packet_id)),1,max(packet_id),'')
330: into X_Packet_Id
331: from ap_invoice_distributions
327: debug_info := 'Select from ap_invoice_distributions';
328:
329: select decode(count(distinct(packet_id)),1,max(packet_id),'')
330: into X_Packet_Id
331: from ap_invoice_distributions
332: where invoice_id = X_Invoice_Id
333: and packet_id is not null;
334:
335: EXCEPTION
365:
366: -- Update the calling sequence
367: --
368: current_calling_sequence :=
369: 'AP_INVOICE_DISTRIBUTIONS_PKG.Query_New_Packet_Id<-'||X_Calling_Sequence;
370:
371: debug_info := 'Select from ap_invoice_distributions';
372:
373: select 'TRUE'
367: --
368: current_calling_sequence :=
369: 'AP_INVOICE_DISTRIBUTIONS_PKG.Query_New_Packet_Id<-'||X_Calling_Sequence;
370:
371: debug_info := 'Select from ap_invoice_distributions';
372:
373: select 'TRUE'
374: into dummy
375: from ap_invoice_distributions
371: debug_info := 'Select from ap_invoice_distributions';
372:
373: select 'TRUE'
374: into dummy
375: from ap_invoice_distributions
376: where rowid = X_Rowid
377: and NVL(packet_id, -1) <> NVL(X_Packet_Id, -1);
378:
379: return(TRUE);
411:
412: -- Update the calling sequence
413: --
414: current_calling_sequence :=
415: 'AP_INVOICE_DISTRIBUTIONS_PKG.All_Encumbered<-'||X_Calling_Sequence;
416:
417: debug_info := 'Select from sys.dual';
418:
419: select 'There are encumbered dists'
419: select 'There are encumbered dists'
420: into dummy
421: from sys.dual
422: where not exists (select 'There are other unencumbered dists'
423: from ap_invoice_distributions
424: where invoice_id = X_Invoice_Id
425: and NVL(match_status_flag, 'N') <> 'A'
426: and rowid <> X_Rowid);
427:
483:
484: -- Update the calling sequence
485: --
486: current_calling_sequence :=
487: 'AP_INVOICE_DISTRIBUTIONS_PKG.Check_Cash_Basis_Paid<-'||X_Calling_Sequence;
488:
489: debug_info := 'Select accounting method from SOB';
490:
491: SELECT NVL(sla_ledger_cash_basis_flag, 'N')
569:
570: BEGIN
571:
572: l_api_name := 'Adjust_Po';
573: current_calling_sequence := 'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_PO<-'||X_Calling_Sequence;
574:
575: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
576: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
577: END IF;
572: l_api_name := 'Adjust_Po';
573: current_calling_sequence := 'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_PO<-'||X_Calling_Sequence;
574:
575: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
576: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
577: END IF;
578:
579: l_debug_info := 'Create l_po_ap_dist_rec object';
580: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
627: X_Msg_Data => l_msg_data);
628:
629:
630: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
631: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
632: END IF;
633:
634: EXCEPTION
635: WHEN OTHERS THEN
697: END Substrbyte;
698:
699: -----------------------------------------------------------------------
700: -- FUNCTION insert_from_dist_set inserts records into
701: -- ap_invoice_distributions given a line number, distribution set id and
702: -- table of distribution information. It returns FALSE if an error
703: -- is encountered.
704: -- Called from ap_invoice_lines_pkg.insert_from_dist_set or from Import
705: -----------------------------------------------------------------------
713: X_Debug_Context OUT NOCOPY VARCHAR2,
714: X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
715: IS
716:
717: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
718: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
719: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
720: l_last_update_login
721: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
714: X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
715: IS
716:
717: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
718: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
719: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
720: l_last_update_login
721: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
722: l_existing_distributions NUMBER := 0;
715: IS
716:
717: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
718: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
719: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
720: l_last_update_login
721: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
722: l_existing_distributions NUMBER := 0;
723: i BINARY_INTEGER := 0;
717: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
718: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
719: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
720: l_last_update_login
721: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
722: l_existing_distributions NUMBER := 0;
723: i BINARY_INTEGER := 0;
724: current_calling_sequence VARCHAR2(2000);
725: debug_info VARCHAR2(100);
742: --------------------------------------------------------------------------
743: -- Step 1 - Update the calling sequence
744: --------------------------------------------------------------------------
745: current_calling_sequence :=
746: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_dist_set<-'||
747: X_calling_sequence;
748:
749: --Bug16324208: Start
750: -------------------------------------------------------------------------
770: debug_info := 'Verify line does not contain distributions';
771: BEGIN
772: SELECT count(*)
773: INTO l_existing_distributions
774: FROM ap_invoice_distributions
775: WHERE invoice_id = X_invoice_id
776: AND invoice_line_number = X_line_number
777: AND rownum=1; --13507856 added the rownum condition to improve the performance
778:
820: --End Bug#10241241
821:
822: FOR i in nvl(X_dist_tab.FIRST, 0) .. nvl(X_dist_tab.LAST, -1) LOOP
823:
824: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
825:
826: INSERT INTO ap_invoice_distributions (
827: batch_id,
828: invoice_id,
822: FOR i in nvl(X_dist_tab.FIRST, 0) .. nvl(X_dist_tab.LAST, -1) LOOP
823:
824: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
825:
826: INSERT INTO ap_invoice_distributions (
827: batch_id,
828: invoice_id,
829: invoice_line_number,
830: invoice_distribution_id,
1155: IF (l_dist_updated = 0 and
1156: x_dist_tab(i).stat_amount is not null and
1157: GET_UOM(X_dist_tab(i).dist_ccid,l_chart_of_accounts_id) is not null) THEN
1158:
1159: Update ap_invoice_distributions
1160: set stat_amount = X_dist_tab(i).stat_amount
1161: where invoice_id = X_invoice_id
1162: and invoice_line_number= X_line_number
1163: and distribution_line_number = X_dist_tab(i).dist_line_num;
1173:
1174: --Bug 4539462 DBI logging
1175: SELECT invoice_distribution_id
1176: BULK COLLECT INTO l_dbi_key_value_list
1177: FROM ap_invoice_distributions
1178: WHERE invoice_id = X_invoice_id;
1179:
1180: AP_DBI_PKG.Maintain_DBI_Summary
1181: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1177: FROM ap_invoice_distributions
1178: WHERE invoice_id = X_invoice_id;
1179:
1180: AP_DBI_PKG.Maintain_DBI_Summary
1181: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1182: p_operation => 'I',
1183: p_key_value1 => X_invoice_id,
1184: p_key_value_list => l_dbi_key_value_list,
1185: p_calling_sequence => current_calling_sequence);
1216:
1217:
1218: -----------------------------------------------------------------------
1219: -- PROCEDURE update_distributions updates columns in
1220: -- AP_INVOICE_DISTRIBUTIONS such as match_status_flag, base_amount,
1221: -- type_1099, income_tax_region.
1222: -- PRECONDITION: Procedure is called during POST-FORMS-COMMIT
1223: -----------------------------------------------------------------------
1224:
1257: ap_invoices.last_updated_by%TYPE;
1258: l_last_update_date
1259: ap_invoices.last_update_date%TYPE;
1260: l_invoice_distribution_id
1261: ap_invoice_distributions.invoice_distribution_id%TYPE;
1262: l_project_id
1263: ap_invoice_distributions.project_id%TYPE;
1264: l_task_id
1265: ap_invoice_distributions.task_id%TYPE;
1259: ap_invoices.last_update_date%TYPE;
1260: l_invoice_distribution_id
1261: ap_invoice_distributions.invoice_distribution_id%TYPE;
1262: l_project_id
1263: ap_invoice_distributions.project_id%TYPE;
1264: l_task_id
1265: ap_invoice_distributions.task_id%TYPE;
1266: l_award_id
1267: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1261: ap_invoice_distributions.invoice_distribution_id%TYPE;
1262: l_project_id
1263: ap_invoice_distributions.project_id%TYPE;
1264: l_task_id
1265: ap_invoice_distributions.task_id%TYPE;
1266: l_award_id
1267: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1268: l_expenditure_item_date DATE;
1269: l_expenditure_type
1263: ap_invoice_distributions.project_id%TYPE;
1264: l_task_id
1265: ap_invoice_distributions.task_id%TYPE;
1266: l_award_id
1267: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1268: l_expenditure_item_date DATE;
1269: l_expenditure_type
1270: ap_invoice_distributions.expenditure_type%TYPE;
1271: l_employee_id
1266: l_award_id
1267: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1268: l_expenditure_item_date DATE;
1269: l_expenditure_type
1270: ap_invoice_distributions.expenditure_type%TYPE;
1271: l_employee_id
1272: po_vendors.employee_id%TYPE;
1273: l_pa_quantity
1274: ap_invoice_distributions.pa_quantity%TYPE;
1270: ap_invoice_distributions.expenditure_type%TYPE;
1271: l_employee_id
1272: po_vendors.employee_id%TYPE;
1273: l_pa_quantity
1274: ap_invoice_distributions.pa_quantity%TYPE;
1275: l_dist_amount
1276: ap_invoice_distributions.amount%TYPE;
1277: l_dist_base_amount
1278: ap_invoice_distributions.base_amount%TYPE;
1272: po_vendors.employee_id%TYPE;
1273: l_pa_quantity
1274: ap_invoice_distributions.pa_quantity%TYPE;
1275: l_dist_amount
1276: ap_invoice_distributions.amount%TYPE;
1277: l_dist_base_amount
1278: ap_invoice_distributions.base_amount%TYPE;
1279: l_expenditure_organization_id
1280: ap_invoice_distributions.expenditure_organization_id%TYPE;
1274: ap_invoice_distributions.pa_quantity%TYPE;
1275: l_dist_amount
1276: ap_invoice_distributions.amount%TYPE;
1277: l_dist_base_amount
1278: ap_invoice_distributions.base_amount%TYPE;
1279: l_expenditure_organization_id
1280: ap_invoice_distributions.expenditure_organization_id%TYPE;
1281:
1282: l_vendor_id NUMBER;
1276: ap_invoice_distributions.amount%TYPE;
1277: l_dist_base_amount
1278: ap_invoice_distributions.base_amount%TYPE;
1279: l_expenditure_organization_id
1280: ap_invoice_distributions.expenditure_organization_id%TYPE;
1281:
1282: l_vendor_id NUMBER;
1283: l_vendor_site_id NUMBER;
1284: l_invoice_date DATE;
1284: l_invoice_date DATE;
1285: l_gl_allow_tax_override
1286: GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRIDE_FLAG%TYPE;
1287: l_tax_recoverable_flag
1288: ap_invoice_distributions.tax_recoverable_flag%TYPE;
1289: l_po_distribution_id
1290: ap_invoice_distributions.po_distribution_id%TYPE;
1291: l_line_location_id
1292: po_distributions.line_location_id%TYPE;
1286: GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRIDE_FLAG%TYPE;
1287: l_tax_recoverable_flag
1288: ap_invoice_distributions.tax_recoverable_flag%TYPE;
1289: l_po_distribution_id
1290: ap_invoice_distributions.po_distribution_id%TYPE;
1291: l_line_location_id
1292: po_distributions.line_location_id%TYPE;
1293: l_accrue_on_receipt
1294: po_distributions.accrue_on_receipt_flag%TYPE;
1294: po_distributions.accrue_on_receipt_flag%TYPE;
1295: l_dist_count NUMBER;
1296: l_user_id NUMBER;
1297: l_dist_attribute_category
1298: ap_invoice_distributions.attribute_category%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1295: l_dist_count NUMBER;
1296: l_user_id NUMBER;
1297: l_dist_attribute_category
1298: ap_invoice_distributions.attribute_category%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1296: l_user_id NUMBER;
1297: l_dist_attribute_category
1298: ap_invoice_distributions.attribute_category%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1297: l_dist_attribute_category
1298: ap_invoice_distributions.attribute_category%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1298: ap_invoice_distributions.attribute_category%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1299: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1300: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1301: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1302: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1303: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1304: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1305: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1306: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1307: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1315: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1308: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1315: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1316: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1309: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1315: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1316: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1317: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1310: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1315: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1316: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1317: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1318: l_inv_attribute2 ap_invoices.attribute1%TYPE;
1311: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1312: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1313: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1314: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1315: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1316: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1317: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1318: l_inv_attribute2 ap_invoices.attribute1%TYPE;
1319: l_inv_attribute3 ap_invoices.attribute1%TYPE;
1351: l_key_value_list ap_dbi_pkg.r_dbi_key_value_arr; -- bug 9772522
1352: l_line_number
1353: ap_invoice_lines.line_number%TYPE;
1354: l_rounded_dist_id
1355: ap_invoice_distributions.invoice_distribution_id%type;
1356: l_round_amt_exists BOOLEAN := FALSE;
1357: l_rounded_amt NUMBER;
1358: --Bug 4539462 DBI logging
1359: l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1439: AI.attribute13,
1440: AI.attribute14,
1441: AI.attribute15,
1442: gsob.chart_of_accounts_id
1443: FROM ap_invoice_distributions AID,
1444: ap_invoices AI,
1445: ap_suppliers PV,
1446: po_distributions PD,
1447: ap_system_parameters ap,
1462: --record changes before reaching the PA code.
1463: BEGIN
1464: -- Update the calling sequence
1465: current_calling_sequence :=
1466: 'AP_INVOICE_DISTRIBUTIONS_PKG.update_distributions<-'||
1467: X_calling_sequence;
1468:
1469: /*-----------------------------------------------------------------+
1470: | First, assume that an update to AP_INVOICE_DISTRIBUTIONS |
1466: 'AP_INVOICE_DISTRIBUTIONS_PKG.update_distributions<-'||
1467: X_calling_sequence;
1468:
1469: /*-----------------------------------------------------------------+
1470: | First, assume that an update to AP_INVOICE_DISTRIBUTIONS |
1471: | will not occur. We will override the this default value |
1472: | if an update does occur. |
1473: +-----------------------------------------------------------------*/
1474:
1528: WHERE sp.org_id = l_org_id
1529: AND sp.org_id = fsp.org_id;
1530:
1531: ---------------------------------------------------------
1532: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS tax info';
1533: ---------------------------------------------------------
1534:
1535: /*-----------------------------------------------------------------+
1536: | Update 1099 type and income tax region for each distribution |
1537: | line if the vendor changed (test performed in PRE-UPDATE) |
1538: +-----------------------------------------------------------------*/
1539:
1540: IF (nvl(X_vendor_changed_flag,'N') = 'Y') THEN
1541: UPDATE ap_invoice_distributions
1542: SET type_1099 = X_type_1099,
1543: income_tax_region = X_income_tax_region
1544: WHERE invoice_id = X_invoice_id
1545: AND invoice_line_number = NVL(X_line_number, invoice_line_number);
1553:
1554: END IF; -- end of x_vendor_changed_flag
1555:
1556: --------------------------------------------------------------
1557: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS match status';
1558: --------------------------------------------------------------
1559:
1560: -- Reset the match status flags if X_reset_match_status
1561: -- is Y and encumbrance is not on.
1561: -- is Y and encumbrance is not on.
1562: -- Don't change NULLS to N as these have never been through
1563: -- AutoApproval
1564: --
1565: UPDATE ap_invoice_distributions
1566: SET match_status_flag = 'N'
1567: WHERE invoice_id = X_invoice_id
1568: AND invoice_line_number = NVL( x_line_number, invoice_line_number)
1569: --Bug 5003892 AND l_purch_encumbrance_flag <> 'Y'
1578: BULK COLLECT INTO l_dbi_key_value_list;
1579:
1580: --Bug 4539462 DBI logging
1581: AP_DBI_PKG.Maintain_DBI_Summary
1582: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1583: p_operation => 'U',
1584: p_key_value1 => X_invoice_id,
1585: p_key_value_list => l_dbi_key_value_list,
1586: p_calling_sequence => current_calling_sequence);
1603: -- all the base columns
1604: -- Set the exchange rate info, base amount and WHO columns
1605: -- for the distributions
1606:
1607: --Bugfix:4644053 changed ap_invoice_distributions
1608: --to ap_invoice_distributions_all
1609: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1610:
1611: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1604: -- Set the exchange rate info, base amount and WHO columns
1605: -- for the distributions
1606:
1607: --Bugfix:4644053 changed ap_invoice_distributions
1608: --to ap_invoice_distributions_all
1609: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1610:
1611: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1612: SET base_amount =
1605: -- for the distributions
1606:
1607: --Bugfix:4644053 changed ap_invoice_distributions
1608: --to ap_invoice_distributions_all
1609: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1610:
1611: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1612: SET base_amount =
1613: DECODE(l_base_currency_code,
1607: --Bugfix:4644053 changed ap_invoice_distributions
1608: --to ap_invoice_distributions_all
1609: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1610:
1611: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1612: SET base_amount =
1613: DECODE(l_base_currency_code,
1614: l_invoice_currency_code, NULL,
1615: ap_utilities_pkg.ap_round_currency(
1682:
1683: -- get the existing base amount for the selected distribution
1684: select base_amount
1685: INTO l_base_amt
1686: FROM AP_INVOICE_DISTRIBUTIONS
1687: WHERE invoice_id = X_invoice_id
1688: AND invoice_line_number = l_line_number
1689: AND invoice_distribution_id = l_round_dist_id_list(i);
1690:
1694: p_rounding_amt => l_modified_dist_rounding_amt,
1695: p_next_line_rounding_amt => l_rounded_amt);
1696:
1697: -- update the calculatd base amount, rounding amount
1698: UPDATE AP_INVOICE_DISTRIBUTIONS
1699: SET base_amount = l_base_amt,
1700: rounding_amt = ABS( l_modified_dist_rounding_amt ),
1701: last_update_date = SYSDATE,
1702: last_updated_by = FND_GLOBAL.user_id,
2014:
2015: /* IF (l_dist_posted_flag = 'N') THEN
2016: IF (l_dist_reversal_flag <> 'Y') THEN
2017:
2018: UPDATE ap_invoice_distributions
2019: SET dist_code_combination_id = l_dist_code_combination_id
2020: WHERE invoice_distribution_id = l_invoice_distribution_id;
2021:
2022: END IF; -- end of l_dist_reversal_flag
2031: IF (l_dist_count >0) THEN
2032: X_update_occurred := 'Y';
2033: END IF;
2034:
2035: UPDATE AP_INVOICE_DISTRIBUTIONS
2036: SET last_update_login = l_last_update_login
2037: WHERE invoice_id = X_invoice_id
2038: AND last_update_login = -3;
2039: -- CHANGES FOR BUG - 3025688 ** ENDS **
2197: BEGIN
2198: -- Update the calling sequence
2199: --
2200: current_calling_sequence :=
2201: 'AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Row<-'||X_Calling_Sequence;
2202:
2203: debug_info := 'Calling invoice distribution handler';
2204:
2205: AP_AID_TABLE_HANDLER_PKG.Insert_Row
2643: RELATED_ID,
2644: ASSET_BOOK_TYPE_CODE,
2645: ASSET_CATEGORY_ID,
2646: INTENDED_USE
2647: FROM AP_INVOICE_DISTRIBUTIONS
2648: WHERE rowid = X_Rowid
2649: FOR UPDATE of Invoice_Id NOWAIT;
2650: Recinfo C%ROWTYPE;
2651:
2663:
2664: -- Update the calling sequence
2665: --
2666: current_calling_sequence :=
2667: 'AP_INVOICE_DISTRIBUTIONS_PKG.Lock_Row<-'||X_Calling_Sequence;
2668:
2669: debug_info := 'Select from ap_invoice_distributions';
2670:
2671: OPEN C;
2665: --
2666: current_calling_sequence :=
2667: 'AP_INVOICE_DISTRIBUTIONS_PKG.Lock_Row<-'||X_Calling_Sequence;
2668:
2669: debug_info := 'Select from ap_invoice_distributions';
2670:
2671: OPEN C;
2672:
2673: debug_info := 'Fetch cursor C';
3188:
3189: -- Update the calling sequence
3190: --
3191: current_calling_sequence :=
3192: 'AP_INVOICE_DISTRIBUTIONS_PKG.Update_Row<-'||X_Calling_Sequence;
3193:
3194: AP_AID_TABLE_HANDLER_PKG.Update_Row
3195: (X_Rowid,
3196: X_Invoice_Id,
3365: PROCEDURE Delete_Row(X_Rowid VARCHAR2,
3366: X_Calling_Sequence VARCHAR2) IS
3367: current_calling_sequence VARCHAR2(2000);
3368: debug_info VARCHAR2(100);
3369: l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
3370:
3371: BEGIN
3372:
3373: -- Update the calling sequence
3372:
3373: -- Update the calling sequence
3374: --
3375: current_calling_sequence :=
3376: 'AP_INVOICE_DISTRIBUTIONS_PKG.Delete_Row<-'||X_Calling_Sequence;
3377:
3378: AP_AID_TABLE_HANDLER_PKG.Delete_Row(
3379: X_Rowid,
3380: current_calling_sequence);
3481: -- Bug 1892826. Added the line_type_lookup_code predicate
3482: -- Bug 2404982. Added package qualifier to procedure call.
3483: SELECT sum(amount)
3484: INTO l_dist_amount
3485: FROM ap_invoice_distributions
3486: WHERE invoice_id = X_Invoice_Id
3487: AND line_type_lookup_code IN ('ITEM','PREPAY')
3488: AND nvl(reversal_flag,'N') <> 'Y'
3489: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3485: FROM ap_invoice_distributions
3486: WHERE invoice_id = X_Invoice_Id
3487: AND line_type_lookup_code IN ('ITEM','PREPAY')
3488: AND nvl(reversal_flag,'N') <> 'Y'
3489: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3490: dist_code_combination_id, X_Sob_Id) =
3491: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3492: X_Prepay_Dist_CCID, X_Sob_Id);
3493:
3487: AND line_type_lookup_code IN ('ITEM','PREPAY')
3488: AND nvl(reversal_flag,'N') <> 'Y'
3489: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3490: dist_code_combination_id, X_Sob_Id) =
3491: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3492: X_Prepay_Dist_CCID, X_Sob_Id);
3493:
3494: RETURN (l_dist_amount);
3495:
3513:
3514: -- Bug 2404982. Added package qualifier to procedure call.
3515: CURSOR c_prepay_dist IS
3516: SELECT sum(nvl(prepay_amount_remaining,amount)),
3517: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3518: aip.dist_code_combination_id, X_Sob_Id)
3519: FROM ap_invoice_distributions aip
3520: WHERE aip.invoice_id = X_Prepay_Id
3521: --bugfix:3881673
3515: CURSOR c_prepay_dist IS
3516: SELECT sum(nvl(prepay_amount_remaining,amount)),
3517: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3518: aip.dist_code_combination_id, X_Sob_Id)
3519: FROM ap_invoice_distributions aip
3520: WHERE aip.invoice_id = X_Prepay_Id
3521: --bugfix:3881673
3522: AND aip.line_type_lookup_code in ('ITEM','ACCRUAL')
3523: AND nvl(aip.reversal_flag,'N') <> 'Y'
3521: --bugfix:3881673
3522: AND aip.line_type_lookup_code in ('ITEM','ACCRUAL')
3523: AND nvl(aip.reversal_flag,'N') <> 'Y'
3524: AND nvl(aip.prepay_amount_remaining,amount) > 0
3525: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3526: aip.dist_code_combination_id, X_Sob_Id) IN
3527: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3528: aid.dist_code_combination_id, X_Sob_Id)
3529: FROM ap_invoice_distributions aid
3523: AND nvl(aip.reversal_flag,'N') <> 'Y'
3524: AND nvl(aip.prepay_amount_remaining,amount) > 0
3525: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3526: aip.dist_code_combination_id, X_Sob_Id) IN
3527: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3528: aid.dist_code_combination_id, X_Sob_Id)
3529: FROM ap_invoice_distributions aid
3530: WHERE aid.invoice_id = X_Invoice_ID)
3531: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3525: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3526: aip.dist_code_combination_id, X_Sob_Id) IN
3527: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3528: aid.dist_code_combination_id, X_Sob_Id)
3529: FROM ap_invoice_distributions aid
3530: WHERE aid.invoice_id = X_Invoice_ID)
3531: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3532: aip.dist_code_combination_id, X_Sob_Id)
3533: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3527: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3528: aid.dist_code_combination_id, X_Sob_Id)
3529: FROM ap_invoice_distributions aid
3530: WHERE aid.invoice_id = X_Invoice_ID)
3531: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3532: aip.dist_code_combination_id, X_Sob_Id)
3533: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3534: aip.dist_code_combination_id, X_Sob_Id);
3535:
3529: FROM ap_invoice_distributions aid
3530: WHERE aid.invoice_id = X_Invoice_ID)
3531: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3532: aip.dist_code_combination_id, X_Sob_Id)
3533: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3534: aip.dist_code_combination_id, X_Sob_Id);
3535:
3536: BEGIN
3537:
3542:
3543: -- Bug 2404982. Added package qualifier to procedure call
3544: SELECT sum(amount)
3545: INTO l_invoice_amount
3546: FROM ap_invoice_distributions
3547: WHERE invoice_id = X_Invoice_ID
3548: AND line_type_lookup_code IN ('ITEM','PREPAY')
3549: AND nvl(reversal_flag,'N') <> 'Y'
3550: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3546: FROM ap_invoice_distributions
3547: WHERE invoice_id = X_Invoice_ID
3548: AND line_type_lookup_code IN ('ITEM','PREPAY')
3549: AND nvl(reversal_flag,'N') <> 'Y'
3550: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3551: dist_code_combination_id, X_Sob_Id)
3552: = l_bal_segment;
3553:
3554: IF l_invoice_amount <> 0 THEN
3577: BEGIN
3578:
3579: --Bug 2404982. Added package qualifier to procedure call.
3580: SELECT count(distinct(
3581: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3582: dist_code_combination_id, X_Sob_Id)) )
3583: INTO l_dist_count
3584: FROM ap_invoice_distributions
3585: WHERE invoice_id = X_Invoice_Id;
3580: SELECT count(distinct(
3581: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3582: dist_code_combination_id, X_Sob_Id)) )
3583: INTO l_dist_count
3584: FROM ap_invoice_distributions
3585: WHERE invoice_id = X_Invoice_Id;
3586:
3587: IF l_dist_count > 1 THEN
3588: RETURN (TRUE);
3708:
3709: -- Update current calling sequence
3710: --
3711: current_calling_sequence :=
3712: 'AP_INVOICE_DISTRIBUTIONS_PKG.Round_Biggest_Distribution<-'
3713: ||X_Calling_Sequence;
3714:
3715: debug_info := 'Adjusting distribution amount in biggest distribution';
3716:
3714:
3715: debug_info := 'Adjusting distribution amount in biggest distribution';
3716:
3717: -- bug 5052661 -- modified SELECT portion of SQL in where clause to go to base tables to avoid FTS
3718: UPDATE ap_invoice_distributions d1
3719: SET base_amount =
3720: (SELECT DECODE(SIGN(SUM(d2.base_amount) -
3721: DECODE(max(f.minimum_accountable_unit), NULL,
3722: ROUND((SUM(d2.amount)
3742: ROUND((SUM(d2.amount) * i.exchange_rate)
3743: /MAX(f.minimum_accountable_unit))*
3744: MAX(f.minimum_accountable_unit))),
3745: d1.base_amount)
3746: FROM ap_invoices i, ap_invoice_distributions d2, fnd_currencies F
3747: WHERE d1.invoice_id = i.invoice_id
3748: AND d1.invoice_id = d2.invoice_id
3749: AND f.currency_code = X_Base_Currency_Code
3750: GROUP BY i.exchange_rate)
3751: WHERE d1.invoice_id = X_Invoice_Id
3752: AND d1.posted_flag = 'N'
3753: AND (d1.invoice_id, d1.distribution_line_number) IN
3754: (SELECT d5.invoice_id, MAX(d5.distribution_line_number)
3755: FROM ap_invoice_distributions_all d5
3756: WHERE (d5.invoice_id, abs(d5.amount)) IN
3757: (SELECT i1.invoice_id , MAX(ABS(d3.amount))
3758: FROM ap_invoices_all i1, ap_invoice_distributions_all d3
3759: WHERE i1.invoice_id = X_Invoice_Id
3754: (SELECT d5.invoice_id, MAX(d5.distribution_line_number)
3755: FROM ap_invoice_distributions_all d5
3756: WHERE (d5.invoice_id, abs(d5.amount)) IN
3757: (SELECT i1.invoice_id , MAX(ABS(d3.amount))
3758: FROM ap_invoices_all i1, ap_invoice_distributions_all d3
3759: WHERE i1.invoice_id = X_Invoice_Id
3760: AND i1.invoice_id = d3.invoice_id
3761: AND d3.line_type_lookup_code NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV','IPV', 'ERV') -- Bug 11654030 <> 'TAX', Bug 12691958: Added excluding 'IPV' and 'ERV' lines
3762: AND d3.related_id IS NULL -- Bug 13864403
3761: AND d3.line_type_lookup_code NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV','IPV', 'ERV') -- Bug 11654030 <> 'TAX', Bug 12691958: Added excluding 'IPV' and 'ERV' lines
3762: AND d3.related_id IS NULL -- Bug 13864403
3763: AND NOT EXISTS
3764: (SELECT d4.invoice_id
3765: FROM ap_invoice_distributions_all d4
3766: WHERE d4.invoice_id = X_Invoice_Id
3767: AND (1 = DECODE(X_Base_Currency_Code,
3768: i1.invoice_currency_code,1,
3769: DECODE(d4.exchange_rate,null,1,0))
3789:
3790:
3791: -----------------------------------------------------------------------
3792: -- FUNCTION insert_single_dist_from_line inserts a record into
3793: -- ap_invoice_distributions given a line number number and/or line record.
3794: -- It returns FALSE if an error is encountered.
3795: -----------------------------------------------------------------------
3796: FUNCTION Insert_Single_Dist_From_Line(
3797: X_batch_id IN AP_INVOICES.BATCH_ID%TYPE,
3981: l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
3982: l_invoice_attribute_rec AP_UTILITIES_PKG.r_invoice_attribute_rec; --bug 8713737
3983:
3984: l_distribution_class
3985: AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
3986: l_dist_ccid
3987: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
3988: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3989: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3983:
3984: l_distribution_class
3985: AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
3986: l_dist_ccid
3987: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
3988: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3989: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3990: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
3991: l_assets_tracking_flag
3988: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3989: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3990: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
3991: l_assets_tracking_flag
3992: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
3993: l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
3994: l_accounting_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
3995: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
3996: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
4013: --bug 8980626, size should be 2000 as error messages are assigned to it
4014: debug_context VARCHAR2(2000);
4015: l_dist_code_concat VARCHAR2(2000);
4016: l_invoice_distribution_id
4017: ap_invoice_distributions.invoice_distribution_id%TYPE;
4018: l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
4019: l_sys_link_function VARCHAR2(2); /* Bug 5102724 */
4020: l_web_parameter_id number; --Bug5003249
4021: l_employee_ccid number;
4030: --------------------------------------------------------------------------
4031: -- Step 1 - Update the calling sequence
4032: --------------------------------------------------------------------------
4033: current_calling_sequence :=
4034: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_single_dist_from_line <-'||
4035: X_calling_sequence;
4036:
4037: --------------------------------------------------------------------------
4038: -- Step 2 - If calling module provided X_invoice_id / X_line_number, then
4073: debug_info := 'Verify line does not contain distributions';
4074: BEGIN
4075: SELECT count(*)
4076: INTO l_existing_distributions
4077: FROM ap_invoice_distributions
4078: WHERE invoice_id = l_invoice_line_rec.invoice_id
4079: AND invoice_line_number = l_invoice_line_rec.line_number
4080: AND rownum=1; --13507856 added the rownum condition to improve the performance
4081:
4657: -- Step 11 - Generate distributions
4658: ----------------------------------------------------------------------------
4659: BEGIN
4660:
4661: INSERT INTO ap_invoice_distributions(
4662: batch_id,
4663: invoice_id,
4664: invoice_line_number,
4665: invoice_distribution_id,
4811: pay_awt_group_id)
4812: VALUES (X_batch_id, -- batch_id
4813: l_invoice_line_rec.invoice_id, -- invoice_id
4814: l_invoice_line_rec.line_number, -- invoice_line_number
4815: ap_invoice_distributions_s.nextval, -- invoice_distribution_id
4816: 1, -- distribution_line_number
4817: l_invoice_line_rec.line_type_lookup_code, -- line_type_lookup_code
4818: l_distribution_class, -- distribution_class
4819: l_invoice_line_rec.description, -- description
5041:
5042: --Bug 8346277 Start
5043: -----------------------------------------------------------------------
5044: -- FUNCTION insert_AWT_dist_from_line inserts a record into
5045: -- ap_invoice_distributions given a line number number and/or line record.
5046: -- It returns FALSE if an error is encountered.
5047: -----------------------------------------------------------------------
5048:
5049: FUNCTION Insert_AWT_Dist_From_Line(
5228:
5229:
5230: l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
5231:
5232: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
5233: l_dist_ccid AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5234: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5235: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
5236: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
5229:
5230: l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
5231:
5232: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
5233: l_dist_ccid AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5234: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5235: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
5236: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
5237: l_assets_tracking_flag AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5233: l_dist_ccid AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5234: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5235: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
5236: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
5237: l_assets_tracking_flag AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5238: l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
5239: l_accounting_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
5240: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
5241: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
5256: current_calling_sequence VARCHAR2(2000);
5257: debug_info VARCHAR2(2000); --Bug 8725625-Increased width
5258: debug_context VARCHAR2(2000);
5259: l_dist_code_concat VARCHAR2(2000);
5260: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
5261: l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
5262: l_sys_link_function VARCHAR2(2);
5263: l_web_parameter_id number;
5264: l_employee_ccid number;
5267: l_Inc_AWT_For_Tax_Flag Number :=1;
5268: l_dist_total number;
5269:
5270: /* bug 10201001 begins */
5271: -- l_accounting_event_id ap_invoice_distributions.accounting_event_id%TYPE := -1; --bug 9216708
5272: l_no_dists_exists NUMBER := 0; --bug 9216708
5273:
5274: cursor events_cur(p_invoice_id NUMBER,p_invoice_line_num NUMBER) is
5275: select distinct accounting_event_id
5272: l_no_dists_exists NUMBER := 0; --bug 9216708
5273:
5274: cursor events_cur(p_invoice_id NUMBER,p_invoice_line_num NUMBER) is
5275: select distinct accounting_event_id
5276: from ap_invoice_distributions_all
5277: where invoice_id = p_invoice_id
5278: and invoice_line_number = p_invoice_line_num
5279: and posted_flag <> 'Y';
5280:
5277: where invoice_id = p_invoice_id
5278: and invoice_line_number = p_invoice_line_num
5279: and posted_flag <> 'Y';
5280:
5281: TYPE EventTab IS TABLE OF ap_invoice_distributions.accounting_event_id%TYPE INDEX BY BINARY_INTEGER;
5282: l_accounting_event_id_list EventTab;
5283:
5284: /* bug 10201001 ends */
5285: BEGIN
5286: --------------------------------------------------------------------------
5287: -- Step 1 - Update the calling sequence
5288: --------------------------------------------------------------------------
5289: current_calling_sequence :=
5290: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_single_dist_from_line <-'||
5291: X_calling_sequence;
5292:
5293: --------------------------------------------------------------------------
5294: -- Step 2 - If calling module provided X_invoice_id / X_line_number, then
5337: debug_info := 'Verify line does not contain distributions';
5338: BEGIN
5339: SELECT count(*)
5340: INTO l_existing_distributions
5341: FROM ap_invoice_distributions
5342: WHERE invoice_id = l_invoice_line_rec.invoice_id
5343: AND invoice_line_number = l_invoice_line_rec.line_number
5344: AND nvl(posted_flag,'N') <> 'N'
5345: AND rownum=1; --13507856 added the rownum condition to improve the performance
5509:
5510:
5511: SELECT SUM(AMOUNT)
5512: INTO l_dist_total
5513: FROM ap_invoice_distributions_all aid
5514: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5515: AND line_type_lookup_code NOT IN('AWT' , 'PREPAY','ERV','TERV')
5516: AND NOT (aid.line_type_lookup_code IN ('REC_TAX' , 'NONREC_TAX', 'TIPV', 'TRV')
5517: AND aid.prepay_distribution_id is not null) /*Bug11937214*/
5525: /* commented the following code
5526: -- bug 9216708 start
5527: select count(*)
5528: into l_dists_exists
5529: from ap_invoice_distributions_all
5530: where invoice_id = l_invoice_line_rec.invoice_id
5531: and accounting_event_id is NOT NULL
5532: and line_type_lookup_code = 'AWT'
5533: and awt_flag = 'M'
5535:
5536: IF l_dists_exists > 0 THEN
5537: SELECT nvl(max(aid.accounting_event_id),-1)
5538: INTO l_accounting_event_id
5539: FROM ap_invoice_distributions_all aid
5540: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5541: AND aid.line_type_lookup_code = 'AWT'
5542: AND aid.awt_flag = 'M'
5543: AND NVL(posted_flag , 'N') <> 'Y';
5548: FETCH events_cur BULK COLLECT INTO l_accounting_event_id_list;
5549: CLOSE events_cur;
5550:
5551: /* bug 10201001 end */
5552: DELETE FROM ap_invoice_distributions_all aid
5553: WHERE invoice_id = l_invoice_line_rec.invoice_id
5554: AND invoice_line_number = l_invoice_line_rec.line_number
5555: AND NVL(posted_flag , 'N') <> 'Y';
5556:
5567: FROM ap_self_assessed_tax_dist_all asatd
5568: WHERE asatd.invoice_id = l_invoice_line_rec.invoice_id
5569: AND asatd.accounting_event_id = xe.event_id)
5570: AND NOT EXISTS (SELECT 'No Invoice rows exist for this event'
5571: FROM ap_invoice_distributions_all aid
5572: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5573: AND aid.accounting_event_id = xe.event_id)
5574: AND NOT EXISTS (SELECT 'No payment rows exist for this event'
5575: FROM ap_invoice_payments_all aip,
5590: END LOOP;
5591: /* bug 10201001 end */
5592: debug_info := ' Before Insert AwT Distributions ';
5593:
5594: INSERT INTO ap_invoice_distributions_all
5595: ( batch_id
5596: , invoice_id
5597: , invoice_line_number
5598: , invoice_distribution_id
5743: )
5744: SELECT X_batch_id batch_id
5745: , l_invoice_line_rec.invoice_id invoice_id
5746: , l_invoice_line_rec.line_number invoice_line_number
5747: , ap_invoice_distributions_s.nextval invoice_distribution_id
5748: , rownum distribution_line_number
5749: , 'AWT' line_type_lookup_code
5750: , l_distribution_class distribution_class
5751: , l_invoice_line_rec.description description
5905: , l_invoice_line_rec.primary_intended_use intended_use
5906: , 'N' rcv_charge_addition_flag
5907: , l_invoice_line_rec.pay_awt_group_id pay_awt_group_id
5908: , aid.invoice_distribution_id awt_related_id
5909: FROM ap_invoice_distributions_all aid
5910: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5911: AND aid.line_type_lookup_code NOT IN('AWT' , 'PREPAY','ERV','TERV')
5912: AND NOT (aid.line_type_lookup_code IN ('REC_TAX' , 'NONREC_TAX', 'TIPV', 'TRV')
5913: AND aid.prepay_distribution_id is not null) /*Bug11937214*/
5918:
5919: debug_info := ' After Insert AwT Distributions ';
5920: end if;
5921:
5922: UPDATE ap_invoice_distributions_all aid
5923: SET aid.amount = aid.amount -
5924: ((
5925: SELECT SUM(aid1.amount)
5926: FROM ap_invoice_distributions_all aid1
5922: UPDATE ap_invoice_distributions_all aid
5923: SET aid.amount = aid.amount -
5924: ((
5925: SELECT SUM(aid1.amount)
5926: FROM ap_invoice_distributions_all aid1
5927: WHERE aid1.invoice_id = aid.invoice_id
5928: AND aid1.invoice_line_number = aid.invoice_line_number
5929: )- l_invoice_line_rec.amount)
5930: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5930: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5931: AND aid.invoice_line_number = l_invoice_line_rec.line_number
5932: AND ABS(aid.amount) =
5933: ( SELECT MAX(ABS(aid1.amount))
5934: FROM ap_invoice_distributions_all aid1
5935: WHERE aid1.invoice_id = aid.invoice_id
5936: AND aid1.invoice_line_number = aid.invoice_line_number)
5937: AND rownum = 1;
5938:
5937: AND rownum = 1;
5938:
5939: IF X_exchange_rate_type is not null then
5940:
5941: UPDATE ap_invoice_distributions_all aid
5942: SET aid.base_amount = aid.base_amount -
5943: ((
5944: SELECT SUM(aid1.base_amount)
5945: FROM ap_invoice_distributions_all aid1
5941: UPDATE ap_invoice_distributions_all aid
5942: SET aid.base_amount = aid.base_amount -
5943: ((
5944: SELECT SUM(aid1.base_amount)
5945: FROM ap_invoice_distributions_all aid1
5946: WHERE aid1.invoice_id = aid.invoice_id
5947: AND aid1.invoice_line_number = aid.invoice_line_number
5948: )- l_invoice_line_rec.base_amount)
5949: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5949: WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
5950: AND aid.invoice_line_number = l_invoice_line_rec.line_number
5951: AND ABS(aid.base_amount) =
5952: ( SELECT MAX(ABS(aid1.base_amount))
5953: FROM ap_invoice_distributions_all aid1
5954: WHERE aid1.invoice_id = aid.invoice_id
5955: AND aid1.invoice_line_number = aid.invoice_line_number)
5956: AND rownum = 1;
5957:
6025: X_invoice_currency_code IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
6026: X_sob_id IN AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE,
6027: X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
6028: X_base_currency_code IN AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE,
6029: X_amount IN AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE,
6030: X_base_amount IN AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE,
6031: X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
6032: X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
6033: X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
6026: X_sob_id IN AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE,
6027: X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
6028: X_base_currency_code IN AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE,
6029: X_amount IN AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE,
6030: X_base_amount IN AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE,
6031: X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
6032: X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
6033: X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
6034: X_line_number IN NUMBER,
6041: X_chrg_line_exp_org_id IN AP_INVOICE_LINES.EXPENDITURE_ORGANIZATION_ID%TYPE,
6042: X_chrg_assets_track_flag IN AP_INVOICE_LINES.ASSETS_TRACKING_FLAG%TYPE,
6043: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
6044: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
6045: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6042: X_chrg_assets_track_flag IN AP_INVOICE_LINES.ASSETS_TRACKING_FLAG%TYPE,
6043: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
6044: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
6045: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6043: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
6044: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
6045: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6044: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
6045: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6052: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6045: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6052: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6053: X_chrg_line_default_ccid IN NUMBER,
6046: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6052: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6053: X_chrg_line_default_ccid IN NUMBER,
6054: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
6047: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6052: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6053: X_chrg_line_default_ccid IN NUMBER,
6054: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
6055: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
6048: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6049: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6050: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6051: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6052: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6053: X_chrg_line_default_ccid IN NUMBER,
6054: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
6055: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
6056: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
6054: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
6055: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
6056: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
6057: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
6058: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
6059: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
6060: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
6061: X_pa_allows_overrides IN VARCHAR2,
6062: X_allow_po_override IN VARCHAR2,
6055: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
6056: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
6057: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
6058: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
6059: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
6060: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
6061: X_pa_allows_overrides IN VARCHAR2,
6062: X_allow_po_override IN VARCHAR2,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6056: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
6057: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
6058: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
6059: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
6060: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
6061: X_pa_allows_overrides IN VARCHAR2,
6062: X_allow_po_override IN VARCHAR2,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6059: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
6060: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
6061: X_pa_allows_overrides IN VARCHAR2,
6062: X_allow_po_override IN VARCHAR2,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6060: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
6061: X_pa_allows_overrides IN VARCHAR2,
6062: X_allow_po_override IN VARCHAR2,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6062: X_allow_po_override IN VARCHAR2,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6063: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6064: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6072: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6065: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
6066: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6072: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6073: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6067: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6072: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6073: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6074: X_error_code OUT NOCOPY VARCHAR2,
6075: X_msg_application OUT NOCOPY VARCHAR2,
6068: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6072: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6073: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6074: X_error_code OUT NOCOPY VARCHAR2,
6075: X_msg_application OUT NOCOPY VARCHAR2,
6076: X_msg_data OUT NOCOPY VARCHAR2,
6069: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
6070: X_account_for_dist OUT NOCOPY NUMBER,
6071: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
6072: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
6073: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
6074: X_error_code OUT NOCOPY VARCHAR2,
6075: X_msg_application OUT NOCOPY VARCHAR2,
6076: X_msg_data OUT NOCOPY VARCHAR2,
6077: X_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE) --bug 5386396
6079: IS
6080:
6081: l_po_accrue_on_receipt_flag PO_DISTRIBUTIONS.ACCRUE_ON_RECEIPT_FLAG%TYPE;
6082: l_po_ccid PO_DISTRIBUTIONS.CODE_COMBINATION_ID%TYPE;
6083: l_dist_ccid AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6084: l_dist_code_concat VARCHAR2(2000);
6085: l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
6086: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
6087: user_id NUMBER;
6635: ARL.percentage,
6636: ARL.amount,
6637: Sum(AID.amount)
6638: FROM AP_Allocation_Rule_Lines ARL,
6639: AP_invoice_distributions_all AID
6640: WHERE ARL.invoice_id = X_invoice_id
6641: AND AID.invoice_id = X_invoice_id
6642: AND ARL.chrg_invoice_line_number = X_line_number
6643: AND AID.invoice_line_number = ARL.to_invoice_line_number
6652: SELECT AIL.line_number,
6653: AIL.amount,
6654: sum(AID.amount)
6655: FROM AP_invoice_lines AIL,
6656: AP_invoice_distributions_all AID
6657: WHERE AIL.invoice_id = X_invoice_id
6658: AND nvl(AIL.discarded_flag, 'N') = 'N'
6659: AND nvl(AIL.cancelled_flag, 'N') = 'N'
6660: AND AIL.amount <> 0
6677: AID.pa_quantity, -- bug6699834
6678: AID.expenditure_type,
6679: AID.expenditure_organization_id,
6680: AID.award_id,
6681: AP_INVOICE_DISTRIBUTIONS_PKG.GET_TOTAL_DIST_AMOUNT(
6682: aid.invoice_distribution_id),
6683: AID.dist_code_combination_id,
6684: AID.assets_tracking_flag,
6685: AID.asset_book_type_code,
6684: AID.assets_tracking_flag,
6685: AID.asset_book_type_code,
6686: AID.asset_category_id,
6687: AID.description
6688: FROM ap_invoice_distributions_all AID
6689: WHERE AID.invoice_id = X_invoice_id
6690: AND AID.invoice_line_number = P_line_number
6691: AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL')
6692: ORDER BY AID.distribution_line_number;
6691: AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL')
6692: ORDER BY AID.distribution_line_number;
6693:
6694:
6695: l_batch_id AP_INVOICE_DISTRIBUTIONS.BATCH_ID%TYPE;
6696: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
6697: l_org_id AP_INVOICE_LINES.ORG_ID%TYPE;
6698: l_sob_id AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE;
6699: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
6692: ORDER BY AID.distribution_line_number;
6693:
6694:
6695: l_batch_id AP_INVOICE_DISTRIBUTIONS.BATCH_ID%TYPE;
6696: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
6697: l_org_id AP_INVOICE_LINES.ORG_ID%TYPE;
6698: l_sob_id AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE;
6699: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
6700: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
6736: l_income_tax_region AP_INVOICE_LINES.INCOME_TAX_REGION%TYPE;
6737: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
6738: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
6739: l_invoice_distribution_id
6740: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6737: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
6738: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
6739: l_invoice_distribution_id
6740: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6738: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
6739: l_invoice_distribution_id
6740: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6739: l_invoice_distribution_id
6740: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6740: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6741: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6742: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6743: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
6744: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6745: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6752: l_dist_code_combination_id
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6746: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6752: l_dist_code_combination_id
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6754: l_dist_assets_tracking_flag
6747: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
6748: l_dist_expenditure_org_id
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6752: l_dist_code_combination_id
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6754: l_dist_assets_tracking_flag
6755: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
6749: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
6750: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6752: l_dist_code_combination_id
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6754: l_dist_assets_tracking_flag
6755: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
6756: l_dist_asset_book_type_code
6757: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
6751: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
6752: l_dist_code_combination_id
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6754: l_dist_assets_tracking_flag
6755: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
6756: l_dist_asset_book_type_code
6757: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
6758: l_dist_asset_category_id
6759: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
6753: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
6754: l_dist_assets_tracking_flag
6755: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
6756: l_dist_asset_book_type_code
6757: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
6758: l_dist_asset_category_id
6759: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
6760: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
6761: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
6755: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
6756: l_dist_asset_book_type_code
6757: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
6758: l_dist_asset_category_id
6759: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
6760: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
6761: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
6762: l_alloc_line_tab AP_INVOICE_LINES_PKG.alloc_line_tab_type;
6763: l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
6756: l_dist_asset_book_type_code
6757: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
6758: l_dist_asset_category_id
6759: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
6760: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
6761: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
6762: l_alloc_line_tab AP_INVOICE_LINES_PKG.alloc_line_tab_type;
6763: l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
6764: l_rule_status AP_ALLOCATION_RULES.STATUS%TYPE;
6768: AP_ALLOCATION_RULE_LINES.TO_INVOICE_LINE_NUMBER%TYPE;
6769: l_applicable_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
6770: l_applicable_line_amount AP_INVOICE_LINES.AMOUNT%TYPE := 0;
6771: l_sum_applic_lines AP_INVOICE_LINES.AMOUNT%TYPE := 0;
6772: l_sum_dists_applic_line AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
6773: l_running_total_alloc AP_INVOICE_LINES.AMOUNT%TYPE := 0;
6774: l_running_total_base_amt AP_INVOICE_LINES.BASE_AMOUNT%TYPE := 0;
6775: l_max_alloc AP_INVOICE_LINES.AMOUNT%TYPE := 0;
6776: l_max_base_alloc AP_INVOICE_LINES.BASE_AMOUNT%TYPE := 0;
6794: l_count_applicable_dists NUMBER := 0;
6795: current_calling_sequence VARCHAR2(2000);
6796: debug_info VARCHAR2(1000);
6797:
6798: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
6799:
6800: -- Bug 5114543
6801: l_max_dist_line_num NUMBER :=0;
6802: l_chrg_line_rec ap_invoice_lines_all%rowtype;
6832: ---------------------------------------------------------------------------
6833: -- Step 1 - Update the calling sequence
6834: ---------------------------------------------------------------------------
6835: current_calling_sequence :=
6836: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_charge_from_alloc <-'
6837: ||X_calling_sequence;
6838:
6839: --------------------------------------------------------------------------
6840: -- Step 2 - Validate the line exists and get line level information
6957: debug_info := 'Verify line does not contain distributions';
6958: BEGIN
6959: SELECT count(*)
6960: INTO l_existing_distributions
6961: FROM ap_invoice_distributions
6962: WHERE invoice_id = X_invoice_id
6963: AND invoice_line_number = X_line_number
6964: AND rownum=1; --13507856 added the rownum condition to improve the performance
6965:
7103: ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION','AMOUNT_CORRECTION')
7104: --Bug 5558693
7105: AND amount <>
7106: (SELECT NVL(SUM(NVL(aid.amount,0)),0)
7107: FROM ap_invoice_distributions_all AID
7108: WHERE AID.invoice_id = ail.invoice_id
7109: AND AID.invoice_line_number = AIL.line_number
7110: AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
7111: OR (AID.prepay_distribution_id IS NOT NULL
7133: AND ARL.chrg_invoice_line_number = X_line_number
7134: AND ARL.to_invoice_line_number = AIL.line_number
7135: --Bug 5558693
7136: AND AIL.amount <> (SELECT NVL(SUM(nvl(amount,0)),0)
7137: FROM ap_invoice_distributions AID
7138: WHERE AID.invoice_id = AIL.invoice_id
7139: AND AID.invoice_line_number = AIL.line_number
7140: AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
7141: OR (AID.prepay_distribution_id IS NOT NULL
7651: -- Bug 13841774. End.
7652:
7653: FOR i in nvl(l_dist_tab.FIRST, 0) .. nvl(l_dist_tab.LAST, -1) LOOP
7654:
7655: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
7656:
7657: INSERT INTO ap_invoice_distributions(
7658: batch_id,
7659: invoice_id,
7653: FOR i in nvl(l_dist_tab.FIRST, 0) .. nvl(l_dist_tab.LAST, -1) LOOP
7654:
7655: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
7656:
7657: INSERT INTO ap_invoice_distributions(
7658: batch_id,
7659: invoice_id,
7660: invoice_line_number,
7661: invoice_distribution_id,
7962: 'N', -- rcv_charge_addition_flag
7963: l_pay_awt_group_id -- pay_awt_group_id 7022001
7964: FROM ap_invoice_lines AIL, --Charge line
7965: ap_invoice_lines AIL1, --ITEM Line
7966: ap_invoice_distributions aid
7967: WHERE ail.invoice_id = X_invoice_id
7968: AND ail.line_number = X_line_number
7969: AND aid.invoice_id = ail.invoice_id
7970: AND aid.invoice_distribution_id = l_dist_tab(i).charge_applicable_to_dist
7993: IF (l_dist_updated = 0 and
7994: l_stat_amount is not null and
7995: GET_UOM(l_dist_tab(i).dist_ccid,l_chart_of_accounts_id) is not null) THEN
7996:
7997: Update ap_invoice_distributions
7998: set stat_amount = l_stat_amount
7999: where invoice_id = X_invoice_id
8000: and invoice_line_number= X_line_number
8001: and distribution_line_number = l_dist_tab(i).dist_line_num;
8024: SET GENERATE_DISTS = 'D'
8025: WHERE invoice_id = X_invoice_id
8026: AND line_number = X_line_number
8027: AND amount = (select sum(amount)
8028: from ap_invoice_distributions
8029: where invoice_id = x_invoice_id
8030: and invoice_line_number = x_line_number);
8031: EXCEPTION
8032: WHEN OTHERS THEN
8054: AND chrg_invoice_line_number = X_line_number
8055: AND exists
8056: (select 'Line Amount Equals Distribution Total'
8057: from ap_invoice_lines_all ail,
8058: ap_invoice_distributions_all aid
8059: where ail.invoice_id = aid.invoice_id
8060: and ail.line_number = aid.invoice_line_number
8061: and ail.invoice_id = X_invoice_id
8062: and ail.line_number = X_line_number
8092: FUNCTION Get_Total_Dist_Amount(
8093: X_invoice_distribution_id IN NUMBER) RETURN NUMBER
8094: IS
8095:
8096: l_original_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
8097:
8098: BEGIN
8099:
8100: SELECT sum(nvl(AID.amount,0))
8098: BEGIN
8099:
8100: SELECT sum(nvl(AID.amount,0))
8101: INTO l_original_amount
8102: FROM ap_invoice_distributions_all AID
8103: WHERE AID.invoice_distribution_id = X_invoice_distribution_id
8104: OR AID.related_id = X_invoice_distribution_id;
8105:
8106:
8127: BEGIN
8128:
8129: SELECT nvl(distribution_line_number,0)
8130: INTO l_dist_line_num
8131: FROM ap_invoice_distributions
8132: WHERE invoice_distribution_id = X_invoice_dist_id;
8133:
8134: RETURN(l_dist_line_num);
8135:
8148: BEGIN
8149:
8150: SELECT nvl(invoice_line_number,0)
8151: INTO l_inv_line_num
8152: FROM ap_invoice_distributions
8153: WHERE invoice_distribution_id = X_invoice_dist_id;
8154:
8155: RETURN(l_inv_line_num);
8156:
8168: BEGIN
8169:
8170: SELECT nvl(ai.invoice_num,0)
8171: INTO l_inv_num
8172: FROM ap_invoice_distributions aid,
8173: ap_invoices ai
8174: WHERE aid.invoice_distribution_id = X_invoice_dist_id
8175: AND aid.invoice_id = ai.invoice_id;
8176:
8187: FUNCTION GET_REVERSAL_RELATED_ID(
8188: X_related_dist_id IN NUMBER )
8189: RETURN NUMBER
8190: IS
8191: l_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
8192: BEGIN
8193:
8194: BEGIN
8195: SELECT invoice_distribution_id
8193:
8194: BEGIN
8195: SELECT invoice_distribution_id
8196: INTO l_distribution_id
8197: FROM ap_invoice_distributions
8198: WHERE parent_reversal_id = X_related_dist_id;
8199: EXCEPTION
8200: WHEN no_data_found THEN
8201: l_distribution_id := null;
8214: -- of the invoice distribution that is reversing the invoice distribution
8215: --identified by x_invoice_dist_id.
8216: -----------------------------------------------------------------------
8217: FUNCTION GET_REVERSING_DIST_NUM(X_Invoice_Dist_Id NUMBER) RETURN NUMBER IS
8218: l_reversing_dist_num ap_invoice_distributions.distribution_line_number%TYPE;
8219: BEGIN
8220:
8221: BEGIN
8222: SELECT distribution_line_number
8220:
8221: BEGIN
8222: SELECT distribution_line_number
8223: INTO l_reversing_dist_num
8224: FROM ap_invoice_distributions
8225: WHERE parent_reversal_id = x_invoice_dist_id;
8226:
8227: EXCEPTION WHEN NO_DATA_FOUND THEN
8228: l_reversing_dist_num := NULL;
8281: IS
8282: l_inv_currency_code ap_invoices.invoice_currency_code%TYPE;
8283: l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
8284: l_dist_line_type VARCHAR2(25);
8285: l_inv_price ap_invoice_distributions.unit_price%TYPE;
8286: l_qty_invoiced NUMBER;
8287: l_po_qty NUMBER;
8288: l_po_price po_line_locations.price_override%TYPE;
8289: l_po_rate NUMBER;
8292: l_match_option po_line_locations.match_option%TYPE;
8293: l_rtxn_uom VARCHAR2(25);
8294: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
8295: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
8296: l_distribution_amt ap_invoice_distributions.amount%TYPE;
8297: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
8298: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
8299: l_corrected_inv_dist_id NUMBER(15);
8300: l_corrected_qty NUMBER;
8293: l_rtxn_uom VARCHAR2(25);
8294: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
8295: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
8296: l_distribution_amt ap_invoice_distributions.amount%TYPE;
8297: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
8298: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
8299: l_corrected_inv_dist_id NUMBER(15);
8300: l_corrected_qty NUMBER;
8301: l_uom_conv_rate NUMBER;
8294: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
8295: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
8296: l_distribution_amt ap_invoice_distributions.amount%TYPE;
8297: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
8298: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
8299: l_corrected_inv_dist_id NUMBER(15);
8300: l_corrected_qty NUMBER;
8301: l_uom_conv_rate NUMBER;
8302: l_rate_diff NUMBER;
8342: PLL.matching_basis, -- l_match_basis /* Amount Based Matching */
8343: ail.line_source --Bug#10416960
8344: FROM ap_invoices_all AI,
8345: ap_system_parameters_all ASP,
8346: ap_invoice_distributions D,
8347: po_distributions PD,
8348: po_lines PL,
8349: po_line_types PLT, -- Amount Based Matching
8350: po_line_locations PLL,
8414: NULL --line_source
8415: FROM ap_invoices_all AI,
8416: ap_invoices retain_ai, --bug 9242891 retained invoice
8417: ap_system_parameters_all ASP,
8418: ap_invoice_distributions D,
8419: ap_invoice_distributions retain_d, --bug 9242891 retainage inv dists
8420: po_distributions PD,
8421: po_lines PL,
8422: po_line_types PLT, -- Amount Based Matching
8415: FROM ap_invoices_all AI,
8416: ap_invoices retain_ai, --bug 9242891 retained invoice
8417: ap_system_parameters_all ASP,
8418: ap_invoice_distributions D,
8419: ap_invoice_distributions retain_d, --bug 9242891 retainage inv dists
8420: po_distributions PD,
8421: po_lines PL,
8422: po_line_types PLT, -- Amount Based Matching
8423: po_line_locations PLL,
8463: --bug 9242891 select added to determine which cursor to open.
8464: SELECT COUNT(*)
8465: INTO l_cnt
8466: FROM AP_INVOICES_ALL ai,
8467: AP_INVOICE_DISTRIBUTIONS aid
8468: WHERE aid.Invoice_Distribution_Id = X_DISTRIBUTION_ID
8469: AND ai.invoice_id = aid.invoice_id
8470: AND ai.invoice_type_lookup_code = 'RETAINAGE RELEASE';
8471: --bug 9242891
8701:
8702: select nvl(AI.exchange_rate, 1)
8703: into l_corrected_inv_rate
8704: from AP_INVOICES AI,
8705: AP_INVOICE_DISTRIBUTIONS D
8706: where D.invoice_distribution_id = l_corrected_inv_dist_id
8707: and D.invoice_id = AI.invoice_id;
8708:
8709: /* Modified below statement for bug#14360581
8777:
8778: select nvl(AI.exchange_rate, 1)
8779: into l_corrected_inv_rate
8780: from AP_INVOICES AI,
8781: AP_INVOICE_DISTRIBUTIONS D
8782: where D.invoice_distribution_id = l_corrected_inv_dist_id
8783: and D.invoice_id = AI.invoice_id;
8784:
8785: /* Modified below statement for bug#14360581
8885:
8886: -- Update the calling sequence
8887: --
8888: current_calling_sequence :=
8889: 'AP_INVOICE_DISTRIBUTIONS_PKG.DIST_REFER_ACTIVE_CORR<-'
8890: ||p_Calling_Sequence;
8891:
8892: debug_info := 'Select from ap_invoice_distributions_all';
8893:
8888: current_calling_sequence :=
8889: 'AP_INVOICE_DISTRIBUTIONS_PKG.DIST_REFER_ACTIVE_CORR<-'
8890: ||p_Calling_Sequence;
8891:
8892: debug_info := 'Select from ap_invoice_distributions_all';
8893:
8894: Select count(*)
8895: Into dummy
8896: From ap_invoice_distributions_all AID
8892: debug_info := 'Select from ap_invoice_distributions_all';
8893:
8894: Select count(*)
8895: Into dummy
8896: From ap_invoice_distributions_all AID
8897: Where NVL(AID.cancellation_flag, 'N' ) <> 'Y'
8898: And NVL( AID.reversal_flag, 'N' ) <> 'Y'
8899: And AID.corrected_invoice_dist_id = p_invoice_dist_id
8900: And rownum < 2; --bug 5034678
8948:
8949: -- Update the calling sequence
8950: --
8951: current_calling_sequence :=
8952: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHRG_REFER_ACTIVE_DIST<-'
8953: ||p_Calling_Sequence;
8954:
8955: debug_info := 'Select from ap_invoice_distributions_all';
8956:
8951: current_calling_sequence :=
8952: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHRG_REFER_ACTIVE_DIST<-'
8953: ||p_Calling_Sequence;
8954:
8955: debug_info := 'Select from ap_invoice_distributions_all';
8956:
8957: Select count(*)
8958: Into dummy
8959: From ap_invoice_distributions_all AID
8955: debug_info := 'Select from ap_invoice_distributions_all';
8956:
8957: Select count(*)
8958: Into dummy
8959: From ap_invoice_distributions_all AID
8960: Where AID.charge_applicable_to_dist_id = p_invoice_dist_id
8961: And NVL(AID.cancellation_flag, 'N') <> 'Y'
8962: And NVL(AID.reversal_flag, 'N') <> 'Y';
8963:
9016:
9017: -- Update the calling sequence
9018: --
9019: current_calling_sequence :=
9020: 'AP_INVOICE_DISTRIBUTIONS_PKG.DIST_REFER_ACTIVE_CORR<-'
9021: ||p_Calling_Sequence;
9022:
9023: debug_info := 'Inside wapper API Dist_Refer_Active_Corr_Wrap, ' ||
9024: 'invoked from NegotiationAMImpl.handleDistributionDeleteAction';
9080:
9081: -- Update the calling sequence
9082: --
9083: current_calling_sequence :=
9084: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHRG_REFER_ACTIVE_DIST<-'
9085: ||p_Calling_Sequence;
9086:
9087: debug_info := 'Inside wapper API Chrg_Refer_Active_Dist_Wrap, ' ||
9088: 'invoked from NegotiationAMImpl.handleDistributionDeleteAction';
9123: global_exception EXCEPTION;
9124:
9125: BEGIN
9126:
9127: current_calling_sequence := 'Ap_Invoice_Distributions_Pkg.Make_Distributions_Permanent<- '
9128: ||p_calling_sequence;
9129:
9130: l_debug_info := 'Updating ap_invoice_distributions';
9131:
9126:
9127: current_calling_sequence := 'Ap_Invoice_Distributions_Pkg.Make_Distributions_Permanent<- '
9128: ||p_calling_sequence;
9129:
9130: l_debug_info := 'Updating ap_invoice_distributions';
9131:
9132: UPDATE ap_invoice_distributions_all
9133: SET distribution_class = 'PERMANENT'
9134: WHERE invoice_id = p_invoice_id
9128: ||p_calling_sequence;
9129:
9130: l_debug_info := 'Updating ap_invoice_distributions';
9131:
9132: UPDATE ap_invoice_distributions_all
9133: SET distribution_class = 'PERMANENT'
9134: WHERE invoice_id = p_invoice_id
9135: AND invoice_line_number = NVL(p_invoice_line_number, invoice_line_number)
9136: AND distribution_class = 'CANDIDATE'
9157: AND ail.line_number = l_line_number_tab(uniq_values);
9158:
9159: --Commented below condition for the bug 7483192
9160: /*AND ail.amount = (SELECT SUM(NVL(aid.amount,0))
9161: FROM ap_invoice_distributions_all aid
9162: WHERE aid.invoice_id = ail.invoice_id
9163: AND aid.invoice_line_number = ail.line_number
9164: AND aid.distribution_class = 'PERMANENT'); */
9165:
9234: l_debug_info := 'Get total charge amount allocated to this distribution';
9235:
9236: SELECT sum(aid.amount)
9237: INTO l_total_charge_amount
9238: FROM ap_invoice_distributions aid
9239: WHERE aid.invoice_id = p_invoice_id
9240: AND aid.charge_applicable_to_dist_id = p_invoice_distribution_id;
9241:
9242: RETURN(l_total_charge_amount);
9277: SELECT 'Y' INTO L_AWT_COMPUTED
9278: FROM DUAL WHERE EXISTS
9279: (
9280: SELECT INVOICE_DISTRIBUTION_ID
9281: FROM AP_INVOICE_DISTRIBUTIONS
9282: WHERE LINE_TYPE_LOOKUP_CODE='AWT'
9283: AND INVOICE_ID=P_INVOICE_ID
9284: AND AWT_RELATED_ID=P_DISTRIBUTION_ID
9285: AND NVL(REVERSAL_FLAG,'N')='N'
9291: RETURN L_AWT_COMPUTED;
9292: END;
9293:
9294:
9295: END AP_INVOICE_DISTRIBUTIONS_PKG;