[Home] [Help]
250: X_calling_sequence IN VARCHAR2) IS
251:
252: l_single_dist_flag VARCHAR2(1) := 'N';
253: l_first NUMBER;
254: l_po_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
255: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
256: l_item_line_number ap_invoice_lines.line_number%TYPE;
257: l_amount_to_recoup ap_invoice_lines.amount%TYPE;
258: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
605: --end Bug 7483260
606: l_retained_amount,
607: -1 * l_retained_amount
608: FROM ap_invoice_distributions aid,
609: po_distributions pd,
610: po_line_locations pll,
611: rcv_transactions rcv
612: WHERE aid.invoice_distribution_id = l_invoice_distribution_id
613: AND pd.po_distribution_id = aid.po_distribution_id
1076:
1077: --Modified below cursor for bug #7015553 to receive projec_id and task_id
1078: --values from receivables tables if these are different in PO and receipt.
1079:
1080: CURSOR po_distributions_cursor IS
1081: SELECT pd.po_distribution_id,
1082: pd.code_combination_id,
1083: accrue_on_receipt_flag ,
1084: DECODE(PD.destination_type_code,'EXPENSE',
1097: NULL), --expenditure_organization_id
1098: DECODE(PD.destination_type_code,
1099: 'EXPENSE', PD.award_id), --award_id
1100: ap_invoice_distributions_s.nextval
1101: FROM po_distributions_ap_v PD,
1102: rcv_transactions RT
1103: WHERE PD.line_location_id = x_po_line_location_id
1104: and Pd.line_location_id = RT.po_line_location_id
1105: and Pd.po_distribution_id = NVL(RT.po_distribution_id, Pd.po_distribution_id) -- bug 8771037
1105: and Pd.po_distribution_id = NVL(RT.po_distribution_id, Pd.po_distribution_id) -- bug 8771037
1106: and RT.TRANSACTION_ID= X_Rcv_Transaction_Id;
1107:
1108:
1109: l_po_distribution_id PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID%TYPE;
1110: l_ordered_po_qty NUMBER;
1111: l_cancelled_po_qty NUMBER;
1112: l_delivered_po_qty NUMBER;
1113: l_returned_po_qty NUMBER;
1125: l_total_quantity NUMBER;
1126: l_matching_case VARCHAR2(2);
1127: l_amount NUMBER;
1128: l_quantity_invoiced NUMBER;
1129: l_po_ccid po_distributions.code_combination_id%TYPE;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1126: l_matching_case VARCHAR2(2);
1127: l_amount NUMBER;
1128: l_quantity_invoiced NUMBER;
1129: l_po_ccid po_distributions.code_combination_id%TYPE;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1127: l_amount NUMBER;
1128: l_quantity_invoiced NUMBER;
1129: l_po_ccid po_distributions.code_combination_id%TYPE;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1128: l_quantity_invoiced NUMBER;
1129: l_po_ccid po_distributions.code_combination_id%TYPE;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1129: l_po_ccid po_distributions.code_combination_id%TYPE;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1137: i NUMBER;
1130: l_accrue_on_receipt_flag po_distributions.accrue_on_receipt_flag%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1137: i NUMBER;
1138: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1131: l_project_id po_distributions.project_id%TYPE;
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1137: i NUMBER;
1138: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1139: l_unbuilt_flex VARCHAR2(240):='';
1132: l_task_id po_distributions.task_id%TYPE;
1133: l_award_set_id po_distributions.award_id%TYPE;
1134: l_expenditure_type po_distributions.expenditure_type%TYPE;
1135: l_po_expenditure_item_date po_distributions.expenditure_item_date%TYPE;
1136: l_expenditure_organization_id po_distributions.expenditure_organization_id%TYPE;
1137: i NUMBER;
1138: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
1139: l_unbuilt_flex VARCHAR2(240):='';
1140: l_reason_unbuilt_flex VARCHAR2(2000):='';
1141: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1142: l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
1143: l_sum_dist_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
1144: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
1145: l_rounding_index po_distributions.po_distribution_id%TYPE;
1146: l_base_amount ap_invoice_distributions.base_amount%TYPE;
1147: FLEX_OVERLAY_FAILED EXCEPTION;
1148: current_calling_sequence VARCHAR2(2000);
1149: l_debug_info VARCHAR2(2000);
1173: X_Total_Quantity => l_total_quantity,
1174: X_Matching_Case => l_matching_case,
1175: X_Calling_Sequence => current_calling_sequence);
1176:
1177: OPEN PO_Distributions_Cursor;
1178:
1179: LOOP
1180:
1181: Fetch PO_Distributions_Cursor INTO l_po_distribution_id,
1177: OPEN PO_Distributions_Cursor;
1178:
1179: LOOP
1180:
1181: Fetch PO_Distributions_Cursor INTO l_po_distribution_id,
1182: l_po_ccid,
1183: l_accrue_on_receipt_flag,
1184: l_project_id,
1185: l_task_id,
1188: l_expenditure_organization_id,
1189: l_award_set_id,
1190: l_invoice_distribution_id;
1191:
1192: EXIT WHEN PO_Distributions_Cursor%NOTFOUND;
1193:
1194: l_debug_info := 'Get distribution level quantities from RCV API ';
1195: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1196: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1366: --End bug#9913906
1367:
1368: END LOOP;
1369:
1370: CLOSE PO_Distributions_Cursor;
1371:
1372: --Perform Proration Rounding
1373: --Update the PL/SQL table's amount column with the rounding amount due
1374: --to proration, before the base_amounts are calculated.
1402: x_dist_tab(i).expenditure_type,
1403: x_dist_tab(i).expenditure_item_date,
1404: x_dist_tab(i).expenditure_organization_id,
1405: x_dist_tab(i).invoice_distribution_id
1406: FROM po_distributions_ap_v pd
1407: ,rcv_transactions RT --Added for bug 13845471
1408: WHERE pd.po_distribution_id = x_dist_tab(i).po_distribution_id
1409: AND pd.po_distribution_id = NVL(rt.po_distribution_id,pd.po_distribution_id) --Added for bug 13845471
1410: AND rt.TRANSACTION_ID= X_Rcv_Transaction_Id; --Added for bug 13845471
1646: --bugfix:4668058 added the following clause
1647: (x_dist_tab(i).project_id IS NOT NULL AND g_allow_pa_override = 'N')
1648: ) THEN
1649:
1650: --po_distributions_ap_v.code_combination_id is accrual account
1651: --if accruing on receipt or else charge account
1652:
1653: x_dist_tab(i).dist_ccid := x_dist_tab(i).po_ccid;
1654:
2318: --Modified for bug#13505998 G_ASSESSABLE_VALUE,
2319: DECODE(X_Amount,l_full_mtch_amt,G_ASSESSABLE_VALUE, NULL),
2320: G_dflt_tax_class_code,
2321: G_PAY_AWT_GROUP_ID --bug 9689194
2322: FROM PO_DISTRIBUTIONS_AP_V PD,
2323: MTL_SYSTEM_ITEMS MSI,
2324: PO_AP_RECEIPT_MATCH_V RCV,
2325: RCV_SHIPMENT_LINES RSL,
2326: RCV_TRANSACTIONS RT --for 7015553
3066: 'N', --accrual_posted_flag
3067: 'N', --cash_posted_flag
3068: 'N', --rcv_charge_addition_flag
3069: x_dist_tab(i).pay_awt_group_id --pay_awt_group_id bug9689194
3070: FROM po_distributions pd,--bug6683010
3071: ap_invoice_lines ail,
3072: po_ap_receipt_match_v rcv,
3073: gl_code_combinations gcc
3074: WHERE ail.invoice_id = x_invoice_id
3170:
3171: current_calling_sequence := 'Update_Po_Shipments_Dists<-'||x_calling_sequence;
3172:
3173: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3174: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RECT_MATCH_PKG.Update_PO_Distributions(+)');
3175: END IF;
3176:
3177: l_debug_info := 'Create l_po_ap_dist_rec object';
3178: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3260: END IF;
3261:
3262: END LOOP;
3263:
3264: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
3265: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3266: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3267: END IF;
3268:
3486: X_Uom_Lookup_Code IN VARCHAR2,
3487: X_Retained_Amount IN NUMBER DEFAULT NULL,
3488: X_Calling_Sequence IN VARCHAR2) IS
3489:
3490: l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE := NULL;
3491: l_item_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
3492: l_amount_to_recoup AP_INVOICE_LINES_ALL.AMOUNT%TYPE;
3493: l_line_amt_net_retainage ap_invoice_lines_all.amount%TYPE;
3494: l_max_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
3769: pd.variance_account_id),
3770: ap_invoice_distributions_s.nextval
3771: FROM ap_invoice_lines ail,
3772: ap_invoice_distributions aid1,
3773: po_distributions pd,
3774: ap_invoice_distributions aid2
3775: WHERE ail.invoice_id = x_corrected_invoice_id
3776: AND ail.line_number = x_corrected_line_number
3777: AND aid1.invoice_id = ail.invoice_id
3813: aid.dist_code_combination_id,
3814: ap_invoice_distributions_s.nextval
3815: FROM ap_invoice_distributions aid,
3816: ap_invoice_lines ail,
3817: po_distributions pd
3818: WHERE ail.invoice_id = x_corrected_invoice_id
3819: AND ail.line_number = x_corrected_line_number
3820: AND aid.invoice_id = ail.invoice_id
3821: AND aid.invoice_line_number = ail.line_number
3827: AND (aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX')
3828: OR aid.prepay_distribution_id IS NULL); */
3829:
3830: l_corrected_inv_dist_id ap_invoice_distributions.corrected_invoice_dist_id%TYPE;
3831: l_po_dist_id po_distributions.po_distribution_id%TYPE;
3832: l_amount ap_invoice_distributions.amount%TYPE;
3833: l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3834: l_base_amount ap_invoice_distributions.base_amount%TYPE;
3835: l_total_quantity_billed po_distributions.quantity_billed%TYPE;
3831: l_po_dist_id po_distributions.po_distribution_id%TYPE;
3832: l_amount ap_invoice_distributions.amount%TYPE;
3833: l_corrected_quantity ap_invoice_distributions.corrected_quantity%TYPE;
3834: l_base_amount ap_invoice_distributions.base_amount%TYPE;
3835: l_total_quantity_billed po_distributions.quantity_billed%TYPE;
3836: l_dist_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
3837: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3838: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
3839: l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
3909: ELSIF (x_correction_type = 'QTY_CORRECTION') THEN
3910:
3911: SELECT sum(pd.quantity_billed)
3912: INTO l_total_quantity_billed
3913: FROM po_distributions pd,
3914: ap_invoice_distributions aid
3915: WHERE pd.po_distribution_id = aid.po_distribution_id
3916: AND aid.invoice_id = x_corrected_invoice_id
3917: AND aid.invoice_line_number = x_corrected_line_number
4077:
4078: current_calling_sequence := 'Update_Corr_Po_Shipments_Dists<-'||x_calling_sequence;
4079:
4080: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4081: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_PKG.Update_Corr_Po_Distributions(+)');
4082: END IF;
4083:
4084: l_debug_info := 'Create l_po_ap_dist_rec object';
4085: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4125:
4126: END IF;
4127:
4128:
4129: l_debug_info := 'Call PO api to update po_distributions and po_shipments table'
4130: ||' with quantity/amount billed information.';
4131:
4132: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4133: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4168: END IF;
4169:
4170: END LOOP;
4171:
4172: l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
4173: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4174: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4175: END IF;
4176:
4586: l_distribution_line_number ap_invoice_distributions.distribution_line_number%type := 1;
4587: l_rounded_dist_id ap_invoice_distributions.invoice_distribution_id%type := NULL;
4588: l_sum_amount_prorated ap_invoice_distributions.amount%TYPE := 0;
4589: l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
4590: l_po_distribution_id po_distributions.po_distribution_id%TYPE;
4591: l_debug_info VARCHAR2(2000);
4592: current_calling_sequence VARCHAR2(2000);
4593: l_api_name VARCHAR2(50);
4594:
4925: decode(g_site_allow_awt_flag,'Y',ail.pay_awt_group_id,NULL),
4926: NULL) --pay_awt_group_id Bug9689194
4927: FROM ap_invoice_lines ail,
4928: ap_invoice_distributions aid,
4929: po_distributions pd
4930: WHERE ail.invoice_id = x_invoice_id
4931: AND ail.line_number = x_invoice_line_number
4932: AND aid.invoice_id = ail.corrected_inv_id
4933: AND aid.invoice_line_number = ail.corrected_line_number