[Home] [Help]
248: X_calling_sequence IN VARCHAR2) IS
249:
250: l_single_dist_flag VARCHAR2(1) := 'N';
251: l_first NUMBER;
252: l_po_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
253: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
254: l_item_line_number ap_invoice_lines.line_number%TYPE;
255: l_amount_to_recoup ap_invoice_lines.amount%TYPE;
256: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
516: --end Bug 7483260
517: l_retained_amount,
518: -1 * l_retained_amount
519: FROM ap_invoice_distributions aid,
520: po_distributions pd,
521: po_line_locations pll,
522: rcv_transactions rcv
523: WHERE aid.invoice_distribution_id = l_invoice_distribution_id
524: AND pd.po_distribution_id = aid.po_distribution_id
977:
978: --Modified below cursor for bug #7015553 to receive projec_id and task_id
979: --values from receivables tables if these are different in PO and receipt.
980:
981: CURSOR po_distributions_cursor IS
982: SELECT pd.po_distribution_id,
983: pd.code_combination_id,
984: accrue_on_receipt_flag ,
985: DECODE(PD.destination_type_code,'EXPENSE',
998: NULL), --expenditure_organization_id
999: DECODE(PD.destination_type_code,
1000: 'EXPENSE', PD.award_id), --award_id
1001: ap_invoice_distributions_s.nextval
1002: FROM po_distributions_ap_v PD,
1003: rcv_transactions RT
1004: WHERE PD.line_location_id = x_po_line_location_id
1005: and Pd.line_location_id = RT.po_line_location_id
1006: and RT.TRANSACTION_ID= X_Rcv_Transaction_Id;
1005: and Pd.line_location_id = RT.po_line_location_id
1006: and RT.TRANSACTION_ID= X_Rcv_Transaction_Id;
1007:
1008:
1009: l_po_distribution_id PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID%TYPE;
1010: l_ordered_po_qty NUMBER;
1011: l_cancelled_po_qty NUMBER;
1012: l_delivered_po_qty NUMBER;
1013: l_returned_po_qty NUMBER;
1025: l_total_quantity NUMBER;
1026: l_matching_case VARCHAR2(2);
1027: l_amount NUMBER;
1028: l_quantity_invoiced NUMBER;
1029: l_po_ccid po_distributions.code_combination_id%TYPE;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1026: l_matching_case VARCHAR2(2);
1027: l_amount NUMBER;
1028: l_quantity_invoiced NUMBER;
1029: l_po_ccid po_distributions.code_combination_id%TYPE;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1027: l_amount NUMBER;
1028: l_quantity_invoiced NUMBER;
1029: l_po_ccid po_distributions.code_combination_id%TYPE;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1028: l_quantity_invoiced NUMBER;
1029: l_po_ccid po_distributions.code_combination_id%TYPE;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1036: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1029: l_po_ccid po_distributions.code_combination_id%TYPE;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1036: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1037: i NUMBER;
1030: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1036: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1037: i NUMBER;
1038: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1031: l_project_id po_distributions.project_id%TYPE;
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1036: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1037: i NUMBER;
1038: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1039: l_unbuilt_flex VARCHAR2(240):='';
1032: l_task_id po_distributions.task_id%TYPE;
1033: l_award_set_id po_distributions.award_id%TYPE;
1034: l_expenditure_type po_distributions.expenditure_type%TYPE;
1035: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1036: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1037: i NUMBER;
1038: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1039: l_unbuilt_flex VARCHAR2(240):='';
1040: l_reason_unbuilt_flex VARCHAR2(2000):='';
1041: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1042: l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
1043: l_sum_dist_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
1044: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
1045: l_rounding_index po_distributions.po_distribution_id%TYPE;
1046: l_base_amount ap_invoice_distributions.base_amount%TYPE;
1047: FLEX_OVERLAY_FAILED EXCEPTION;
1048: current_calling_sequence VARCHAR2(2000);
1049: l_debug_info VARCHAR2(2000);
1073: X_Total_Quantity => l_total_quantity,
1074: X_Matching_Case => l_matching_case,
1075: X_Calling_Sequence => current_calling_sequence);
1076:
1077: OPEN PO_Distributions_Cursor;
1078:
1079: LOOP
1080:
1081: Fetch PO_Distributions_Cursor INTO l_po_distribution_id,
1077: OPEN PO_Distributions_Cursor;
1078:
1079: LOOP
1080:
1081: Fetch PO_Distributions_Cursor INTO l_po_distribution_id,
1082: l_po_ccid,
1083: l_accrue_on_receipt_flag,
1084: l_project_id,
1085: l_task_id,
1088: l_expenditure_organization_id,
1089: l_award_set_id,
1090: l_invoice_distribution_id;
1091:
1092: EXIT WHEN PO_Distributions_Cursor%NOTFOUND;
1093:
1094: l_debug_info := 'Get distribution level quantities from RCV API ';
1095: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1096: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1231: l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
1232:
1233: END LOOP;
1234:
1235: CLOSE PO_Distributions_Cursor;
1236:
1237: --Perform Proration Rounding
1238: --Update the PL/SQL table's amount column with the rounding amount due
1239: --to proration, before the base_amounts are calculated.
1267: x_dist_tab(i).expenditure_type,
1268: x_dist_tab(i).expenditure_item_date,
1269: x_dist_tab(i).expenditure_organization_id,
1270: x_dist_tab(i).invoice_distribution_id
1271: FROM po_distributions_ap_v pd
1272: WHERE pd.po_distribution_id = x_dist_tab(i).po_distribution_id;
1273:
1274: -- Bug 5554493
1275: x_dist_tab(i).pa_quantity := x_dist_tab(i).quantity_invoiced;
1504: --bugfix:4668058 added the following clause
1505: (x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'N')
1506: ) THEN
1507:
1508: --po_distributions_ap_v.code_combination_id is accrual account
1509: --if accruing on receipt or else charge account
1510:
1511: x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1512:
2145: G_PRODUCT_CATEGORY, --product_category
2146: G_USER_DEFINED_FISC_CLASS, --user_defined_fisc_clas
2147: G_ASSESSABLE_VALUE,
2148: G_dflt_tax_class_code
2149: FROM PO_DISTRIBUTIONS_AP_V PD,
2150: MTL_SYSTEM_ITEMS MSI,
2151: PO_AP_RECEIPT_MATCH_V RCV,
2152: RCV_SHIPMENT_LINES RSL,
2153: RCV_TRANSACTIONS RT --for 7015553
2858: g_intended_use, --intended_use
2859: 'N', --accrual_posted_flag
2860: 'N', --cash_posted_flag
2861: 'N' --rcv_charge_addition_flag
2862: FROM po_distributions pd,--bug6683010
2863: ap_invoice_lines ail,
2864: po_ap_receipt_match_v rcv,
2865: gl_code_combinations gcc
2866: WHERE ail.invoice_id = x_invoice_id
2962:
2963: current_calling_sequence := 'Update_Po_Shipments_Dists<-'||x_calling_sequence;
2964:
2965: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2966: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Distributions(+)');
2967: END IF;
2968:
2969: l_debug_info := 'Create l_po_ap_dist_rec object';
2970: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3052: END IF;
3053:
3054: END LOOP;
3055:
3056: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3057: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3058: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3059: END IF;
3060:
3277: X_Uom_Lookup_Code IN VARCHAR2,
3278: X_Retained_Amount IN NUMBER DEFAULT NULL,
3279: X_Calling_Sequence IN VARCHAR2) IS
3280:
3281: l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE := NULL;
3282: l_item_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
3283: l_amount_to_recoup AP_INVOICE_LINES_ALL.AMOUNT%TYPE;
3284: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
3285: l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3559: pd.variance_account_id),
3560: ap_invoice_distributions_s.nextval
3561: FROM ap_invoice_lines ail,
3562: ap_invoice_distributions aid,
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
3600: aid.dist_code_combination_id,
3601: ap_invoice_distributions_s.nextval
3602: FROM ap_invoice_distributions aid,
3603: ap_invoice_lines ail,
3604: po_distributions pd
3605: WHERE ail.invoice_id = x_corrected_invoice_id
3606: AND ail.line_number = x_corrected_line_number
3607: AND aid.invoice_id = ail.invoice_id
3608: AND aid.invoice_line_number = ail.line_number
3614: AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3615: OR aid.prepay_distribution_id IS NULL); */
3616:
3617: l_corrected_inv_dist_id ap_invoice_distributions.corrected_invoice_dist_id%TYPE;
3618: l_po_dist_id po_distributions.po_distribution_id%TYPE;
3619: l_amount ap_invoice_distributions.amount%TYPE;
3620: l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3621: l_base_amount ap_invoice_distributions.base_amount%TYPE;
3622: l_total_quantity_billed po_distributions.quantity_billed%TYPE;
3618: l_po_dist_id po_distributions.po_distribution_id%TYPE;
3619: l_amount ap_invoice_distributions.amount%TYPE;
3620: l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3621: l_base_amount ap_invoice_distributions.base_amount%TYPE;
3622: l_total_quantity_billed po_distributions.quantity_billed%TYPE;
3623: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
3624: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3625: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
3626: l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
3692: ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3693:
3694: SELECT sum(pd.quantity_billed)
3695: INTO l_total_quantity_billed
3696: FROM po_distributions pd,
3697: ap_invoice_distributions aid
3698: WHERE pd.po_distribution_id = aid.po_distribution_id
3699: AND aid.invoice_id = x_corrected_invoice_id
3700: AND aid.invoice_line_number = x_corrected_line_number
3858:
3859: current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
3860:
3861: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3862: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
3863: END IF;
3864:
3865: l_debug_info := 'Create l_po_ap_dist_rec object';
3866: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3906:
3907: END IF;
3908:
3909:
3910: l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
3911: ||' with quantity/amount billed information.';
3912:
3913: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3914: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3949: END IF;
3950:
3951: END LOOP;
3952:
3953: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3954: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3955: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3956: END IF;
3957:
4363: l_distribution_line_number ap_invoice_distributions.distribution_line_number%type := 1;
4364: l_rounded_dist_id ap_invoice_distributions.invoice_distribution_id%type := NULL;
4365: l_sum_amount_prorated ap_invoice_distributions.amount%TYPE := 0;
4366: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
4367: l_po_distribution_id po_distributions.po_distribution_id%TYPE;
4368: l_debug_info VARCHAR2(2000);
4369: current_calling_sequence VARCHAR2(2000);
4370: l_api_name VARCHAR2(50);
4371:
4698: 'N', --cash_posted_flag
4699: 'N' --rcv_charge_addition_flag
4700: FROM ap_invoice_lines ail,
4701: ap_invoice_distributions aid,
4702: po_distributions pd
4703: WHERE ail.invoice_id = x_invoice_id
4704: AND ail.line_number = x_invoice_line_number
4705: AND aid.invoice_id = ail.corrected_inv_id
4706: AND aid.invoice_line_number = ail.corrected_line_number