[Home] [Help]
1: PACKAGE BODY AP_INVOICE_DISTRIBUTIONS_PKG AS
2: /* $Header: apiindib.pls 120.68.12010000.4 2009/01/13 02:19:05 bgoyal 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.68.12010000.4 2009/01/13 02:19:05 bgoyal 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
315: --
316: current_calling_sequence :=
317: 'AP_INVOICE_DISTRIBUTION_PKG.Set_Inv_Packet_Id<-'||X_Calling_Sequence;
318:
319: debug_info := 'Select from ap_invoice_distributions';
320:
321: select decode(count(distinct(packet_id)),1,max(packet_id),'')
322: into X_Packet_Id
323: from ap_invoice_distributions
319: debug_info := 'Select from ap_invoice_distributions';
320:
321: select decode(count(distinct(packet_id)),1,max(packet_id),'')
322: into X_Packet_Id
323: from ap_invoice_distributions
324: where invoice_id = X_Invoice_Id
325: and packet_id is not null;
326:
327: EXCEPTION
357:
358: -- Update the calling sequence
359: --
360: current_calling_sequence :=
361: 'AP_INVOICE_DISTRIBUTIONS_PKG.Query_New_Packet_Id<-'||X_Calling_Sequence;
362:
363: debug_info := 'Select from ap_invoice_distributions';
364:
365: select 'TRUE'
359: --
360: current_calling_sequence :=
361: 'AP_INVOICE_DISTRIBUTIONS_PKG.Query_New_Packet_Id<-'||X_Calling_Sequence;
362:
363: debug_info := 'Select from ap_invoice_distributions';
364:
365: select 'TRUE'
366: into dummy
367: from ap_invoice_distributions
363: debug_info := 'Select from ap_invoice_distributions';
364:
365: select 'TRUE'
366: into dummy
367: from ap_invoice_distributions
368: where rowid = X_Rowid
369: and NVL(packet_id, -1) <> NVL(X_Packet_Id, -1);
370:
371: return(TRUE);
403:
404: -- Update the calling sequence
405: --
406: current_calling_sequence :=
407: 'AP_INVOICE_DISTRIBUTIONS_PKG.All_Encumbered<-'||X_Calling_Sequence;
408:
409: debug_info := 'Select from sys.dual';
410:
411: select 'There are encumbered dists'
411: select 'There are encumbered dists'
412: into dummy
413: from sys.dual
414: where not exists (select 'There are other unencumbered dists'
415: from ap_invoice_distributions
416: where invoice_id = X_Invoice_Id
417: and NVL(match_status_flag, 'N') <> 'A'
418: and rowid <> X_Rowid);
419:
475:
476: -- Update the calling sequence
477: --
478: current_calling_sequence :=
479: 'AP_INVOICE_DISTRIBUTIONS_PKG.Check_Cash_Basis_Paid<-'||X_Calling_Sequence;
480:
481: debug_info := 'Select accounting method from SOB';
482:
483: SELECT NVL(sla_ledger_cash_basis_flag, 'N')
561:
562: BEGIN
563:
564: l_api_name := 'Adjust_Po';
565: current_calling_sequence := 'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_PO<-'||X_Calling_Sequence;
566:
567: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
568: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
569: END IF;
564: l_api_name := 'Adjust_Po';
565: current_calling_sequence := 'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_PO<-'||X_Calling_Sequence;
566:
567: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
568: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
569: END IF;
570:
571: l_debug_info := 'Create l_po_ap_dist_rec object';
572: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
619: X_Msg_Data => l_msg_data);
620:
621:
622: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
623: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_DISTRIBUTIONS_PKG.Adjust_Po(-)');
624: END IF;
625:
626: EXCEPTION
627: WHEN OTHERS THEN
689: END Substrbyte;
690:
691: -----------------------------------------------------------------------
692: -- FUNCTION insert_from_dist_set inserts records into
693: -- ap_invoice_distributions given a line number, distribution set id and
694: -- table of distribution information. It returns FALSE if an error
695: -- is encountered.
696: -- Called from ap_invoice_lines_pkg.insert_from_dist_set or from Import
697: -----------------------------------------------------------------------
705: X_Debug_Context OUT NOCOPY VARCHAR2,
706: X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
707: IS
708:
709: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
710: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
711: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
712: l_last_update_login
713: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
706: X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
707: IS
708:
709: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
710: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
711: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
712: l_last_update_login
713: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
714: l_existing_distributions NUMBER := 0;
707: IS
708:
709: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
710: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
711: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
712: l_last_update_login
713: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
714: l_existing_distributions NUMBER := 0;
715: i BINARY_INTEGER := 0;
709: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
710: l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE;
711: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
712: l_last_update_login
713: AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
714: l_existing_distributions NUMBER := 0;
715: i BINARY_INTEGER := 0;
716: current_calling_sequence VARCHAR2(2000);
717: debug_info VARCHAR2(100);
724: --------------------------------------------------------------------------
725: -- Step 1 - Update the calling sequence
726: --------------------------------------------------------------------------
727: current_calling_sequence :=
728: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_dist_set<-'||
729: X_calling_sequence;
730:
731: -------------------------------------------------------------------------
732: -- Step 2 - Validate line does not contain other distributions
734: debug_info := 'Verify line does not contain distributions';
735: BEGIN
736: SELECT count(*)
737: INTO l_existing_distributions
738: FROM ap_invoice_distributions
739: WHERE invoice_id = X_invoice_id
740: AND invoice_line_number = X_line_number;
741:
742: IF (l_existing_distributions <> 0) then
763: -- Step 4 - Generate distributions
764: -------------------------------------------------------------------------
765: FOR i in nvl(X_dist_tab.FIRST, 0) .. nvl(X_dist_tab.LAST, -1) LOOP
766:
767: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
768:
769: INSERT INTO ap_invoice_distributions (
770: batch_id,
771: invoice_id,
765: FOR i in nvl(X_dist_tab.FIRST, 0) .. nvl(X_dist_tab.LAST, -1) LOOP
766:
767: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
768:
769: INSERT INTO ap_invoice_distributions (
770: batch_id,
771: invoice_id,
772: invoice_line_number,
773: invoice_distribution_id,
1089:
1090: --Bug 4539462 DBI logging
1091: SELECT invoice_distribution_id
1092: BULK COLLECT INTO l_dbi_key_value_list
1093: FROM ap_invoice_distributions
1094: WHERE invoice_id = X_invoice_id;
1095:
1096: AP_DBI_PKG.Maintain_DBI_Summary
1097: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1093: FROM ap_invoice_distributions
1094: WHERE invoice_id = X_invoice_id;
1095:
1096: AP_DBI_PKG.Maintain_DBI_Summary
1097: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1098: p_operation => 'I',
1099: p_key_value1 => X_invoice_id,
1100: p_key_value_list => l_dbi_key_value_list,
1101: p_calling_sequence => current_calling_sequence);
1132:
1133:
1134: -----------------------------------------------------------------------
1135: -- PROCEDURE update_distributions updates columns in
1136: -- AP_INVOICE_DISTRIBUTIONS such as match_status_flag, base_amount,
1137: -- type_1099, income_tax_region.
1138: -- PRECONDITION: Procedure is called during POST-FORMS-COMMIT
1139: -----------------------------------------------------------------------
1140:
1173: ap_invoices.last_updated_by%TYPE;
1174: l_last_update_date
1175: ap_invoices.last_update_date%TYPE;
1176: l_invoice_distribution_id
1177: ap_invoice_distributions.invoice_distribution_id%TYPE;
1178: l_project_id
1179: ap_invoice_distributions.project_id%TYPE;
1180: l_task_id
1181: ap_invoice_distributions.task_id%TYPE;
1175: ap_invoices.last_update_date%TYPE;
1176: l_invoice_distribution_id
1177: ap_invoice_distributions.invoice_distribution_id%TYPE;
1178: l_project_id
1179: ap_invoice_distributions.project_id%TYPE;
1180: l_task_id
1181: ap_invoice_distributions.task_id%TYPE;
1182: l_award_id
1183: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1177: ap_invoice_distributions.invoice_distribution_id%TYPE;
1178: l_project_id
1179: ap_invoice_distributions.project_id%TYPE;
1180: l_task_id
1181: ap_invoice_distributions.task_id%TYPE;
1182: l_award_id
1183: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1184: l_expenditure_item_date DATE;
1185: l_expenditure_type
1179: ap_invoice_distributions.project_id%TYPE;
1180: l_task_id
1181: ap_invoice_distributions.task_id%TYPE;
1182: l_award_id
1183: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1184: l_expenditure_item_date DATE;
1185: l_expenditure_type
1186: ap_invoice_distributions.expenditure_type%TYPE;
1187: l_employee_id
1182: l_award_id
1183: ap_invoice_distributions.award_id%TYPE; -- OGM_0.0 changes
1184: l_expenditure_item_date DATE;
1185: l_expenditure_type
1186: ap_invoice_distributions.expenditure_type%TYPE;
1187: l_employee_id
1188: po_vendors.employee_id%TYPE;
1189: l_pa_quantity
1190: ap_invoice_distributions.pa_quantity%TYPE;
1186: ap_invoice_distributions.expenditure_type%TYPE;
1187: l_employee_id
1188: po_vendors.employee_id%TYPE;
1189: l_pa_quantity
1190: ap_invoice_distributions.pa_quantity%TYPE;
1191: l_dist_amount
1192: ap_invoice_distributions.amount%TYPE;
1193: l_dist_base_amount
1194: ap_invoice_distributions.base_amount%TYPE;
1188: po_vendors.employee_id%TYPE;
1189: l_pa_quantity
1190: ap_invoice_distributions.pa_quantity%TYPE;
1191: l_dist_amount
1192: ap_invoice_distributions.amount%TYPE;
1193: l_dist_base_amount
1194: ap_invoice_distributions.base_amount%TYPE;
1195: l_expenditure_organization_id
1196: ap_invoice_distributions.expenditure_organization_id%TYPE;
1190: ap_invoice_distributions.pa_quantity%TYPE;
1191: l_dist_amount
1192: ap_invoice_distributions.amount%TYPE;
1193: l_dist_base_amount
1194: ap_invoice_distributions.base_amount%TYPE;
1195: l_expenditure_organization_id
1196: ap_invoice_distributions.expenditure_organization_id%TYPE;
1197:
1198: l_vendor_id NUMBER;
1192: ap_invoice_distributions.amount%TYPE;
1193: l_dist_base_amount
1194: ap_invoice_distributions.base_amount%TYPE;
1195: l_expenditure_organization_id
1196: ap_invoice_distributions.expenditure_organization_id%TYPE;
1197:
1198: l_vendor_id NUMBER;
1199: l_vendor_site_id NUMBER;
1200: l_invoice_date DATE;
1200: l_invoice_date DATE;
1201: l_gl_allow_tax_override
1202: GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRIDE_FLAG%TYPE;
1203: l_tax_recoverable_flag
1204: ap_invoice_distributions.tax_recoverable_flag%TYPE;
1205: l_po_distribution_id
1206: ap_invoice_distributions.po_distribution_id%TYPE;
1207: l_line_location_id
1208: po_distributions.line_location_id%TYPE;
1202: GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRIDE_FLAG%TYPE;
1203: l_tax_recoverable_flag
1204: ap_invoice_distributions.tax_recoverable_flag%TYPE;
1205: l_po_distribution_id
1206: ap_invoice_distributions.po_distribution_id%TYPE;
1207: l_line_location_id
1208: po_distributions.line_location_id%TYPE;
1209: l_accrue_on_receipt
1210: po_distributions.accrue_on_receipt_flag%TYPE;
1210: po_distributions.accrue_on_receipt_flag%TYPE;
1211: l_dist_count NUMBER;
1212: l_user_id NUMBER;
1213: l_dist_attribute_category
1214: ap_invoice_distributions.attribute_category%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1211: l_dist_count NUMBER;
1212: l_user_id NUMBER;
1213: l_dist_attribute_category
1214: ap_invoice_distributions.attribute_category%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1212: l_user_id NUMBER;
1213: l_dist_attribute_category
1214: ap_invoice_distributions.attribute_category%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1213: l_dist_attribute_category
1214: ap_invoice_distributions.attribute_category%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1214: ap_invoice_distributions.attribute_category%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1215: l_dist_attribute1 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1216: l_dist_attribute2 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1217: l_dist_attribute3 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1218: l_dist_attribute4 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1219: l_dist_attribute5 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1220: l_dist_attribute6 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1221: l_dist_attribute7 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1222: l_dist_attribute8 ap_invoice_distributions.attribute1%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1223: l_dist_attribute9 ap_invoice_distributions.attribute1%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1231: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1224: l_dist_attribute10 ap_invoice_distributions.attribute1%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1231: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1232: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1225: l_dist_attribute11 ap_invoice_distributions.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1231: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1232: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1233: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1226: l_dist_attribute12 ap_invoice_distributions.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1231: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1232: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1233: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1234: l_inv_attribute2 ap_invoices.attribute1%TYPE;
1227: l_dist_attribute13 ap_invoice_distributions.attribute1%TYPE;
1228: l_dist_attribute14 ap_invoice_distributions.attribute1%TYPE;
1229: l_dist_attribute15 ap_invoice_distributions.attribute1%TYPE;
1230: l_dist_posted_flag ap_invoice_distributions.posted_flag%TYPE;
1231: l_dist_reversal_flag ap_invoice_distributions.reversal_flag%TYPE;
1232: l_inv_attribute_category ap_invoices.attribute_category%TYPE;
1233: l_inv_attribute1 ap_invoices.attribute1%TYPE;
1234: l_inv_attribute2 ap_invoices.attribute1%TYPE;
1235: l_inv_attribute3 ap_invoices.attribute1%TYPE;
1267: l_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1268: l_line_number
1269: ap_invoice_lines.line_number%TYPE;
1270: l_rounded_dist_id
1271: ap_invoice_distributions.invoice_distribution_id%type;
1272: l_round_amt_exists BOOLEAN := FALSE;
1273: l_rounded_amt NUMBER;
1274: --Bug 4539462 DBI logging
1275: l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1355: AI.attribute13,
1356: AI.attribute14,
1357: AI.attribute15,
1358: gsob.chart_of_accounts_id
1359: FROM ap_invoice_distributions AID,
1360: ap_invoices AI,
1361: ap_suppliers PV,
1362: po_distributions PD,
1363: ap_system_parameters ap,
1378: --record changes before reaching the PA code.
1379: BEGIN
1380: -- Update the calling sequence
1381: current_calling_sequence :=
1382: 'AP_INVOICE_DISTRIBUTIONS_PKG.update_distributions<-'||
1383: X_calling_sequence;
1384:
1385: /*-----------------------------------------------------------------+
1386: | First, assume that an update to AP_INVOICE_DISTRIBUTIONS |
1382: 'AP_INVOICE_DISTRIBUTIONS_PKG.update_distributions<-'||
1383: X_calling_sequence;
1384:
1385: /*-----------------------------------------------------------------+
1386: | First, assume that an update to AP_INVOICE_DISTRIBUTIONS |
1387: | will not occur. We will override the this default value |
1388: | if an update does occur. |
1389: +-----------------------------------------------------------------*/
1390:
1444: WHERE sp.org_id = l_org_id
1445: AND sp.org_id = fsp.org_id;
1446:
1447: ---------------------------------------------------------
1448: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS tax info';
1449: ---------------------------------------------------------
1450:
1451: /*-----------------------------------------------------------------+
1452: | Update 1099 type and income tax region for each distribution |
1453: | line if the vendor changed (test performed in PRE-UPDATE) |
1454: +-----------------------------------------------------------------*/
1455:
1456: IF (nvl(X_vendor_changed_flag,'N') = 'Y') THEN
1457: UPDATE ap_invoice_distributions
1458: SET type_1099 = X_type_1099,
1459: income_tax_region = X_income_tax_region
1460: WHERE invoice_id = X_invoice_id
1461: AND invoice_line_number = NVL(X_line_number, invoice_line_number);
1469:
1470: END IF; -- end of x_vendor_changed_flag
1471:
1472: --------------------------------------------------------------
1473: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS match status';
1474: --------------------------------------------------------------
1475:
1476: -- Reset the match status flags if X_reset_match_status
1477: -- is Y and encumbrance is not on.
1477: -- is Y and encumbrance is not on.
1478: -- Don't change NULLS to N as these have never been through
1479: -- AutoApproval
1480: --
1481: UPDATE ap_invoice_distributions
1482: SET match_status_flag = 'N'
1483: WHERE invoice_id = X_invoice_id
1484: AND invoice_line_number = NVL( x_line_number, invoice_line_number)
1485: --Bug 5003892 AND l_purch_encumbrance_flag <> 'Y'
1489: BULK COLLECT INTO l_dbi_key_value_list;
1490:
1491: --Bug 4539462 DBI logging
1492: AP_DBI_PKG.Maintain_DBI_Summary
1493: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1494: p_operation => 'U',
1495: p_key_value1 => X_invoice_id,
1496: p_key_value_list => l_dbi_key_value_list,
1497: p_calling_sequence => current_calling_sequence);
1514: -- all the base columns
1515: -- Set the exchange rate info, base amount and WHO columns
1516: -- for the distributions
1517:
1518: --Bugfix:4644053 changed ap_invoice_distributions
1519: --to ap_invoice_distributions_all
1520: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1521:
1522: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1515: -- Set the exchange rate info, base amount and WHO columns
1516: -- for the distributions
1517:
1518: --Bugfix:4644053 changed ap_invoice_distributions
1519: --to ap_invoice_distributions_all
1520: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1521:
1522: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1523: SET base_amount =
1516: -- for the distributions
1517:
1518: --Bugfix:4644053 changed ap_invoice_distributions
1519: --to ap_invoice_distributions_all
1520: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1521:
1522: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1523: SET base_amount =
1524: DECODE(l_base_currency_code,
1518: --Bugfix:4644053 changed ap_invoice_distributions
1519: --to ap_invoice_distributions_all
1520: debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
1521:
1522: UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
1523: SET base_amount =
1524: DECODE(l_base_currency_code,
1525: l_invoice_currency_code, NULL,
1526: ap_utilities_pkg.ap_round_currency(
1589:
1590: -- get the existing base amount for the selected distribution
1591: select base_amount
1592: INTO l_base_amt
1593: FROM AP_INVOICE_DISTRIBUTIONS
1594: WHERE invoice_id = X_invoice_id
1595: AND invoice_line_number = l_line_number
1596: AND invoice_distribution_id = l_round_dist_id_list(i);
1597:
1601: p_rounding_amt => l_modified_dist_rounding_amt,
1602: p_next_line_rounding_amt => l_rounded_amt);
1603:
1604: -- update the calculatd base amount, rounding amount
1605: UPDATE AP_INVOICE_DISTRIBUTIONS
1606: SET base_amount = l_base_amt,
1607: rounding_amt = ABS( l_modified_dist_rounding_amt ),
1608: last_update_date = SYSDATE,
1609: last_updated_by = FND_GLOBAL.user_id,
1921:
1922: /* IF (l_dist_posted_flag = 'N') THEN
1923: IF (l_dist_reversal_flag <> 'Y') THEN
1924:
1925: UPDATE ap_invoice_distributions
1926: SET dist_code_combination_id = l_dist_code_combination_id
1927: WHERE invoice_distribution_id = l_invoice_distribution_id;
1928:
1929: END IF; -- end of l_dist_reversal_flag
1938: IF (l_dist_count >0) THEN
1939: X_update_occurred := 'Y';
1940: END IF;
1941:
1942: UPDATE AP_INVOICE_DISTRIBUTIONS
1943: SET last_update_login = l_last_update_login
1944: WHERE invoice_id = X_invoice_id
1945: AND last_update_login = -3;
1946: -- CHANGES FOR BUG - 3025688 ** ENDS **
2104: BEGIN
2105: -- Update the calling sequence
2106: --
2107: current_calling_sequence :=
2108: 'AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Row<-'||X_Calling_Sequence;
2109:
2110: debug_info := 'Calling invoice distribution handler';
2111:
2112: AP_AID_TABLE_HANDLER_PKG.Insert_Row
2549: RELATED_ID,
2550: ASSET_BOOK_TYPE_CODE,
2551: ASSET_CATEGORY_ID,
2552: INTENDED_USE
2553: FROM AP_INVOICE_DISTRIBUTIONS
2554: WHERE rowid = X_Rowid
2555: FOR UPDATE of Invoice_Id NOWAIT;
2556: Recinfo C%ROWTYPE;
2557:
2560:
2561: -- Update the calling sequence
2562: --
2563: current_calling_sequence :=
2564: 'AP_INVOICE_DISTRIBUTIONS_PKG.Lock_Row<-'||X_Calling_Sequence;
2565:
2566: debug_info := 'Select from ap_invoice_distributions';
2567:
2568: OPEN C;
2562: --
2563: current_calling_sequence :=
2564: 'AP_INVOICE_DISTRIBUTIONS_PKG.Lock_Row<-'||X_Calling_Sequence;
2565:
2566: debug_info := 'Select from ap_invoice_distributions';
2567:
2568: OPEN C;
2569:
2570: debug_info := 'Fetch cursor C';
3055:
3056: -- Update the calling sequence
3057: --
3058: current_calling_sequence :=
3059: 'AP_INVOICE_DISTRIBUTIONS_PKG.Update_Row<-'||X_Calling_Sequence;
3060:
3061: AP_AID_TABLE_HANDLER_PKG.Update_Row
3062: (X_Rowid,
3063: X_Invoice_Id,
3196: PROCEDURE Delete_Row(X_Rowid VARCHAR2,
3197: X_Calling_Sequence VARCHAR2) IS
3198: current_calling_sequence VARCHAR2(2000);
3199: debug_info VARCHAR2(100);
3200: l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
3201:
3202: BEGIN
3203:
3204: -- Update the calling sequence
3203:
3204: -- Update the calling sequence
3205: --
3206: current_calling_sequence :=
3207: 'AP_INVOICE_DISTRIBUTIONS_PKG.Delete_Row<-'||X_Calling_Sequence;
3208:
3209: AP_AID_TABLE_HANDLER_PKG.Delete_Row(
3210: X_Rowid,
3211: current_calling_sequence);
3312: -- Bug 1892826. Added the line_type_lookup_code predicate
3313: -- Bug 2404982. Added package qualifier to procedure call.
3314: SELECT sum(amount)
3315: INTO l_dist_amount
3316: FROM ap_invoice_distributions
3317: WHERE invoice_id = X_Invoice_Id
3318: AND line_type_lookup_code IN ('ITEM','PREPAY')
3319: AND nvl(reversal_flag,'N') <> 'Y'
3320: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3316: FROM ap_invoice_distributions
3317: WHERE invoice_id = X_Invoice_Id
3318: AND line_type_lookup_code IN ('ITEM','PREPAY')
3319: AND nvl(reversal_flag,'N') <> 'Y'
3320: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3321: dist_code_combination_id, X_Sob_Id) =
3322: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3323: X_Prepay_Dist_CCID, X_Sob_Id);
3324:
3318: AND line_type_lookup_code IN ('ITEM','PREPAY')
3319: AND nvl(reversal_flag,'N') <> 'Y'
3320: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3321: dist_code_combination_id, X_Sob_Id) =
3322: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3323: X_Prepay_Dist_CCID, X_Sob_Id);
3324:
3325: RETURN (l_dist_amount);
3326:
3344:
3345: -- Bug 2404982. Added package qualifier to procedure call.
3346: CURSOR c_prepay_dist IS
3347: SELECT sum(nvl(prepay_amount_remaining,amount)),
3348: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3349: aip.dist_code_combination_id, X_Sob_Id)
3350: FROM ap_invoice_distributions aip
3351: WHERE aip.invoice_id = X_Prepay_Id
3352: --bugfix:3881673
3346: CURSOR c_prepay_dist IS
3347: SELECT sum(nvl(prepay_amount_remaining,amount)),
3348: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3349: aip.dist_code_combination_id, X_Sob_Id)
3350: FROM ap_invoice_distributions aip
3351: WHERE aip.invoice_id = X_Prepay_Id
3352: --bugfix:3881673
3353: AND aip.line_type_lookup_code in ('ITEM','ACCRUAL')
3354: AND nvl(aip.reversal_flag,'N') <> 'Y'
3352: --bugfix:3881673
3353: AND aip.line_type_lookup_code in ('ITEM','ACCRUAL')
3354: AND nvl(aip.reversal_flag,'N') <> 'Y'
3355: AND nvl(aip.prepay_amount_remaining,amount) > 0
3356: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3357: aip.dist_code_combination_id, X_Sob_Id) IN
3358: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3359: aid.dist_code_combination_id, X_Sob_Id)
3360: FROM ap_invoice_distributions aid
3354: AND nvl(aip.reversal_flag,'N') <> 'Y'
3355: AND nvl(aip.prepay_amount_remaining,amount) > 0
3356: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3357: aip.dist_code_combination_id, X_Sob_Id) IN
3358: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3359: aid.dist_code_combination_id, X_Sob_Id)
3360: FROM ap_invoice_distributions aid
3361: WHERE aid.invoice_id = X_Invoice_ID)
3362: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3356: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3357: aip.dist_code_combination_id, X_Sob_Id) IN
3358: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3359: aid.dist_code_combination_id, X_Sob_Id)
3360: FROM ap_invoice_distributions aid
3361: WHERE aid.invoice_id = X_Invoice_ID)
3362: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3363: aip.dist_code_combination_id, X_Sob_Id)
3364: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3358: (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3359: aid.dist_code_combination_id, X_Sob_Id)
3360: FROM ap_invoice_distributions aid
3361: WHERE aid.invoice_id = X_Invoice_ID)
3362: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3363: aip.dist_code_combination_id, X_Sob_Id)
3364: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3365: aip.dist_code_combination_id, X_Sob_Id);
3366:
3360: FROM ap_invoice_distributions aid
3361: WHERE aid.invoice_id = X_Invoice_ID)
3362: GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3363: aip.dist_code_combination_id, X_Sob_Id)
3364: ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3365: aip.dist_code_combination_id, X_Sob_Id);
3366:
3367: BEGIN
3368:
3373:
3374: -- Bug 2404982. Added package qualifier to procedure call
3375: SELECT sum(amount)
3376: INTO l_invoice_amount
3377: FROM ap_invoice_distributions
3378: WHERE invoice_id = X_Invoice_ID
3379: AND line_type_lookup_code IN ('ITEM','PREPAY')
3380: AND nvl(reversal_flag,'N') <> 'Y'
3381: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3377: FROM ap_invoice_distributions
3378: WHERE invoice_id = X_Invoice_ID
3379: AND line_type_lookup_code IN ('ITEM','PREPAY')
3380: AND nvl(reversal_flag,'N') <> 'Y'
3381: AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3382: dist_code_combination_id, X_Sob_Id)
3383: = l_bal_segment;
3384:
3385: IF l_invoice_amount <> 0 THEN
3408: BEGIN
3409:
3410: --Bug 2404982. Added package qualifier to procedure call.
3411: SELECT count(distinct(
3412: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3413: dist_code_combination_id, X_Sob_Id)) )
3414: INTO l_dist_count
3415: FROM ap_invoice_distributions
3416: WHERE invoice_id = X_Invoice_Id;
3411: SELECT count(distinct(
3412: AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
3413: dist_code_combination_id, X_Sob_Id)) )
3414: INTO l_dist_count
3415: FROM ap_invoice_distributions
3416: WHERE invoice_id = X_Invoice_Id;
3417:
3418: IF l_dist_count > 1 THEN
3419: RETURN (TRUE);
3539:
3540: -- Update current calling sequence
3541: --
3542: current_calling_sequence :=
3543: 'AP_INVOICE_DISTRIBUTIONS_PKG.Round_Biggest_Distribution<-'
3544: ||X_Calling_Sequence;
3545:
3546: debug_info := 'Adjusting distribution amount in biggest distribution';
3547:
3545:
3546: debug_info := 'Adjusting distribution amount in biggest distribution';
3547:
3548: -- bug 5052661 -- modified SELECT portion of SQL in where clause to go to base tables to avoid FTS
3549: UPDATE ap_invoice_distributions d1
3550: SET base_amount =
3551: (SELECT DECODE(SIGN(SUM(d2.base_amount) -
3552: DECODE(max(f.minimum_accountable_unit), NULL,
3553: ROUND((SUM(d2.amount)
3573: ROUND((SUM(d2.amount) * i.exchange_rate)
3574: /MAX(f.minimum_accountable_unit))*
3575: MAX(f.minimum_accountable_unit))),
3576: d1.base_amount)
3577: FROM ap_invoices i, ap_invoice_distributions d2, fnd_currencies F
3578: WHERE d1.invoice_id = i.invoice_id
3579: AND d1.invoice_id = d2.invoice_id
3580: AND f.currency_code = X_Base_Currency_Code
3581: GROUP BY i.exchange_rate)
3582: WHERE d1.invoice_id = X_Invoice_Id
3583: AND d1.posted_flag = 'N'
3584: AND (d1.invoice_id, d1.distribution_line_number) IN
3585: (SELECT d5.invoice_id, MAX(d5.distribution_line_number)
3586: FROM ap_invoice_distributions_all d5
3587: WHERE (d5.invoice_id, abs(d5.amount)) IN
3588: (SELECT i1.invoice_id , MAX(ABS(d3.amount))
3589: FROM ap_invoices_all i1, ap_invoice_distributions_all d3
3590: WHERE i1.invoice_id = X_Invoice_Id
3585: (SELECT d5.invoice_id, MAX(d5.distribution_line_number)
3586: FROM ap_invoice_distributions_all d5
3587: WHERE (d5.invoice_id, abs(d5.amount)) IN
3588: (SELECT i1.invoice_id , MAX(ABS(d3.amount))
3589: FROM ap_invoices_all i1, ap_invoice_distributions_all d3
3590: WHERE i1.invoice_id = X_Invoice_Id
3591: AND i1.invoice_id = d3.invoice_id
3592: AND d3.line_type_lookup_code <> 'TAX'
3593: AND NOT EXISTS
3591: AND i1.invoice_id = d3.invoice_id
3592: AND d3.line_type_lookup_code <> 'TAX'
3593: AND NOT EXISTS
3594: (SELECT d4.invoice_id
3595: FROM ap_invoice_distributions_all d4
3596: WHERE d4.invoice_id = X_Invoice_Id
3597: AND (1 = DECODE(X_Base_Currency_Code,
3598: i1.invoice_currency_code,1,
3599: DECODE(d4.exchange_rate,null,1,0))
3619:
3620:
3621: -----------------------------------------------------------------------
3622: -- FUNCTION insert_single_dist_from_line inserts a record into
3623: -- ap_invoice_distributions given a line number number and/or line record.
3624: -- It returns FALSE if an error is encountered.
3625: -----------------------------------------------------------------------
3626: FUNCTION Insert_Single_Dist_From_Line(
3627: X_batch_id IN AP_INVOICES.BATCH_ID%TYPE,
3810:
3811: l_invoice_line_rec AP_INVOICES_PKG.r_invoice_line_rec;
3812:
3813: l_distribution_class
3814: AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
3815: l_dist_ccid
3816: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
3817: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3818: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3812:
3813: l_distribution_class
3814: AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
3815: l_dist_ccid
3816: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
3817: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3818: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3819: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
3820: l_assets_tracking_flag
3817: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
3818: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
3819: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
3820: l_assets_tracking_flag
3821: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
3822: l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
3823: l_accounting_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
3824: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
3825: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
3841: debug_info VARCHAR2(100);
3842: debug_context VARCHAR2(2000);
3843: l_dist_code_concat VARCHAR2(2000);
3844: l_invoice_distribution_id
3845: ap_invoice_distributions.invoice_distribution_id%TYPE;
3846: l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
3847: l_sys_link_function VARCHAR2(2); /* Bug 5102724 */
3848: l_web_parameter_id number; --Bug5003249
3849: l_employee_ccid number;
3854: --------------------------------------------------------------------------
3855: -- Step 1 - Update the calling sequence
3856: --------------------------------------------------------------------------
3857: current_calling_sequence :=
3858: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_single_dist_from_line <-'||
3859: X_calling_sequence;
3860:
3861: --------------------------------------------------------------------------
3862: -- Step 2 - If calling module provided X_invoice_id / X_line_number, then
3897: debug_info := 'Verify line does not contain distributions';
3898: BEGIN
3899: SELECT count(*)
3900: INTO l_existing_distributions
3901: FROM ap_invoice_distributions
3902: WHERE invoice_id = l_invoice_line_rec.invoice_id
3903: AND invoice_line_number = l_invoice_line_rec.line_number;
3904:
3905: IF (l_existing_distributions <> 0) then
4365: -- Step 11 - Generate distributions
4366: ----------------------------------------------------------------------------
4367: BEGIN
4368:
4369: INSERT INTO ap_invoice_distributions(
4370: batch_id,
4371: invoice_id,
4372: invoice_line_number,
4373: invoice_distribution_id,
4519: pay_awt_group_id)
4520: VALUES (X_batch_id, -- batch_id
4521: l_invoice_line_rec.invoice_id, -- invoice_id
4522: l_invoice_line_rec.line_number, -- invoice_line_number
4523: ap_invoice_distributions_s.nextval, -- invoice_distribution_id
4524: 1, -- distribution_line_number
4525: l_invoice_line_rec.line_type_lookup_code, -- line_type_lookup_code
4526: l_distribution_class, -- distribution_class
4527: l_invoice_line_rec.description, -- description
4742: X_invoice_currency_code IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
4743: X_sob_id IN AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE,
4744: X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
4745: X_base_currency_code IN AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE,
4746: X_amount IN AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE,
4747: X_base_amount IN AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE,
4748: X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
4749: X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
4750: X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
4743: X_sob_id IN AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE,
4744: X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
4745: X_base_currency_code IN AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE,
4746: X_amount IN AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE,
4747: X_base_amount IN AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE,
4748: X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
4749: X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
4750: X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
4751: X_line_number IN NUMBER,
4758: X_chrg_line_exp_org_id IN AP_INVOICE_LINES.EXPENDITURE_ORGANIZATION_ID%TYPE,
4759: X_chrg_assets_track_flag IN AP_INVOICE_LINES.ASSETS_TRACKING_FLAG%TYPE,
4760: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
4761: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
4762: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4759: X_chrg_assets_track_flag IN AP_INVOICE_LINES.ASSETS_TRACKING_FLAG%TYPE,
4760: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
4761: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
4762: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4760: X_chrg_asset_book_type_code IN AP_INVOICE_LINES.ASSET_BOOK_TYPE_CODE%TYPE,
4761: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
4762: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4761: X_chrg_asset_category_id IN AP_INVOICE_LINES.ASSET_CATEGORY_ID%TYPE,
4762: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4769: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4762: X_item_dist_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4769: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4770: X_chrg_line_default_ccid IN NUMBER,
4763: X_item_dist_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4769: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4770: X_chrg_line_default_ccid IN NUMBER,
4771: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
4764: X_item_dist_award_id IN AP_INVOICE_DISTRIBUTIONS.award_ID%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4769: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4770: X_chrg_line_default_ccid IN NUMBER,
4771: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
4772: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
4765: X_item_dist_expenditure_type IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4766: X_item_dist_exp_org_id IN AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4767: X_item_assets_track_flag IN AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4768: X_item_asset_book_type_code IN AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4769: X_item_asset_category_id IN AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4770: X_chrg_line_default_ccid IN NUMBER,
4771: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
4772: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
4773: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
4771: X_overlay_dist_code_concat IN AP_INVOICE_LINES.OVERLAY_DIST_CODE_CONCAT%TYPE,
4772: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
4773: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
4774: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
4775: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
4776: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
4777: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
4778: X_pa_allows_overrides IN VARCHAR2,
4779: X_allow_po_override IN VARCHAR2,
4772: X_balancing_segment IN AP_INVOICE_LINES.BALANCING_SEGMENT%TYPE,
4773: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
4774: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
4775: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
4776: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
4777: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
4778: X_pa_allows_overrides IN VARCHAR2,
4779: X_allow_po_override IN VARCHAR2,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4773: X_account_segment IN AP_INVOICE_LINES.ACCOUNT_SEGMENT%TYPE,
4774: X_cost_center_segment IN AP_INVOICE_LINES.COST_CENTER_SEGMENT%TYPE,
4775: X_item_dist_ccid IN AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE,
4776: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
4777: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
4778: X_pa_allows_overrides IN VARCHAR2,
4779: X_allow_po_override IN VARCHAR2,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4776: X_item_po_dist_id IN AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE,
4777: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
4778: X_pa_allows_overrides IN VARCHAR2,
4779: X_allow_po_override IN VARCHAR2,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4777: X_item_rcv_trx_id IN AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE,
4778: X_pa_allows_overrides IN VARCHAR2,
4779: X_allow_po_override IN VARCHAR2,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4779: X_allow_po_override IN VARCHAR2,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4780: X_project_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4781: X_task_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4789: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4782: X_award_id OUT NOCOPY AP_INVOICE_LINES.award_ID%TYPE,
4783: X_expenditure_type OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4789: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4790: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4784: X_expenditure_org_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4789: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4790: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4791: X_error_code OUT NOCOPY VARCHAR2,
4792: X_msg_application OUT NOCOPY VARCHAR2,
4785: X_expenditure_item_date OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4789: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4790: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4791: X_error_code OUT NOCOPY VARCHAR2,
4792: X_msg_application OUT NOCOPY VARCHAR2,
4793: X_msg_data OUT NOCOPY VARCHAR2,
4786: X_pa_addition_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE,
4787: X_account_for_dist OUT NOCOPY NUMBER,
4788: X_assets_tracking_flag OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE,
4789: X_asset_book_typ_code OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE,
4790: X_asset_category_id OUT NOCOPY AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE,
4791: X_error_code OUT NOCOPY VARCHAR2,
4792: X_msg_application OUT NOCOPY VARCHAR2,
4793: X_msg_data OUT NOCOPY VARCHAR2,
4794: X_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE) --bug 5386396
4796: IS
4797:
4798: l_po_accrue_on_receipt_flag PO_DISTRIBUTIONS.ACCRUE_ON_RECEIPT_FLAG%TYPE;
4799: l_po_ccid PO_DISTRIBUTIONS.CODE_COMBINATION_ID%TYPE;
4800: l_dist_ccid AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
4801: l_dist_code_concat VARCHAR2(2000);
4802: l_employee_id AP_SUPPLIERS.EMPLOYEE_ID%TYPE;
4803: l_account_type GL_CODE_COMBINATIONS.ACCOUNT_TYPE%TYPE;
4804: user_id NUMBER;
5273: ARL.percentage,
5274: ARL.amount,
5275: Sum(AID.amount)
5276: FROM AP_Allocation_Rule_Lines ARL,
5277: AP_invoice_distributions_all AID
5278: WHERE ARL.invoice_id = X_invoice_id
5279: AND AID.invoice_id = X_invoice_id
5280: AND ARL.chrg_invoice_line_number = X_line_number
5281: AND AID.invoice_line_number = ARL.to_invoice_line_number
5290: SELECT AIL.line_number,
5291: AIL.amount,
5292: sum(AID.amount)
5293: FROM AP_invoice_lines AIL,
5294: AP_invoice_distributions_all AID
5295: WHERE AIL.invoice_id = X_invoice_id
5296: AND nvl(AIL.discarded_flag, 'N') = 'N'
5297: AND nvl(AIL.cancelled_flag, 'N') = 'N'
5298: AND AIL.amount <> 0
5315: AID.pa_quantity, -- bug6699834
5316: AID.expenditure_type,
5317: AID.expenditure_organization_id,
5318: AID.award_id,
5319: AP_INVOICE_DISTRIBUTIONS_PKG.GET_TOTAL_DIST_AMOUNT(
5320: aid.invoice_distribution_id),
5321: AID.dist_code_combination_id,
5322: AID.assets_tracking_flag,
5323: AID.asset_book_type_code,
5322: AID.assets_tracking_flag,
5323: AID.asset_book_type_code,
5324: AID.asset_category_id,
5325: AID.description
5326: FROM ap_invoice_distributions_all AID
5327: WHERE AID.invoice_id = X_invoice_id
5328: AND AID.invoice_line_number = P_line_number
5329: AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL')
5330: ORDER BY AID.distribution_line_number;
5329: AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL')
5330: ORDER BY AID.distribution_line_number;
5331:
5332:
5333: l_batch_id AP_INVOICE_DISTRIBUTIONS.BATCH_ID%TYPE;
5334: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
5335: l_org_id AP_INVOICE_LINES.ORG_ID%TYPE;
5336: l_sob_id AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE;
5337: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5330: ORDER BY AID.distribution_line_number;
5331:
5332:
5333: l_batch_id AP_INVOICE_DISTRIBUTIONS.BATCH_ID%TYPE;
5334: l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
5335: l_org_id AP_INVOICE_LINES.ORG_ID%TYPE;
5336: l_sob_id AP_INVOICE_LINES.SET_OF_BOOKS_ID%TYPE;
5337: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5338: l_chart_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
5374: l_income_tax_region AP_INVOICE_LINES.INCOME_TAX_REGION%TYPE;
5375: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
5376: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
5377: l_invoice_distribution_id
5378: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5375: l_open_gl_date AP_INVOICE_LINES.ACCOUNTING_DATE%TYPE;
5376: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
5377: l_invoice_distribution_id
5378: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5376: l_open_period_name AP_INVOICE_LINES.PERIOD_NAME%TYPE;
5377: l_invoice_distribution_id
5378: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5377: l_invoice_distribution_id
5378: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5378: AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5379: l_po_distribution_id AP_INVOICE_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5380: l_rcv_transaction_id AP_INVOICE_DISTRIBUTIONS.RCV_TRANSACTION_ID%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5381: l_dist_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE;
5382: l_dist_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5383: l_dist_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE; --6699834
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5390: l_dist_code_combination_id
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5384: l_dist_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE;
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5390: l_dist_code_combination_id
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5392: l_dist_assets_tracking_flag
5385: l_dist_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE;
5386: l_dist_expenditure_org_id
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5390: l_dist_code_combination_id
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5392: l_dist_assets_tracking_flag
5393: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5387: AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE;
5388: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5390: l_dist_code_combination_id
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5392: l_dist_assets_tracking_flag
5393: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5394: l_dist_asset_book_type_code
5395: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
5389: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
5390: l_dist_code_combination_id
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5392: l_dist_assets_tracking_flag
5393: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5394: l_dist_asset_book_type_code
5395: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
5396: l_dist_asset_category_id
5397: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
5391: AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID%TYPE;
5392: l_dist_assets_tracking_flag
5393: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5394: l_dist_asset_book_type_code
5395: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
5396: l_dist_asset_category_id
5397: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
5398: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
5399: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
5393: AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE;
5394: l_dist_asset_book_type_code
5395: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
5396: l_dist_asset_category_id
5397: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
5398: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
5399: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
5400: l_alloc_line_tab AP_INVOICE_LINES_PKG.alloc_line_tab_type;
5401: l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
5394: l_dist_asset_book_type_code
5395: AP_INVOICE_DISTRIBUTIONS.ASSET_BOOK_TYPE_CODE%TYPE;
5396: l_dist_asset_category_id
5397: AP_INVOICE_DISTRIBUTIONS.ASSET_CATEGORY_ID%TYPE;
5398: l_dist_description AP_INVOICE_DISTRIBUTIONS.DESCRIPTION%TYPE;
5399: l_dist_tab AP_INVOICE_LINES_PKG.dist_tab_type;
5400: l_alloc_line_tab AP_INVOICE_LINES_PKG.alloc_line_tab_type;
5401: l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
5402: l_rule_status AP_ALLOCATION_RULES.STATUS%TYPE;
5406: AP_ALLOCATION_RULE_LINES.TO_INVOICE_LINE_NUMBER%TYPE;
5407: l_applicable_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
5408: l_applicable_line_amount AP_INVOICE_LINES.AMOUNT%TYPE := 0;
5409: l_sum_applic_lines AP_INVOICE_LINES.AMOUNT%TYPE := 0;
5410: l_sum_dists_applic_line AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
5411: l_running_total_alloc AP_INVOICE_LINES.AMOUNT%TYPE := 0;
5412: l_running_total_base_amt AP_INVOICE_LINES.BASE_AMOUNT%TYPE := 0;
5413: l_max_alloc AP_INVOICE_LINES.AMOUNT%TYPE := 0;
5414: l_max_base_alloc AP_INVOICE_LINES.BASE_AMOUNT%TYPE := 0;
5428: l_count_applicable_dists NUMBER := 0;
5429: current_calling_sequence VARCHAR2(2000);
5430: debug_info VARCHAR2(1000);
5431:
5432: l_inv_dist_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
5433:
5434: -- Bug 5114543
5435: l_max_dist_line_num NUMBER :=0;
5436: l_chrg_line_rec ap_invoice_lines_all%rowtype;
5442: ---------------------------------------------------------------------------
5443: -- Step 1 - Update the calling sequence
5444: ---------------------------------------------------------------------------
5445: current_calling_sequence :=
5446: 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_charge_from_alloc <-'
5447: ||X_calling_sequence;
5448:
5449: --------------------------------------------------------------------------
5450: -- Step 2 - Validate the line exists and get line level information
5521: debug_info := 'Verify line does not contain distributions';
5522: BEGIN
5523: SELECT count(*)
5524: INTO l_existing_distributions
5525: FROM ap_invoice_distributions
5526: WHERE invoice_id = X_invoice_id
5527: AND invoice_line_number = X_line_number;
5528:
5529: IF (l_existing_distributions <> 0) then
5666: ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION','AMOUNT_CORRECTION')
5667: --Bug 5558693
5668: AND amount <>
5669: (SELECT NVL(SUM(NVL(aid.amount,0)),0)
5670: FROM ap_invoice_distributions_all AID
5671: WHERE AID.invoice_id = ail.invoice_id
5672: AND AID.invoice_line_number = AIL.line_number
5673: AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
5674: OR (AID.prepay_distribution_id IS NOT NULL
5696: AND ARL.chrg_invoice_line_number = X_line_number
5697: AND ARL.to_invoice_line_number = AIL.line_number
5698: --Bug 5558693
5699: AND AIL.amount <> (SELECT NVL(SUM(nvl(amount,0)),0)
5700: FROM ap_invoice_distributions AID
5701: WHERE AID.invoice_id = AIL.invoice_id
5702: AND AID.invoice_line_number = AIL.line_number
5703: AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
5704: OR (AID.prepay_distribution_id IS NOT NULL
6183: ----------------------------------------------------------------------------
6184:
6185: FOR i in nvl(l_dist_tab.FIRST, 0) .. nvl(l_dist_tab.LAST, -1) LOOP
6186:
6187: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
6188:
6189: INSERT INTO ap_invoice_distributions(
6190: batch_id,
6191: invoice_id,
6185: FOR i in nvl(l_dist_tab.FIRST, 0) .. nvl(l_dist_tab.LAST, -1) LOOP
6186:
6187: SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
6188:
6189: INSERT INTO ap_invoice_distributions(
6190: batch_id,
6191: invoice_id,
6192: invoice_line_number,
6193: invoice_distribution_id,
6492: 'N', -- rcv_charge_addition_flag
6493: l_pay_awt_group_id -- pay_awt_group_id 7022001
6494: FROM ap_invoice_lines AIL, --Charge line
6495: ap_invoice_lines AIL1, --ITEM Line
6496: ap_invoice_distributions aid
6497: WHERE ail.invoice_id = X_invoice_id
6498: AND ail.line_number = X_line_number
6499: AND aid.invoice_id = ail.invoice_id
6500: AND aid.invoice_distribution_id = l_dist_tab(i).charge_applicable_to_dist
6529: SET GENERATE_DISTS = 'D'
6530: WHERE invoice_id = X_invoice_id
6531: AND line_number = X_line_number
6532: AND amount = (select sum(amount)
6533: from ap_invoice_distributions
6534: where invoice_id = x_invoice_id
6535: and invoice_line_number = x_line_number);
6536: EXCEPTION
6537: WHEN OTHERS THEN
6559: AND chrg_invoice_line_number = X_line_number
6560: AND exists
6561: (select 'Line Amount Equals Distribution Total'
6562: from ap_invoice_lines_all ail,
6563: ap_invoice_distributions_all aid
6564: where ail.invoice_id = aid.invoice_id
6565: and ail.line_number = aid.invoice_line_number
6566: and ail.invoice_id = X_invoice_id
6567: and ail.line_number = X_line_number
6597: FUNCTION Get_Total_Dist_Amount(
6598: X_invoice_distribution_id IN NUMBER) RETURN NUMBER
6599: IS
6600:
6601: l_original_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
6602:
6603: BEGIN
6604:
6605: SELECT sum(nvl(AID.amount,0))
6603: BEGIN
6604:
6605: SELECT sum(nvl(AID.amount,0))
6606: INTO l_original_amount
6607: FROM ap_invoice_distributions_all AID
6608: WHERE AID.invoice_distribution_id = X_invoice_distribution_id
6609: OR AID.related_id = X_invoice_distribution_id;
6610:
6611:
6632: BEGIN
6633:
6634: SELECT nvl(distribution_line_number,0)
6635: INTO l_dist_line_num
6636: FROM ap_invoice_distributions
6637: WHERE invoice_distribution_id = X_invoice_dist_id;
6638:
6639: RETURN(l_dist_line_num);
6640:
6653: BEGIN
6654:
6655: SELECT nvl(invoice_line_number,0)
6656: INTO l_inv_line_num
6657: FROM ap_invoice_distributions
6658: WHERE invoice_distribution_id = X_invoice_dist_id;
6659:
6660: RETURN(l_inv_line_num);
6661:
6673: BEGIN
6674:
6675: SELECT nvl(ai.invoice_num,0)
6676: INTO l_inv_num
6677: FROM ap_invoice_distributions aid,
6678: ap_invoices ai
6679: WHERE aid.invoice_distribution_id = X_invoice_dist_id
6680: AND aid.invoice_id = ai.invoice_id;
6681:
6692: FUNCTION GET_REVERSAL_RELATED_ID(
6693: X_related_dist_id IN NUMBER )
6694: RETURN NUMBER
6695: IS
6696: l_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
6697: BEGIN
6698:
6699: BEGIN
6700: SELECT invoice_distribution_id
6698:
6699: BEGIN
6700: SELECT invoice_distribution_id
6701: INTO l_distribution_id
6702: FROM ap_invoice_distributions
6703: WHERE parent_reversal_id = X_related_dist_id;
6704: EXCEPTION
6705: WHEN no_data_found THEN
6706: l_distribution_id := null;
6717: -- of the invoice distribution that is reversing the invoice distribution
6718: --identified by x_invoice_dist_id.
6719: -----------------------------------------------------------------------
6720: FUNCTION GET_REVERSING_DIST_NUM(X_Invoice_Dist_Id NUMBER) RETURN NUMBER IS
6721: l_reversing_dist_num ap_invoice_distributions.distribution_line_number%TYPE;
6722: BEGIN
6723:
6724: BEGIN
6725: SELECT distribution_line_number
6723:
6724: BEGIN
6725: SELECT distribution_line_number
6726: INTO l_reversing_dist_num
6727: FROM ap_invoice_distributions
6728: WHERE parent_reversal_id = x_invoice_dist_id;
6729:
6730: EXCEPTION WHEN NO_DATA_FOUND THEN
6731: l_reversing_dist_num := NULL;
6782: IS
6783: l_inv_currency_code ap_invoices.invoice_currency_code%TYPE;
6784: l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
6785: l_dist_line_type VARCHAR2(25);
6786: l_inv_price ap_invoice_distributions.unit_price%TYPE;
6787: l_qty_invoiced NUMBER;
6788: l_po_qty NUMBER;
6789: l_po_price po_line_locations.price_override%TYPE;
6790: l_po_rate NUMBER;
6793: l_match_option po_line_locations.match_option%TYPE;
6794: l_rtxn_uom VARCHAR2(25);
6795: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
6796: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
6797: l_distribution_amt ap_invoice_distributions.amount%TYPE;
6798: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
6799: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
6800: l_corrected_inv_dist_id NUMBER(15);
6801: l_corrected_qty NUMBER;
6794: l_rtxn_uom VARCHAR2(25);
6795: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
6796: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
6797: l_distribution_amt ap_invoice_distributions.amount%TYPE;
6798: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
6799: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
6800: l_corrected_inv_dist_id NUMBER(15);
6801: l_corrected_qty NUMBER;
6802: l_uom_conv_rate NUMBER;
6795: l_rtxn_item_id rcv_shipment_lines.item_id%TYPE;
6796: l_po_uom po_line_locations.unit_meas_lookup_code%TYPE;
6797: l_distribution_amt ap_invoice_distributions.amount%TYPE;
6798: l_dist_base_amt ap_invoice_distributions.base_amount%TYPE;
6799: l_dist_match_type ap_invoice_distributions.dist_match_type%TYPE;
6800: l_corrected_inv_dist_id NUMBER(15);
6801: l_corrected_qty NUMBER;
6802: l_uom_conv_rate NUMBER;
6803: l_rate_diff NUMBER;
6841: D.corrected_quantity, -- l_corrected_quantity
6842: PLL.matching_basis -- l_match_basis /* Amount Based Matching */
6843: FROM ap_invoices_all AI,
6844: ap_system_parameters_all ASP,
6845: ap_invoice_distributions D,
6846: po_distributions PD,
6847: po_lines PL,
6848: po_line_types PLT, -- Amount Based Matching
6849: po_line_locations PLL,
7010:
7011: select nvl(AI.exchange_rate, 1)
7012: into l_corrected_inv_rate
7013: from AP_INVOICES AI,
7014: AP_INVOICE_DISTRIBUTIONS D
7015: where D.invoice_distribution_id = l_corrected_inv_dist_id
7016: and D.invoice_id = AI.invoice_id;
7017:
7018: x_erv := AP_UTILITIES_PKG.ap_round_Currency( l_original_dist_amt *
7075:
7076: select nvl(AI.exchange_rate, 1)
7077: into l_corrected_inv_rate
7078: from AP_INVOICES AI,
7079: AP_INVOICE_DISTRIBUTIONS D
7080: where D.invoice_distribution_id = l_corrected_inv_dist_id
7081: and D.invoice_id = AI.invoice_id;
7082:
7083: x_erv := AP_UTILITIES_PKG.ap_round_Currency( l_original_dist_amt *
7172:
7173: -- Update the calling sequence
7174: --
7175: current_calling_sequence :=
7176: 'AP_INVOICE_DISTRIBUTIONS_PKG.DIST_REFER_ACTIVE_CORR<-'
7177: ||p_Calling_Sequence;
7178:
7179: debug_info := 'Select from ap_invoice_distributions_all';
7180:
7175: current_calling_sequence :=
7176: 'AP_INVOICE_DISTRIBUTIONS_PKG.DIST_REFER_ACTIVE_CORR<-'
7177: ||p_Calling_Sequence;
7178:
7179: debug_info := 'Select from ap_invoice_distributions_all';
7180:
7181: Select count(*)
7182: Into dummy
7183: From ap_invoice_distributions_all AID
7179: debug_info := 'Select from ap_invoice_distributions_all';
7180:
7181: Select count(*)
7182: Into dummy
7183: From ap_invoice_distributions_all AID
7184: Where NVL(AID.cancellation_flag, 'N' ) <> 'Y'
7185: And NVL( AID.reversal_flag, 'N' ) <> 'Y'
7186: And AID.corrected_invoice_dist_id = p_invoice_dist_id
7187: And rownum < 2; --bug 5034678
7234:
7235: -- Update the calling sequence
7236: --
7237: current_calling_sequence :=
7238: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHRG_REFER_ACTIVE_DIST<-'
7239: ||p_Calling_Sequence;
7240:
7241: debug_info := 'Select from ap_invoice_distributions_all';
7242:
7237: current_calling_sequence :=
7238: 'AP_INVOICE_DISTRIBUTIONS_PKG.CHRG_REFER_ACTIVE_DIST<-'
7239: ||p_Calling_Sequence;
7240:
7241: debug_info := 'Select from ap_invoice_distributions_all';
7242:
7243: Select count(*)
7244: Into dummy
7245: From ap_invoice_distributions_all AID
7241: debug_info := 'Select from ap_invoice_distributions_all';
7242:
7243: Select count(*)
7244: Into dummy
7245: From ap_invoice_distributions_all AID
7246: Where AID.charge_applicable_to_dist_id = p_invoice_dist_id
7247: And NVL(AID.cancellation_flag, 'N') <> 'Y'
7248: And NVL(AID.reversal_flag, 'N') <> 'Y';
7249:
7281: global_exception EXCEPTION;
7282:
7283: BEGIN
7284:
7285: current_calling_sequence := 'Ap_Invoice_Distributions_Pkg.Make_Distributions_Permanent<- '
7286: ||p_calling_sequence;
7287:
7288: l_debug_info := 'Updating ap_invoice_distributions';
7289:
7284:
7285: current_calling_sequence := 'Ap_Invoice_Distributions_Pkg.Make_Distributions_Permanent<- '
7286: ||p_calling_sequence;
7287:
7288: l_debug_info := 'Updating ap_invoice_distributions';
7289:
7290: UPDATE ap_invoice_distributions_all
7291: SET distribution_class = 'PERMANENT'
7292: WHERE invoice_id = p_invoice_id
7286: ||p_calling_sequence;
7287:
7288: l_debug_info := 'Updating ap_invoice_distributions';
7289:
7290: UPDATE ap_invoice_distributions_all
7291: SET distribution_class = 'PERMANENT'
7292: WHERE invoice_id = p_invoice_id
7293: AND invoice_line_number = NVL(p_invoice_line_number, invoice_line_number)
7294: AND distribution_class = 'CANDIDATE'
7315: AND ail.line_number = l_line_number_tab(uniq_values);
7316:
7317: --Commented below condition for the bug 7483192
7318: /*AND ail.amount = (SELECT SUM(NVL(aid.amount,0))
7319: FROM ap_invoice_distributions_all aid
7320: WHERE aid.invoice_id = ail.invoice_id
7321: AND aid.invoice_line_number = ail.line_number
7322: AND aid.distribution_class = 'PERMANENT'); */
7323:
7392: l_debug_info := 'Get total charge amount allocated to this distribution';
7393:
7394: SELECT sum(aid.amount)
7395: INTO l_total_charge_amount
7396: FROM ap_invoice_distributions aid
7397: WHERE aid.invoice_id = p_invoice_id
7398: AND aid.charge_applicable_to_dist_id = p_invoice_distribution_id;
7399:
7400: RETURN(l_total_charge_amount);
7412:
7413: END Associated_Charges;
7414:
7415:
7416: END AP_INVOICE_DISTRIBUTIONS_PKG;