[Home] [Help]
178: G_Build_Prepay_Accts_Flag ap_system_parameters.build_prepayment_accounts_flag%TYPE;
179: G_Income_Tax_Region ap_system_parameters.income_tax_region%TYPE;
180: G_Project_ID pa_projects_all.project_id%TYPE;
181: G_Task_ID pa_tasks.task_id%TYPE;
182: G_Award_ID po_distributions_all.award_id%TYPE;
183: G_Expenditure_Type pa_expenditure_types.expenditure_type%TYPE;
184: G_Invoice_Date ap_invoices.invoice_date%TYPE;
185: G_Expenditure_Organization_ID pa_exp_orgs_it.organization_id%TYPE;
186: G_Asset_Book_Type_Code fa_book_controls.book_type_code%TYPE;
249: X_retained_amount IN NUMBER DEFAULT NULL,
250: X_calling_sequence IN VARCHAR2) IS
251:
252: l_single_dist_flag varchar2(1) := 'N';
253: l_po_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
254: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
255: l_item_line_number ap_invoice_lines_all.line_number%TYPE;
256: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
257: l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
304: p_match_amount => x_amount);
305: END IF;
306:
307: --If shipment level match then we need to prorate the match-quantity among the
308: --po distributions of the shipment, for distribution level match we need to
309: --derive the invoice_distribution_id, base_amount, ccid.
310:
311: l_debug_info := 'Get PO information';
312: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
523: --end Bug 7483260
524: l_retained_amount,
525: -1 * l_retained_amount
526: FROM ap_invoice_distributions aid,
527: po_distributions pd,
528: po_line_locations pll
529: WHERE aid.invoice_distribution_id = l_invoice_distribution_id
530: AND pd.po_distribution_id = aid.po_distribution_id
531: AND pll.line_location_id = pd.line_location_id)
996: X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
997: X_Calling_Sequence IN VARCHAR2) IS
998:
999:
1000: CURSOR po_distributions_cursor(p_total_quantity NUMBER) IS
1001: SELECT po_distribution_id,
1002: /*PRORATED AMOUNT*/
1003: DECODE(g_min_acct_unit,
1004: '', ROUND( X_match_amount * DECODE(X_match_mode,
1087: NULL), --expenditure_organization_id
1088: DECODE(PD.destination_type_code,
1089: 'EXPENSE', PD.award_id), --award_id
1090: ap_invoice_distributions_s.nextval
1091: FROM po_distributions_ap_v PD
1092: WHERE line_location_id = x_po_line_location_id;
1093:
1094:
1095: l_total_quantity number;
1092: WHERE line_location_id = x_po_line_location_id;
1093:
1094:
1095: l_total_quantity number;
1096: l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1097: l_amount_invoiced ap_invoice_distributions_all.amount%TYPE;
1098: l_quantity_invoiced ap_invoice_distributions_all.quantity_invoiced%TYPE;
1099: l_po_dist_ccid po_distributions.code_combination_id%TYPE;
1100: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1095: l_total_quantity number;
1096: l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1097: l_amount_invoiced ap_invoice_distributions_all.amount%TYPE;
1098: l_quantity_invoiced ap_invoice_distributions_all.quantity_invoiced%TYPE;
1099: l_po_dist_ccid po_distributions.code_combination_id%TYPE;
1100: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1101: l_project_id po_distributions.project_id%TYPE;
1102: l_unbuilt_flex varchar2(240):='';
1103: l_reason_unbuilt_flex varchar2(2000):='';
1096: l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1097: l_amount_invoiced ap_invoice_distributions_all.amount%TYPE;
1098: l_quantity_invoiced ap_invoice_distributions_all.quantity_invoiced%TYPE;
1099: l_po_dist_ccid po_distributions.code_combination_id%TYPE;
1100: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1101: l_project_id po_distributions.project_id%TYPE;
1102: l_unbuilt_flex varchar2(240):='';
1103: l_reason_unbuilt_flex varchar2(2000):='';
1104: l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1097: l_amount_invoiced ap_invoice_distributions_all.amount%TYPE;
1098: l_quantity_invoiced ap_invoice_distributions_all.quantity_invoiced%TYPE;
1099: l_po_dist_ccid po_distributions.code_combination_id%TYPE;
1100: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1101: l_project_id po_distributions.project_id%TYPE;
1102: l_unbuilt_flex varchar2(240):='';
1103: l_reason_unbuilt_flex varchar2(2000):='';
1104: l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1105: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1102: l_unbuilt_flex varchar2(240):='';
1103: l_reason_unbuilt_flex varchar2(2000):='';
1104: l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1105: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1106: l_task_id po_distributions.task_id%TYPE;
1107: l_award_set_id po_distributions_all.award_id%TYPE;
1108: l_expenditure_type po_distributions.expenditure_type%TYPE;
1109: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1103: l_reason_unbuilt_flex varchar2(2000):='';
1104: l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1105: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1106: l_task_id po_distributions.task_id%TYPE;
1107: l_award_set_id po_distributions_all.award_id%TYPE;
1108: l_expenditure_type po_distributions.expenditure_type%TYPE;
1109: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111: l_max_dist_amount number := 0;
1104: l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
1105: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1106: l_task_id po_distributions.task_id%TYPE;
1107: l_award_set_id po_distributions_all.award_id%TYPE;
1108: l_expenditure_type po_distributions.expenditure_type%TYPE;
1109: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111: l_max_dist_amount number := 0;
1112: l_sum_prorated_amount number := 0;
1105: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1106: l_task_id po_distributions.task_id%TYPE;
1107: l_award_set_id po_distributions_all.award_id%TYPE;
1108: l_expenditure_type po_distributions.expenditure_type%TYPE;
1109: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111: l_max_dist_amount number := 0;
1112: l_sum_prorated_amount number := 0;
1113: l_sum_dist_base_amount number := 0;
1106: l_task_id po_distributions.task_id%TYPE;
1107: l_award_set_id po_distributions_all.award_id%TYPE;
1108: l_expenditure_type po_distributions.expenditure_type%TYPE;
1109: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111: l_max_dist_amount number := 0;
1112: l_sum_prorated_amount number := 0;
1113: l_sum_dist_base_amount number := 0;
1114: l_rounding_index po_distributions.po_distribution_id%TYPE;
1110: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1111: l_max_dist_amount number := 0;
1112: l_sum_prorated_amount number := 0;
1113: l_sum_dist_base_amount number := 0;
1114: l_rounding_index po_distributions.po_distribution_id%TYPE;
1115: l_base_amount ap_invoice_distributions.base_amount%TYPE;
1116: flex_overlay_failed exception;
1117: current_calling_sequence varchar2(2000);
1118: l_debug_info varchar2(2000);
1147: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1148: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1149: END IF;
1150:
1151: OPEN PO_Distributions_Cursor(l_total_quantity);
1152: LOOP
1153:
1154: l_debug_info := 'Fetch record from Po_Distributions_Cursor l_total_quantity is '||l_total_quantity;
1155: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1150:
1151: OPEN PO_Distributions_Cursor(l_total_quantity);
1152: LOOP
1153:
1154: l_debug_info := 'Fetch record from Po_Distributions_Cursor l_total_quantity is '||l_total_quantity;
1155: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1157: END IF;
1158:
1155: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1157: END IF;
1158:
1159: FETCH PO_Distributions_Cursor INTO l_po_distribution_id,
1160: l_amount_invoiced,
1161: l_quantity_invoiced,
1162: l_po_dist_ccid,
1163: l_accrue_on_receipt_flag,
1168: l_expenditure_organization_id,
1169: l_award_set_id,
1170: l_invoice_distribution_id;
1171:
1172: EXIT WHEN PO_Distributions_Cursor%NOTFOUND;
1173:
1174: --IF (l_amount_invoiced <> 0) THEN --Bug6321189
1175:
1176: l_debug_info := 'Populate the PL/SQL table x_dist_tab with the distribution information';
1209: --END IF; /* (l_amount_invoiced <> 0) */
1210:
1211: END LOOP;
1212:
1213: CLOSE PO_Distributions_Cursor;
1214:
1215: --Update the PL/SQL table's amount column with the rounding amount due
1216: --to proration, before the base_amounts are calculated.
1217:
1245: x_dist_tab(i).expenditure_type,
1246: x_dist_tab(i).expenditure_item_date,
1247: x_dist_tab(i).expenditure_organization_id,
1248: x_dist_tab(i).invoice_distribution_id
1249: FROM po_distributions_ap_v
1250: WHERE po_distribution_id = x_dist_tab(i).po_distribution_id;
1251:
1252: -- Bug 5554493
1253: x_dist_tab(i).pa_quantity := x_dist_tab(i).quantity_invoiced;
1485: --bugfix:4668058 added the following clause
1486: (x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'N')
1487: ) THEN
1488:
1489: --po_distributions_ap_v.code_combination_id is accrual account
1490: --if accruing on receipt or else charge account
1491:
1492: x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1493:
1659: )
1660: )
1661: )
1662: INTO X_Total_Quantity
1663: FROM po_distributions_ap_v PD
1664: WHERE line_location_id = X_Po_Line_Location_Id;
1665:
1666: IF(x_total_quantity = 0) THEN
1667: x_total_quantity := 1;
1727: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1728: END IF;
1729:
1730: -- bug 5061826
1731: -- go to base tables PO_LINES_ALL, PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
1732: -- instead of PO_LINE_LOCATIONS_AP_V and PO_DISTRIBUTIONS_AP_V
1733:
1734: INSERT INTO AP_INVOICE_LINES (
1735: INVOICE_ID,
1728: END IF;
1729:
1730: -- bug 5061826
1731: -- go to base tables PO_LINES_ALL, PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
1732: -- instead of PO_LINE_LOCATIONS_AP_V and PO_DISTRIBUTIONS_AP_V
1733:
1734: INSERT INTO AP_INVOICE_LINES (
1735: INVOICE_ID,
1736: LINE_NUMBER,
2103: G_PAY_AWT_GROUP_ID --pay_awt_group_id bug8222382
2104: -- bug 5061826 -- new FROM clause that goes to base tables
2105: FROM PO_LINES_ALL PL,
2106: PO_LINE_LOCATIONS_ALL PLL,
2107: po_distributions pd,
2108: mtl_system_items msi
2109: WHERE pll.line_location_id = x_po_line_location_id
2110: and pd.line_location_id = pll.line_location_id
2111: AND PLL.PO_LINE_ID = PL.PO_LINE_ID
2114: and msi.organization_id(+) = g_inventory_organization_id
2115: and rownum = 1;
2116: /* -- bug 5061826 -- commented out older FROM clause
2117: FROM PO_LINE_LOCATIONS_AP_V PLL,
2118: PO_DISTRIBUTIONS_AP_V PD,
2119: MTL_SYSTEM_ITEMS MSI
2120: WHERE PLL.LINE_LOCATION_ID = X_PO_LINE_LOCATION_ID
2121: AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
2122: AND PD.PO_DISTRIBUTION_ID = NVL(X_PO_DISTRIBUTION_ID,PD.PO_DISTRIBUTION_ID)
2864: 'N', --accrual_posted_flag
2865: 'N', --cash_posted_flag
2866: 'N', --rcv_charge_addition_flag
2867: x_dist_tab(i).pay_awt_group_id --pay_awt_group_id bug8222382
2868: FROM po_distributions pd,
2869: ap_invoice_lines ail,
2870: gl_code_combinations gcc
2871: WHERE ail.invoice_id = x_invoice_id
2872: AND ail.line_number = x_invoice_line_number
3047: END IF;
3048:
3049: END LOOP;
3050:
3051: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3052: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3053: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3054: END IF;
3055:
3225: X_Uom_Lookup_Code IN VARCHAR2,
3226: X_Retained_Amount IN NUMBER DEFAULT NULL,
3227: X_Calling_Sequence IN VARCHAR2) IS
3228:
3229: l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE := NULL;
3230: l_item_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
3231: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
3232: l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3233: l_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3520: pd.variance_account_id),
3521: ap_invoice_distributions_s.nextval
3522: FROM ap_invoice_lines ail,
3523: ap_invoice_distributions aid,
3524: po_distributions pd
3525: WHERE ail.invoice_id = x_corrected_invoice_id
3526: AND ail.line_number = x_corrected_line_number
3527: AND aid.invoice_id = ail.invoice_id
3528: AND aid.po_distribution_id = pd.po_distribution_id /*bugfix:5606697*/
3559: aid.dist_code_combination_id,
3560: ap_invoice_distributions_s.nextval
3561: FROM ap_invoice_distributions aid,
3562: ap_invoice_lines ail,
3563: po_distributions pd
3564: WHERE ail.invoice_id = x_corrected_invoice_id
3565: AND ail.line_number = x_corrected_line_number
3566: AND aid.invoice_id = ail.invoice_id
3567: AND aid.invoice_line_number = ail.line_number
3661: IF (x_match_mode IN ('STD-PS','CR-PS')) THEN
3662:
3663: SELECT sum(decode(pd.distribution_type,'PREPAYMENT',nvl(pd.quantity_financed,0),nvl(pd.quantity_billed,0)))
3664: INTO l_total_quantity_billed
3665: FROM po_distributions pd,
3666: ap_invoice_distributions aid
3667: WHERE pd.po_distribution_id = aid.po_distribution_id
3668: AND aid.invoice_id = x_corrected_invoice_id
3669: AND aid.invoice_line_number = x_corrected_line_number
3838:
3839: current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
3840:
3841: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3842: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
3843: END IF;
3844:
3845: l_debug_info := 'Create l_po_ap_dist_rec object';
3846: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3886:
3887: END IF;
3888:
3889:
3890: l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
3891: ||' with quantity/amount billed information.';
3892:
3893: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3894: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3929: END IF;
3930:
3931: END LOOP;
3932:
3933: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3934: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3935: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3936: END IF;
3937:
4770: ail.pay_awt_group_id,NULL),
4771: NULL) --pay_awt_group_id bug8222382
4772: FROM ap_invoice_lines ail,
4773: ap_invoice_distributions aid,
4774: po_distributions pd
4775: WHERE ail.invoice_id = x_invoice_id
4776: AND ail.line_number = x_invoice_line_number
4777: AND aid.invoice_id = ail.corrected_inv_id
4778: AND aid.invoice_line_number = ail.corrected_line_number