[Home] [Help]
303: )
304: where (aida.invoice_id = cn_invoice_id or jatd.invoice_id = cn_invoice_id)
305: and not exists
306: (select 1
307: from ap_invoice_lines_all aila
308: where aida.invoice_id = aila.invoice_id
309: and aida.invoice_line_number = aila.line_number
310: and NVL(aila.DISCARDED_FLAG,'N') = 'Y'
311: and aila.invoice_id = cn_invoice_id
2260: --Get Section Type, Section Code, Tax type, Tax Rate from JAI_AP_TDS_INV_TAXES or Description of Invoice Distribution
2261:
2262: \* cursor get_line_info(cn_invoice_distribution_id number)
2263: is
2264: select line. from ap_invoice_lines_all line, ap_invoice_distributions_all dist
2265: where line.invoice_id = dist.invoice_id
2266: and line.line_number = dist.invoice_line_number
2267: and dist.invoice_distribution_id = *\
2268:
2346:
2347: rec_trxs.invoice_id, --INVOICE_ID ,
2348: rec_trxs.invoice_num, --get from ap_invoices_all --INVOICE_NUMBER ,
2349: nvl(rec_dist_info.parent_invoice_id,rec_trxs.invoice_id),
2350: --1, --get from ap_invoice_lines_all --INVOICE_LINE_ID ,
2351: --rec_dist_info.invoice_line_number, --get from ap_invoice_lines_all -- LINE_NUMBER ,
2352: --rec_tds_taxes.invoice_distribution_id, --INVOICE_DISTRIBUTION_ID ,
2353: --rec_dist_info.distribution_line_number,--get from ap_invoice_distributions_all.,--DISTRIBUTION_LINE_NUMBER ,
2354: rec_dist_info.accounting_date, --get from ap_invoice_distributions_all.,--ACCOUNTING_DATE ,
2347: rec_trxs.invoice_id, --INVOICE_ID ,
2348: rec_trxs.invoice_num, --get from ap_invoices_all --INVOICE_NUMBER ,
2349: nvl(rec_dist_info.parent_invoice_id,rec_trxs.invoice_id),
2350: --1, --get from ap_invoice_lines_all --INVOICE_LINE_ID ,
2351: --rec_dist_info.invoice_line_number, --get from ap_invoice_lines_all -- LINE_NUMBER ,
2352: --rec_tds_taxes.invoice_distribution_id, --INVOICE_DISTRIBUTION_ID ,
2353: --rec_dist_info.distribution_line_number,--get from ap_invoice_distributions_all.,--DISTRIBUTION_LINE_NUMBER ,
2354: rec_dist_info.accounting_date, --get from ap_invoice_distributions_all.,--ACCOUNTING_DATE ,
2355: rec_dist_info.org_id, --get from ap_invoice_distributions_all.,--ORG_ID ,
3100: -----------------------------------------------------------------------------------------------------
3101: --When get count > 0, means AP invoice containing matched invoice lines
3102: cursor c_check_is_matched is
3103: select count(line_number)
3104: from ap_invoice_lines_all
3105: where invoice_id = p_invoice_id
3106: and match_type <> 'NOT_MATCHED';
3107:
3108: cursor c_check_effective_date(cp_legal_entity_id JAI_AP_TDS_THHOLD_ACCOUNT.LEGAL_ENTITY_ID%TYPE
9399: FROM ap_invoice_distributions_all
9400: WHERE invoice_id = cp_invoice_id
9401: AND distribution_line_number = 1;
9402:
9403: /*Get Accounting Date from AP_INVOICE_LINES_ALL.
9404: This is fail safe if Distributions does not have accounting date*/
9405: CURSOR c_get_lines_acct_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
9406: SELECT accounting_date
9407: ,creation_date --Added by Chong for Bug#13802244 2012/09/17
9404: This is fail safe if Distributions does not have accounting date*/
9405: CURSOR c_get_lines_acct_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
9406: SELECT accounting_date
9407: ,creation_date --Added by Chong for Bug#13802244 2012/09/17
9408: FROM ap_invoice_lines_all
9409: WHERE invoice_id = cp_invoice_id
9410: AND line_number = 1;
9411:
9412: --Added by Chong for Bug#13802244 2012/09/17 Start
9634: FETCH get_dist_gl_date
9635: INTO ld_accounting_date, ld_creation_date;
9636: CLOSE get_dist_gl_date;
9637:
9638: /*Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
9639: IF (ld_accounting_date IS NULL) THEN
9640: OPEN c_get_lines_acct_date(cur_rec.invoice_id);
9641: FETCH c_get_lines_acct_date
9642: INTO ld_accounting_date, ld_creation_date;
9787: FETCH get_dist_gl_date
9788: INTO ld_accounting_date, ld_creation_date; --Added creation_date by Chong for Bug#13802244 2012/09/17
9789: CLOSE get_dist_gl_date;
9790:
9791: /*Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
9792: IF (ld_accounting_date IS NULL) THEN
9793: OPEN c_get_lines_acct_date(r_ap_invoices_all_au.invoice_id);
9794: FETCH c_get_lines_acct_date
9795: INTO ld_accounting_date, ld_creation_date; --Added creation_date by Chong for Bug#13802244 2012/09/17
9923: FETCH get_dist_gl_date
9924: INTO ld_accounting_date, ld_creation_date; --Added creation_date by Chong for Bug#13802244 2012/09/17
9925: CLOSE get_dist_gl_date;
9926:
9927: /*Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
9928: if (ld_accounting_date is NULL) then
9929: OPEN c_get_lines_acct_date(r_ap_invoices_all_cm.invoice_id);
9930: FETCH c_get_lines_acct_date
9931: INTO ld_accounting_date, ld_creation_date; --Added creation_date by Chong for Bug#13802244 2012/09/17
10137: )
10138: ;
10139: --Get surcharge tax amount for surcharge RTN invoice
10140: SELECT SUM(aila.AMOUNT) INTO ln_tax_amount
10141: FROM AP_INVOICE_LINES_ALL aila
10142: WHERE INVOICE_ID IN (
10143: SELECT NVL(jattt.invoice_to_tds_authority_id, jattt.invoice_to_vendor_id)
10144: FROM JAI_AP_TDS_THHOLD_TRXS jattt
10145: ,AP_INVOICES_ALL aia