[Home] [Help]
19: P_Calling_Sequence IN VARCHAR2)
20: IS
21: withholding_total NUMBER := 0;
22: base_withholding_total NUMBER := 0;
23: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
24:
25: CURSOR C_temp_dists (InvId IN NUMBER) IS
26: SELECT AATD.invoice_id
27: , AATD.payment_num
123: l_disc_amt_factor NUMBER;
124: l_disc_amt_divisor NUMBER; -- BUG 7000143
125: l_basecur ap_system_parameters.base_currency_code%TYPE;
126: l_enable_1099_on_awt_flag ap_system_parameters.enable_1099_on_awt_flag%TYPE;
127: l_type_1099 ap_invoice_distributions.type_1099%TYPE;
128: l_combined_filing_flag ap_system_parameters.combined_filing_flag%TYPE;
129: l_income_tax_region_asp ap_system_parameters.income_tax_region%TYPE;
130: l_income_tax_region_pvs ap_system_parameters.income_tax_region%TYPE;
131: l_income_tax_region_flag ap_system_parameters.income_tax_region_flag%TYPE;
333: SYSDATE,
334: P_request_ID,
335: rec_temp_dists.org_id, --7230158
336: decode (rec_temp_dists.invoice_payment_id,NULL,NULL,rec_temp_dists.group_id)); --7230158
337: debug_info := 'Insert INTO ap_invoice_distributions';
338:
339: INSERT INTO ap_invoice_distributions_all (
340: accounting_date
341: ,accrual_posted_flag
335: rec_temp_dists.org_id, --7230158
336: decode (rec_temp_dists.invoice_payment_id,NULL,NULL,rec_temp_dists.group_id)); --7230158
337: debug_info := 'Insert INTO ap_invoice_distributions';
338:
339: INSERT INTO ap_invoice_distributions_all (
340: accounting_date
341: ,accrual_posted_flag
342: ,assets_addition_flag
343: ,assets_tracking_flag
454: rec_temp_dists.gross_amount/rec_invoice.exchange_rate,
455: P_currency_code)
456: ,rec_temp_dists.group_id
457: ,rec_temp_dists.invoice_payment_id
458: ,ap_invoice_distributions_s.nextval
459: ,rec_temp_dists.GLOBAL_ATTRIBUTE_CATEGORY
460: ,rec_temp_dists.GLOBAL_ATTRIBUTE1
461: ,rec_temp_dists.GLOBAL_ATTRIBUTE2
462: ,rec_temp_dists.GLOBAL_ATTRIBUTE3
486: );
487:
488: --Bug 4539462 DBI logging
489: AP_DBI_PKG.Maintain_DBI_Summary
490: ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
491: p_operation => 'I',
492: p_key_value1 => P_invoice_id,
493: p_key_value2 => l_Invoice_distribution_id,
494: p_calling_sequence => current_calling_sequence);
721: ta_accts_pay_code_comb_id po_vendor_sites.accts_pay_code_combination_id%TYPE;
722: ta_payment_currency_code po_vendor_sites.payment_currency_code%TYPE;
723: c_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
724: c_payment_cross_rate_type ap_invoices.payment_cross_rate_type%TYPE;
725: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
726: l_legal_entity_id ap_invoices_all.legal_entity_id%type;
727:
728:
729: l_IBY_PAYMENT_METHOD varchar2(80);
774: , APID.batch_id batch_id
775: --, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
776: --, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
777: , APID.org_id
778: FROM ap_invoice_distributions_all APID,
779: ap_tax_codes_all ATC,
780: ap_invoices_all AI
781: WHERE (APID.invoice_id = InvId)
782: AND (APID.invoice_line_number = NVL(P_Inv_Line_No,invoice_line_number))
1225:
1226: --To be resolved by DBI forward porting project.
1227: /*
1228: AP_DBI_PKG.Maintain_DBI_Summary
1229: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1230: p_operation => 'I',
1231: p_key_value1 => new_invoice_id,
1232: p_key_value2 => l_Invoice_distribution_Id,
1233: p_calling_sequence => current_calling_sequence); */
1231: p_key_value1 => new_invoice_id,
1232: p_key_value2 => l_Invoice_distribution_Id,
1233: p_calling_sequence => current_calling_sequence); */
1234:
1235: debug_info := 'Insert INTO ap_invoice_distributions';
1236:
1237: SELECT ap_invoice_distributions_s.nextval
1238: INTO l_invoice_distribution_id
1239: FROM DUAL;
1233: p_calling_sequence => current_calling_sequence); */
1234:
1235: debug_info := 'Insert INTO ap_invoice_distributions';
1236:
1237: SELECT ap_invoice_distributions_s.nextval
1238: INTO l_invoice_distribution_id
1239: FROM DUAL;
1240:
1241:
1238: INTO l_invoice_distribution_id
1239: FROM DUAL;
1240:
1241:
1242: INSERT INTO ap_invoice_distributions_all (
1243: accounting_date
1244: ,accrual_posted_flag
1245: ,assets_addition_flag
1246: ,assets_tracking_flag
1375: ,'N'
1376: ,'CANDIDATE'); --bug7719929
1377:
1378: AP_DBI_PKG.Maintain_DBI_Summary
1379: ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1380: p_operation => 'I',
1381: p_key_value1 => new_invoice_id,
1382: p_key_value2 => l_invoice_distribution_id,
1383: p_calling_sequence => current_calling_sequence);
1425: ,'ap_do_withholding');
1426:
1427: -- End: Update original negative distribution with new invoice id:
1428:
1429: debug_info := 'Update ap_invoice_distributions';
1430:
1431: UPDATE ap_invoice_distributions_all
1432: SET awt_invoice_id = new_invoice_id
1433: WHERE current of c_awt_lines;
1427: -- End: Update original negative distribution with new invoice id:
1428:
1429: debug_info := 'Update ap_invoice_distributions';
1430:
1431: UPDATE ap_invoice_distributions_all
1432: SET awt_invoice_id = new_invoice_id
1433: WHERE current of c_awt_lines;
1434:
1435: END LOOP For_Each_Negative_Line;
2011: AND AI.invoice_id = ASI.invoice_id
2012: AND AI.org_id = asp.org_id
2013: AND NVL(ASI.ok_to_pay_flag,'Y') IN ( 'Y','F')
2014: AND NOT EXISTS (SELECT 'Manual AWT dists exist'
2015: FROM ap_invoice_distributions AID
2016: WHERE AID.invoice_id = ASI.invoice_id
2017: AND AID.line_type_lookup_code = 'AWT'
2018: AND AID.awt_flag = 'M')
2019: AND ((ASP.create_awt_dists_type ='PAYMENT' --Bug6660355
2024: OR
2025: ASP.create_awt_dists_type ='BOTH')
2026:
2027: AND EXISTS (SELECT 'At least one dist exists with AWT_GROUP_ID'
2028: FROM ap_invoice_distributions AID
2029: WHERE AID.invoice_id = ASI.invoice_id
2030: AND AID.awt_group_id IS NOT NULL)
2031: AND ASI.original_invoice_id IS NULL --Bug6660355
2032: AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
2112: AND payment_num = rec_ok_sel_invs.payment_num;
2113: --Bug6660355
2114: SELECT sum(nvl(aid.base_amount,aid.amount))
2115: INTO l_total_awt_amount
2116: FROM ap_invoice_distributions aid,ap_invoices ai
2117: WHERE aid.invoice_id = ai.invoice_id
2118: AND aid.invoice_id =rec_ok_sel_invs.invoice_id
2119: AND aid.line_type_lookup_code in ('AWT')
2120: AND aid.awt_origin_group_id = ai.awt_group_id;
2897:
2898: -- PL/SQL Main Block Constants AND Variables:
2899:
2900: awt_period ap_other_periods.period_name%TYPE;
2901: gl_period_name ap_invoice_distributions.period_name%TYPE;
2902: gl_awt_date DATE;
2903: DBG_Loc VARCHAR2(30) := 'Ap_Undo_Withholding';
2904: current_calling_sequence VARCHAR2(2000);
2905: debug_info VARCHAR2(100);
3015: , AID.other_invoice_id
3016: , AID.awt_invoice_payment_id
3017: , AID.invoice_distribution_id
3018: , AID.awt_related_id
3019: FROM ap_invoice_distributions AID,
3020: ap_tax_codes TC,
3021: ap_invoices AI
3022: WHERE AID.invoice_id = ParentId
3023: AND TC.tax_id (+) = AID.withholding_tax_code_id /* Bug 5382525 */
3132: , AID.other_invoice_id
3133: , AID.awt_invoice_payment_id
3134: , AID.invoice_distribution_id
3135: , awt_related_id
3136: FROM ap_invoice_distributions AID,
3137: ap_tax_codes TC
3138: WHERE AID.awt_invoice_payment_id = ParentId
3139: AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
3140: AND AID.invoice_line_number = NVL(P_Inv_Line_No,
3305:
3306: CURSOR c_curr_dist (InvId IN NUMBER,
3307: InvLineNum IN NUMBER) IS
3308: SELECT MAX(distribution_line_number)+1 curr_line_number
3309: FROM ap_invoice_distributions
3310: WHERE invoice_id = InvId
3311: AND invoice_line_number = InvLineNum;
3312:
3313: curr_line_number ap_invoice_distributions.distribution_line_number%TYPE;
3309: FROM ap_invoice_distributions
3310: WHERE invoice_id = InvId
3311: AND invoice_line_number = InvLineNum;
3312:
3313: curr_line_number ap_invoice_distributions.distribution_line_number%TYPE;
3314: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3315:
3316: BEGIN
3317: debug_info := 'OPEN CURSOR c_curr_dist';
3310: WHERE invoice_id = InvId
3311: AND invoice_line_number = InvLineNum;
3312:
3313: curr_line_number ap_invoice_distributions.distribution_line_number%TYPE;
3314: l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3315:
3316: BEGIN
3317: debug_info := 'OPEN CURSOR c_curr_dist';
3318: OPEN c_curr_dist (rec_awt_dists.invoice_id,
3360: -- From now there will be no difference between REVERSE DIST and CANCEL INVOICE
3361: -- except when REVERSE DIST IS passed match status flag of newly created
3362: -- awt lines will be N else it will be Y.
3363:
3364: debug_info := 'Insert reverse AWT line INTO ap_invoice_distributions';
3365:
3366: INSERT INTO ap_invoice_distributions
3367: (
3368: accounting_date
3362: -- awt lines will be N else it will be Y.
3363:
3364: debug_info := 'Insert reverse AWT line INTO ap_invoice_distributions';
3365:
3366: INSERT INTO ap_invoice_distributions
3367: (
3368: accounting_date
3369: ,accrual_posted_flag
3370: ,assets_addition_flag
3462: ,'N'
3463: ,''
3464: ,'N'
3465: ,'N'
3466: ,ap_invoice_distributions_s.nextval
3467: ,'N'
3468: ,rec_awt_dists.invoice_distribution_id
3469: ,rec_awt_dists.type_1099
3470: ,rec_awt_dists.income_tax_region
3474: );
3475:
3476: --Bug 4539462 DBI logging
3477: AP_DBI_PKG.Maintain_DBI_Summary
3478: ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
3479: p_operation => 'I',
3480: p_key_value1 => rec_awt_dists.invoice_id,
3481: p_key_value2 => l_Invoice_distribution_ID,
3482: p_calling_sequence => current_calling_sequence);
3691: P_create_dists => l_create_dists); --bug7685907
3692:
3693: END IF;
3694:
3695: UPDATE ap_invoice_distributions
3696: SET reversal_flag='Y'
3697: WHERE invoice_distribution_id = rec_awt_dists.invoice_distribution_id
3698: OR parent_reversal_id=rec_awt_dists.invoice_distribution_id;
3699:
3705:
3706: IF (P_Calling_Module IN ('CANCEL INVOICE','REVERSE DIST')) THEN
3707: CLOSE c_awt_dists_inv;
3708:
3709: UPDATE ap_invoice_distributions
3710: SET awt_withheld_amt = NULL
3711: WHERE invoice_id = P_parent_id
3712: AND NVL(awt_withheld_amt,0) <> 0;
3713: