[Home] [Help]
448: REFERENCE_KEY4,
449: REFERENCE_KEY5,
450: SHIP_TO_LOCATION_ID,
451: PAY_AWT_GROUP_ID --bug 7022001
452: FROM ap_invoice_lines_all
453: WHERE invoice_id = c_invoice_id
454: ORDER BY decode(line_type_lookup_code,'ITEM',1,2), line_number;
455:
456: --Bug 8346277
608: , REFERENCE_KEY4
609: , REFERENCE_KEY5
610: , SHIP_TO_LOCATION_ID
611: , PAY_AWT_GROUP_ID
612: FROM ap_invoice_lines_all
613: WHERE invoice_id = c_invoice_id
614: AND line_type_lookup_code = 'AWT'
615: AND line_source = 'MANUAL LINE ENTRY'
616: ORDER BY line_number;
1286: -- Bug 8346277 start
1287: l_manual_awt_exist:=0;
1288: SELECT COUNT(*)
1289: INTO l_manual_awt_exist
1290: FROM ap_invoice_lines_all ail
1291: WHERE ail.invoice_id = l_invoice_rec.invoice_id
1292: AND ail.line_type_lookup_code = 'AWT'
1293: AND ail.line_source = 'MANUAL LINE ENTRY';
1294:
2614:
2615: END Approve;
2616:
2617: /*bug6858309 - modified the function to look for
2618: ap_invoice_lines instead of ap_invoic_distributions
2619: as dists are not created for freshly created
2620: recurring invoice in R12*/
2621: FUNCTION validate_period (p_invoice_id IN NUMBER)
2622: RETURN BOOLEAN IS
2622: RETURN BOOLEAN IS
2623:
2624: CURSOR get_gl_date IS
2625: SELECT distinct accounting_date acc_date
2626: FROM ap_invoice_lines_all
2627: WHERE invoice_id = p_invoice_id
2628: and NVL(generate_dists,'N') <> 'D';
2629:
2630: CURSOR get_inv_source IS
2713:
2714: -- Select stmt added for bug 6684139
2715: SELECT count(*)
2716: into p_invoice_line_count
2717: from ap_invoice_lines
2718: where invoice_id = p_invoice_id;
2719:
2720: IF nvl(p_conc_flag, 'N') <> 'Y' THEN
2721:
2882: --bugfix:4745464
2883: BEGIN
2884: SELECT 'Y'
2885: INTO l_undistributed_line_exists
2886: FROM ap_invoice_lines_all L
2887: WHERE L.invoice_id = p_invoice_id
2888: AND L.amount <>
2889: (SELECT NVL(SUM(NVL(aid.amount,0)),0)
2890: FROM ap_invoice_distributions_all aid
3071: IS
3072:
3073: CURSOR Alloc_Rule_Cur IS
3074: SELECT ALOC.rule_type
3075: FROM ap_invoice_lines AIL,
3076: ap_allocation_rules ALOC
3077: WHERE AIL.invoice_id = p_inv_line_rec.invoice_id
3078: AND AIL.line_number = p_inv_line_rec.line_number
3079: AND AIL.invoice_id = ALOC.invoice_id
3236: IS
3237:
3238: CURSOR Alloc_Rule_Cur IS
3239: SELECT ALOC.rule_type
3240: FROM ap_invoice_lines AIL,
3241: ap_allocation_rules ALOC
3242: WHERE AIL.invoice_id = p_inv_line_rec.invoice_id
3243: AND AIL.line_number = p_inv_line_rec.line_number
3244: AND AIL.invoice_id = ALOC.invoice_id
3259:
3260: --Bugfix:4673607
3261: l_registration_api VARCHAR2(1000);
3262: l_registration_view VARCHAR2(1000);
3263: l_reference_key1 ap_invoice_lines_all.reference_key1%type;
3264: l_reference_key2 ap_invoice_lines_all.reference_key2%type;
3265: l_reference_key3 ap_invoice_lines_all.reference_key3%type;
3266: l_reference_key4 ap_invoice_lines_all.reference_key4%type;
3267: l_reference_key5 ap_invoice_lines_all.reference_key5%type;
3260: --Bugfix:4673607
3261: l_registration_api VARCHAR2(1000);
3262: l_registration_view VARCHAR2(1000);
3263: l_reference_key1 ap_invoice_lines_all.reference_key1%type;
3264: l_reference_key2 ap_invoice_lines_all.reference_key2%type;
3265: l_reference_key3 ap_invoice_lines_all.reference_key3%type;
3266: l_reference_key4 ap_invoice_lines_all.reference_key4%type;
3267: l_reference_key5 ap_invoice_lines_all.reference_key5%type;
3268: l_err varchar2(2000);
3261: l_registration_api VARCHAR2(1000);
3262: l_registration_view VARCHAR2(1000);
3263: l_reference_key1 ap_invoice_lines_all.reference_key1%type;
3264: l_reference_key2 ap_invoice_lines_all.reference_key2%type;
3265: l_reference_key3 ap_invoice_lines_all.reference_key3%type;
3266: l_reference_key4 ap_invoice_lines_all.reference_key4%type;
3267: l_reference_key5 ap_invoice_lines_all.reference_key5%type;
3268: l_err varchar2(2000);
3269:
3262: l_registration_view VARCHAR2(1000);
3263: l_reference_key1 ap_invoice_lines_all.reference_key1%type;
3264: l_reference_key2 ap_invoice_lines_all.reference_key2%type;
3265: l_reference_key3 ap_invoice_lines_all.reference_key3%type;
3266: l_reference_key4 ap_invoice_lines_all.reference_key4%type;
3267: l_reference_key5 ap_invoice_lines_all.reference_key5%type;
3268: l_err varchar2(2000);
3269:
3270: BEGIN
3263: l_reference_key1 ap_invoice_lines_all.reference_key1%type;
3264: l_reference_key2 ap_invoice_lines_all.reference_key2%type;
3265: l_reference_key3 ap_invoice_lines_all.reference_key3%type;
3266: l_reference_key4 ap_invoice_lines_all.reference_key4%type;
3267: l_reference_key5 ap_invoice_lines_all.reference_key5%type;
3268: l_err varchar2(2000);
3269:
3270: BEGIN
3271:
3332: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3333: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
3334: END IF;
3335:
3336: l_success := AP_INVOICE_LINES_PKG.Insert_From_Dist_Set(
3337: X_invoice_id => p_inv_line_rec.invoice_id,
3338: X_line_number => p_inv_line_rec.line_number,
3339: X_GL_Date => p_inv_line_rec.accounting_date,
3340: X_Period_Name => p_inv_line_rec.period_name,
4546: SELECT 'Distribution needs to be verified. '
4547: FROM DUAL
4548: WHERE EXISTS (
4549: SELECT 'Dist Total <> Invoice Line Amount'
4550: FROM ap_invoice_lines_all AIL, ap_invoice_distributions_all D
4551: -- WHERE AIL.invoice_id = D.invoice_id
4552: WHERE AIL.invoice_id = D.invoice_id(+)
4553: AND AIL.line_number = nvl(p_invoice_line_number, AIL.line_number) --bug6661773
4554: AND AIL.invoice_id = p_invoice_id
4860:
4861:
4862: CURSOR Line_Var_Cur IS
4863: SELECT 'Line Total <> Invoice Amount'
4864: FROM ap_invoice_lines_all AIL, ap_invoices_all A
4865: WHERE AIL.invoice_id = A.invoice_id
4866: AND AIL.invoice_id = p_invoice_id
4867: AND ((AIL.line_type_lookup_code <> 'TAX'
4868: and (AIL.line_type_lookup_code NOT IN ('AWT','PREPAY')
4930: /* End of Bug 7271262 */
4931:
4932: SELECT count(*)
4933: INTO l_line_count
4934: FROM ap_invoice_lines ail
4935: WHERE ail.invoice_id = p_invoice_id
4936: AND (ail.line_type_lookup_code NOT IN ('PREPAY','AWT')
4937: OR nvl(invoice_includes_prepay_flag,'N') = 'Y');
4938:
5029: p_exchange_rate IN NUMBER,
5030: p_need_to_round_flag IN VARCHAR2 DEFAULT 'N',
5031: p_calling_sequence IN VARCHAR2) IS
5032:
5033: l_rounded_line_num ap_invoice_lines.line_number%TYPE;
5034: l_rounded_amt NUMBER;
5035: l_round_amt_exist BOOLEAN := FALSE;
5036: l_key_value NUMBER;
5037:
5062: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
5063: END IF;
5064: -----------------------------------------------------
5065:
5066: UPDATE AP_INVOICE_LINES AIL
5067: SET AIL.base_amount = DECODE(p_base_currency_code, p_invoice_currency_code,
5068: NULL,
5069: ap_utilities_pkg.ap_round_currency(
5070: amount * p_exchange_rate,
5145: IF l_rounded_amt <> 0 THEN
5146: -- get the existing base amount for the selected line
5147: select base_amount, amount -- Bug 12686348: Added column amount
5148: INTO l_base_amt, l_amt -- Bug 12686348: Added column amount
5149: FROM AP_INVOICE_LINES
5150: WHERE invoice_id = p_invoice_id
5151: AND line_number = l_round_inv_line_numbers(i);
5152:
5153: -- get the calculated adjusted base amount and rounding amount
5158: p_next_line_rounding_amt => l_rounded_amt,
5159: p_amount => l_amt); -- Bug 12686348
5160:
5161: -- update the calculatd base amount, rounding amount
5162: UPDATE AP_INVOICE_LINES
5163: SET base_amount = l_base_amt,
5164: rounding_amt = ABS( NVL(l_modified_line_rounding_amt, 0) ),
5165: last_update_date = SYSDATE,
5166: last_updated_by = FND_GLOBAL.user_id,
5236: l_debug_context VARCHAR2(2000);
5237:
5238: l_base_amt NUMBER; --6892789
5239: l_modified_dist_rounding_amt NUMBER; --6892789
5240: l_round_dist_id_list AP_INVOICE_LINES_PKG.distribution_id_tab_type; --6892789
5241: l_amt NUMBER; -- Bug 12686348
5242:
5243: BEGIN
5244:
5290: adjust dist base amount upto zero and adjust the remaing amount in another
5291: distribution having next max amount */
5292:
5293: -- get the distributions which can be adjusted
5294: l_round_amt_exists := AP_INVOICE_LINES_PKG.round_base_amts(
5295: x_invoice_id => p_invoice_id,
5296: x_line_number => p_invoice_line_number,
5297: x_reporting_ledger_id => NULL,
5298: x_round_dist_id_list => l_round_dist_id_list,
5511:
5512: CURSOR Prepay_Var_Cur IS
5513: SELECT AI.invoice_amount +
5514: (SELECT NVL(SUM(nvl(ail1.amount,0)), 0) --Bug 13050996
5515: FROM ap_invoice_lines_all ail1
5516: WHERE ail1.invoice_id=ai.invoice_id
5517: AND ail1.line_type_lookup_code ='AWT')
5518: ,nvl(AI.amount_paid,0)
5519: , (0 - sum(nvl(AIL.amount,0))) -- taking the remaining amount to be paid on as part of bug 8339454
5516: WHERE ail1.invoice_id=ai.invoice_id
5517: AND ail1.line_type_lookup_code ='AWT')
5518: ,nvl(AI.amount_paid,0)
5519: , (0 - sum(nvl(AIL.amount,0))) -- taking the remaining amount to be paid on as part of bug 8339454
5520: FROM ap_invoices_all AI, ap_invoice_lines_all AIL
5521: WHERE AI.invoice_id = p_invoice_id
5522: AND AIL.invoice_id = AI.invoice_id
5523: AND AIL.invoice_includes_prepay_flag = 'N' --Bug 13050996: Uncommented out this line
5524: AND AIL.line_type_lookup_code IN ('PREPAY', 'TAX')
5795: -- in the invoice
5796: CURSOR c_non_awt_dists_segs is
5797: SELECT ap_utilities_pkg.get_auto_offsets_segments(
5798: aid.dist_code_combination_id)
5799: FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
5800: WHERE ail.invoice_id = p_invoice_id
5801: AND ail.invoice_id = aid.invoice_id
5802: AND ail.line_number = aid.invoice_line_number
5803: AND ((aid.line_type_lookup_code not in ('AWT','PREPAY')
7707: aid.last_updated_by user_id,
7708: aid.last_update_login last_update_login,
7709: l_curr_calling_sequence calling_sequence
7710: FROM ap_invoice_distributions aid,
7711: ap_invoice_lines ail
7712: WHERE aid.invoice_id = p_invoice_id
7713: AND ail.invoice_id = aid.invoice_id
7714: AND ail.line_number = aid.invoice_line_number
7715: --bugfix:5609186
7778: l_debug_info := 'Check if Prepayment Application Exists';
7779: ------------------------------------------------------------
7780: BEGIN
7781: --Bug8340784
7782: -- Query table ap_invoice_distributions instead of ap_invoice_lines
7783: -- This will include recoupment amount applied as prepayment
7784: SELECT 'Y'
7785: INTO l_prepayment_app_exists
7786: FROM ap_invoice_distributions aid
7845:
7846: IF (p_exclude_freight_from_disc = 'Y') THEN
7847: SELECT NVL(SUM(nvl(ail.amount,0)),0)
7848: INTO l_total_freight_amount
7849: FROM ap_invoice_lines ail
7850: WHERE ail.invoice_id = p_invoice_id
7851: AND ail.line_type_lookup_code = 'FREIGHT'
7852: AND nvl(ail.discarded_flag,'N') <> 'Y';
7853: ELSE
7938: --bugfix:5638734
7939: -- Get the exculusive tax amount for the prepay appln line.
7940: SELECT sum(aid.amount)
7941: INTO l_prepay_excl_tax_amt
7942: FROM ap_invoice_lines_all ail,ap_invoice_distributions_all aid
7943: WHERE ail.line_type_lookup_code='TAX'
7944: AND ail.invoice_id=p_invoice_id
7945: AND aid.invoice_id=ail.invoice_id
7946: AND aid.invoice_line_number=ail.line_number
9108: -------------------------------------------
9109:
9110: IF (nvl(p_generate_permanent,'N') = 'Y') then
9111: BEGIN
9112: UPDATE AP_INVOICE_LINES
9113: SET GENERATE_DISTS = 'D'
9114: WHERE invoice_id = p_invoice_line_rec.invoice_id
9115: AND line_number = p_invoice_line_rec.line_number;
9116: EXCEPTION
9492: AND ai.validation_request_id IS NULL
9493: /*bug 7029877 Invoice saved but not submitted*/
9494: AND ai.approval_ready_flag <>'S'
9495: AND EXISTS (select ail.invoice_id
9496: from ap_invoice_lines_all ail
9497: where ail.invoice_id = ai.invoice_id) ;
9498:
9499: -- bug 6351170 -Added below if condition - Return if there are no invoices to process
9500: IF sql%rowcount = 0 THEN
9655: -- Bug 11830074 start
9656: l_manual_awt_exist:=0;
9657: SELECT COUNT(*)
9658: INTO l_manual_awt_exist
9659: FROM ap_invoice_lines_all ail
9660: WHERE ail.invoice_id = l_invoice_rec.invoice_id
9661: AND ail.line_type_lookup_code = 'AWT'
9662: AND ail.line_source = 'MANUAL LINE ENTRY';
9663:
9849: AND USER_RELEASEABLE_FLAG = ''N''))) /* Bug 14579876 */
9850: AND H.RELEASE_LOOKUP_CODE IS NULL
9851: AND EXISTS
9852: ( SELECT ''Lines''
9853: FROM AP_INVOICE_LINES_ALL L2
9854: WHERE L2.INVOICE_ID = H.INVOICE_ID ) --8580790,9112369
9855: UNION ALL
9856: SELECT 1
9857: FROM AP_INVOICE_LINES_ALL AIL
9853: FROM AP_INVOICE_LINES_ALL L2
9854: WHERE L2.INVOICE_ID = H.INVOICE_ID ) --8580790,9112369
9855: UNION ALL
9856: SELECT 1
9857: FROM AP_INVOICE_LINES_ALL AIL
9858: WHERE AIL.INVOICE_ID = AI.INVOICE_ID
9859: /* Bug 9777752 AND AI.CANCELLED_DATE IS NULL */
9860: AND NVL(AIL.DISCARDED_FLAG, ''N'') <> ''Y''
9861: AND NVL(AIL.CANCELLED_FLAG, ''N'') <> ''Y''
10331: -- Bug 11830074 start
10332: l_manual_awt_exist:=0;
10333: SELECT COUNT(*)
10334: INTO l_manual_awt_exist
10335: FROM ap_invoice_lines_all ail
10336: WHERE ail.invoice_id = l_selected_invoices_cursor(i).invoice_id
10337: AND ail.line_type_lookup_code = 'AWT'
10338: AND ail.line_source = 'MANUAL LINE ENTRY';
10339:
10759: -- Bug 11830074 start
10760: l_manual_awt_exist:=0;
10761: SELECT COUNT(*)
10762: INTO l_manual_awt_exist
10763: FROM ap_invoice_lines_all ail
10764: WHERE ail.invoice_id = l_selected_invoices_cursor(i).invoice_id
10765: AND ail.line_type_lookup_code = 'AWT'
10766: AND ail.line_source = 'MANUAL LINE ENTRY';
10767:
10999: -- Bug 11830074 start
11000: l_manual_awt_exist:=0;
11001: SELECT COUNT(*)
11002: INTO l_manual_awt_exist
11003: FROM ap_invoice_lines_all ail
11004: WHERE ail.invoice_id = AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id
11005: AND ail.line_type_lookup_code = 'AWT'
11006: AND ail.line_source = 'MANUAL LINE ENTRY';
11007:
11582: BEGIN
11583:
11584: SELECT 'Y'
11585: INTO l_not_exist_nond_line
11586: FROM ap_invoice_lines_all ail
11587: WHERE ail.invoice_id = p_invoice_rec.invoice_id
11588: AND nvl(ail.generate_dists, 'N') <> 'D'
11589: AND rownum < 2;
11590:
11609: WHERE aha.invoice_id = p_invoice_rec.invoice_id
11610: AND aha.hold_lookup_code = 'CANNOT EXECUTE ALLOCATION'
11611: AND aha.release_lookup_code IS NULL
11612: AND NOT EXISTS ( SELECT 1
11613: FROM ap_invoice_lines_all ail, ap_allocation_rules ALR
11614: WHERE ail.invoice_id = p_invoice_rec.invoice_id
11615: AND ail.line_type_lookup_code in ('FREIGHT','MISCELLANEOUS')
11616: AND nvl(ail.discarded_flag,'N') ='N'
11617: AND ALR.invoice_id = AIL.invoice_id
11689: ----------------------------------------------------------------
11690:
11691: SELECT nvl(prorate_across_all_items,'N')
11692: INTO l_prorate_across_all_items
11693: FROM ap_invoice_lines_all
11694: WHERE invoice_id = t_inv_lines_table(i).invoice_id
11695: AND line_number = t_inv_lines_table(i).line_number;
11696:
11697: --Bug10052153: Added below code to check if PRORATION rule is defined.
11818: (P_Invoice_Id => t_inv_lines_table(i).invoice_id,
11819: P_Invoice_Line_Number => t_inv_lines_table(i).line_number);
11820:
11821: IF l_recouped_amount <> 0 THEN
11822: IF (ap_invoice_lines_utility_pkg.get_approval_status
11823: ( p_invoice_id => t_inv_lines_table(i).invoice_id
11824: ,p_line_number => t_inv_lines_table(i).line_number) = 'NEVER APPROVED') THEN
11825:
11826: ----------------------------------------------------------------
12252: BEGIN
12253:
12254: SELECT 'Y'
12255: INTO l_not_exist_nond_line
12256: FROM ap_invoice_lines_all ail
12257: WHERE ail.invoice_id = p_invoice_rec.invoice_id
12258: AND nvl(ail.generate_dists, 'N') <> 'D'
12259: AND rownum < 2;
12260:
12728: FROM PO_DISTRIBUTIONS_ALL POD
12729: WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
12730: WHEN (AID. Global_Attribute1 IS NULL) THEN
12731: (SELECT Global_Attribute1
12732: FROM AP_INVOICE_LINES AIL
12733: WHERE AIL.INVOICE_ID = AID.INVOICE_ID
12734: AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
12735: AND AIL. Global_Attribute1 IS NOT NULL)
12736: END),
12741: FROM PO_DISTRIBUTIONS_ALL POD
12742: WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
12743: WHEN (AID. Global_Attribute2 IS NULL) THEN
12744: (SELECT Global_Attribute2
12745: FROM AP_INVOICE_LINES AIL
12746: WHERE AIL.INVOICE_ID = AID.INVOICE_ID
12747: AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
12748: AND AIL.Global_Attribute2 IS NOT NULL)
12749: END),
12754: FROM PO_DISTRIBUTIONS_ALL POD
12755: WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
12756: WHEN (AID. Global_Attribute3 IS NULL) THEN
12757: (SELECT Global_Attribute3
12758: FROM AP_INVOICE_LINES AIL
12759: WHERE AIL.INVOICE_ID = AID.INVOICE_ID
12760: AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
12761: AND AIL.Global_Attribute3 IS NOT NULL)
12762: END),
12768: FROM PO_DISTRIBUTIONS_ALL POD
12769: WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
12770: WHEN (AID. Global_Attribute_Category IS NULL) THEN
12771: (SELECT Global_Attribute_Category
12772: FROM AP_INVOICE_LINES AIL
12773: WHERE AIL.INVOICE_ID = AID.INVOICE_ID
12774: AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
12775: AND AIL.Global_Attribute_Category IS NOT NULL)
12776: END)