[Home] [Help]
286: a.distribution_line_number, a.prepay_distribution_id,
287: a.amount, a.reversal_flag, a.parent_reversal_id, a.accounting_event_id, a.posted_flag, a.org_id,
288: a.accounting_date, b.invoice_amount, b.amount_paid, b.cancelled_date, b.invoice_type_lookup_code invoice_type,
289: a.creation_date, a.po_distribution_id
290: FROM ap_invoice_distributions_all a, ap_invoices_all b
291: WHERE a.invoice_id = b.invoice_id
292: AND invoice_distribution_id = cp_invoice_distribution_id;
293:
294: CURSOR c_invoice_payment(cp_invoice_payment_id IN NUMBER) IS
403: p_po_distribution_id IN NUMBER,
404: p_rcv_transaction_id IN NUMBER
405: ) RETURN NUMBER IS
406:
407: CURSOR c_parent_distribution_id( p_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS
408: SELECT invoice_distribution_id
409: FROM ap_invoice_distributions_all
410: WHERE invoice_id = p_invoice_id
411: AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
405: ) RETURN NUMBER IS
406:
407: CURSOR c_parent_distribution_id( p_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS
408: SELECT invoice_distribution_id
409: FROM ap_invoice_distributions_all
410: WHERE invoice_id = p_invoice_id
411: AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
412: AND po_distribution_id = p_po_distribution_id
413: AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
412: AND po_distribution_id = p_po_distribution_id
413: AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
414: AND parent_reversal_id IS NULL; -- CHK
415:
416: ln_item_distribution_id AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
417:
418: BEGIN
419:
420: OPEN c_parent_distribution_id('ITEM');
546: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
547:
548: CURSOR get_dist_line_cur IS
549: SELECT 'STANDALONE' match_type, aida.invoice_distribution_id, jcdt.tax_amt, jcdt.tax_type
550: FROM ap_invoice_distributions_all aida,
551: jai_cmn_document_taxes jcdt
552: WHERE aida.invoice_id = pn_invoice_id
553: AND aida.line_type_lookup_code = 'MISCELLANEOUS'
554: AND jcdt.source_doc_id = pn_invoice_id
661:
662: IF pv_action = 'REVERSAL' THEN
663: lv_source_trx_type := 'REVERSAL_ACCOUNTING';
664: lv_called_from := 'REVERSAL_ACCOUNTING' ;
665: lv_source_table := 'AP_INVOICE_DISTRIBUTIONS_ALL';
666: lv_repository_name := 'invoice_distribution_id';
667: ELSE --'CLAIM'
668: lv_source_trx_type := 'CLAIM_ACCOUNTING';
669: lv_called_from := 'CLAIM_ACCOUNTING' ;
974: AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
975: AND jrtr.source = 'AP_CLAIM'
976: AND jrtr.source_document_id = aipa.invoice_payment_id)
977: AND EXISTS (SELECT 1
978: FROM ap_invoice_distributions_all aida
979: WHERE aida.invoice_id = pn_invoice_id
980: AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
981: aida.accounting_date, 'AP') = 'Y')
982: UNION ALL
1002: AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
1003: AND jrtr.source = 'AP_CLAIM'
1004: AND jrtr.source_document_id = aipa.invoice_payment_id)
1005: AND EXISTS (SELECT 1
1006: FROM ap_invoice_distributions_all aida
1007: WHERE aida.invoice_id = pn_invoice_id
1008: AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
1009: aida.accounting_date, 'AP') = 'Y')
1010: ORDER BY 2;
1079: -- Added for bug#7191302 by Xiao, begin.
1080: /* The cursor c_total_inv_amount is to fetch the total invoice amount without considering
1081: any prepayment applications. Refer to bug for more details*/
1082: CURSOR c_total_inv_amount(cp_invoice_id NUMBER) IS
1083: select sum(amount) from ap_invoice_distributions_all a where invoice_id=cp_invoice_id
1084: and prepay_distribution_id is null;
1085:
1086: -- Added for bug#7191302 by Xiao, end.
1087: -- Added the following cursor for Bug 7522584
1847: ,aid.accounting_date --Added by zhiwei for POT change Bug#13023443 on 20110930
1848: FROM xla_ae_headers xah ,
1849: xla_transaction_entities xte,
1850: ap_invoices_all ai,
1851: ap_invoice_distributions_all aid
1852: WHERE xah.je_category_name = cp_ae_category
1853: AND xah.ledger_id = cp_sob_id
1854: AND xah.application_id =200
1855: and xah.entity_id = xte.entity_id
1907: parent_reversal_id ,
1908: org_id ,
1909: invoice_line_number ,/*Bug 12805386*/
1910: po_distribution_id /*Bug 12805386*/
1911: FROM ap_invoice_distributions_all
1912: WHERE org_id = p_org_id
1913: AND line_type_lookup_code = jai_constants.prepay_line
1914: AND invoice_id IN ( SELECT invoice_id
1915: FROM ap_invoice_distributions_all
1911: FROM ap_invoice_distributions_all
1912: WHERE org_id = p_org_id
1913: AND line_type_lookup_code = jai_constants.prepay_line
1914: AND invoice_id IN ( SELECT invoice_id
1915: FROM ap_invoice_distributions_all
1916: WHERE po_distribution_id IN ( SELECT pda.po_distribution_id
1917: FROM po_line_locations_all pll,
1918: po_distributions_all pda,
1919: jai_po_line_locations jpll
1964: SELECT a.invoice_id, a.invoice_distribution_id, a.distribution_line_number, a.dist_match_type,
1965: a.invoicE_line_number, /* INVOICE LINES UPTAKE */
1966: a.parent_reversal_id, a.reversal_flag, a.rcv_transaction_id, a.po_distribution_id
1967: , a.accounting_date ----Add by Xiao for POT change, reg bug#12533434 on 29-May-2011
1968: FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
1969: WHERE a.invoice_id = cp_invoice_id
1970: /*Added the below or condition for bug#11666653*/
1971: AND (
1972: (a.line_type_lookup_code <> 'PREPAY' And exists (select '1' from jai_rcv_tp_invoices where invoice_id=a.invoice_id))
1998: SELECT 1 chk, match_tax.tax_id, tax.tax_rate, abs(ap_dist.amount), match_tax.parent_invoice_distribution_id,tax.tax_type,
1999: ap_line.line_number invoice_line_number,
2000: 100 recoverable_ptg, match_tax.base_amount
2001: FROM AP_INVOICE_LINES_ALL ap_line,
2002: AP_INVOICE_DISTRIBUTIONS_ALL ap_dist, JAI_AP_MATCH_INV_TAXES match_tax,
2003: JAI_CMN_TAXES_ALL tax, JAI_RGM_REGISTRATIONS rgm
2004: WHERE ap_dist.invoice_id = cp_invoice_id
2005: AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2006: AND ap_line.invoice_id = ap_dist.invoice_id
2025: and not the Line Number of the Service Tax*/
2026: a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
2027: nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
2028: FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
2029: AP_INVOICE_DISTRIBUTIONS_ALL d
2030: WHERE a.source_doc_id = cp_invoice_id
2031: AND d.invoice_distribution_id = cp_inv_distribution_id
2032: AND d.invoice_id = a.source_doc_id
2033: AND d.invoice_line_number = a.source_doc_line_id
2045: a.source_doc_parent_line_no invoice_line_number, /* INVOICE LINES UPTAKE */
2046: 100 recoverable_ptg,
2047: ap_dist.base_amount
2048: FROM
2049: AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
2050: JAI_CMN_TAXES_ALL b
2051: WHERE ap_dist.invoice_id = cp_invoice_id
2052: AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2053: AND ap_line.invoice_id = ap_dist.invoice_id
2090: SELECT 2 chk, a.tax_id, b.tax_rate, a.tax_amt,null, b.tax_type,
2091: a.source_doc_parent_line_no invoice_line_number,
2092: nvl(b.mod_cr_percentage,0) recoverable_ptg, d.base_amount
2093: FROM JAI_CMN_DOCUMENT_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c,
2094: AP_INVOICE_DISTRIBUTIONS_ALL d
2095: WHERE a.source_doc_id = cp_invoice_id
2096: AND d.invoice_distribution_id = cp_inv_distribution_id
2097: AND d.invoice_id = a.source_doc_id
2098: AND d.invoice_line_number = a.source_doc_line_id
2110: a.source_doc_parent_line_no invoice_line_number,
2111: 100 recoverable_ptg,
2112: ap_dist.base_amount
2113: FROM
2114: AP_INVOICE_LINES_ALL ap_line,AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,JAI_CMN_DOCUMENT_TAXES a, JAI_RGM_REGISTRATIONS c,
2115: JAI_CMN_TAXES_ALL b
2116: WHERE ap_dist.invoice_id = cp_invoice_id
2117: AND ap_dist.invoice_distribution_id = cp_inv_distribution_id
2118: AND ap_line.invoice_id = ap_dist.invoice_id
2162: ORDER BY invoice_payment_id;
2163:
2164: CURSOR c_previous_prepayments(cp_invoice_id IN NUMBER, cp_start_date IN DATE) IS
2165: SELECT invoice_distribution_id, reversal_flag, parent_reversal_id, amount, org_id
2166: FROM ap_invoice_distributions_all
2167: WHERE invoice_id = cp_invoice_id
2168: AND creation_date < cp_start_date
2169: ORDER BY invoice_distribution_id;
2170:
2194: ainvd.accounting_date -- Xiao for POT change, reg bug#12533434
2195: FROM
2196: ap_invoice_payments_all apinvp,
2197: ap_checks_all apc ,
2198: ap_invoice_distributions_all ainvd ,
2199: jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
2200: WHERE
2201: apinvp.org_id = p_org_id
2202: AND apinvp.check_id = apc.check_id
2204: AND apinvp.accounting_date/*Commented by nprashar for bug #6636517
2205: v_today*/ BETWEEN cp_start_date AND cp_till_date
2206: AND ainvd.invoice_id IN
2207: ( SELECT invoice_id
2208: FROM ap_invoice_distributions_all
2209: WHERE org_id = p_org_id
2210: AND po_distribution_id in
2211: (SELECT pda.po_distribution_id
2212: FROM po_line_locations_all pll,
2273: r_parent_dist c_invoice_distribution%ROWTYPE;
2274:
2275: r_tax_dist_dtl c_tax_dist_dtl%ROWTYPE;
2276:
2277: ln_item_line_id NUMBER(15); -- Incase of AP -> AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
2278: ln_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
2279: ln_reference_id JAI_RGM_TRX_REFS.reference_id%TYPE;
2280: ln_parent_reference_id JAI_RGM_TRX_REFS.parent_reference_id%TYPE;
2281: ln_taxable_basis NUMBER;
2338: ld_st_accrual_date DATE;
2339:
2340: CURSOR get_accounting_date(pn_invoice_distribution_id NUMBER) IS
2341: SELECT accounting_date, po_distribution_id, invoice_id, invoice_line_number /*Bug 12805386*/
2342: FROM ap_invoice_distributions_all
2343:
2344: WHERE invoice_distribution_id = pn_invoice_distribution_id;
2345:
2346: ld_gl_date DATE;
4444: , ai.cancelled_date
4445: FROM xla_ae_headers xah
4446: , xla_transaction_entities xte
4447: , ap_invoices_all ai
4448: , ap_invoice_distributions_all aid
4449: WHERE xah.je_category_name = cp_ae_category
4450: AND xah.ledger_id = cp_sob_id
4451: AND xah.application_id = 200
4452: AND xah.entity_id = xte.entity_id
4470: AND NOT EXISTS( SELECT 1
4471: FROM jai_rgm_trx_records rgtr
4472: WHERE rgtr.source = 'AP'
4473: AND rgtr.organization_id = p_organization_id
4474: AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4475: AND rgtr.source_document_id = aid.invoice_distribution_id)
4476: AND EXISTS(SELECT 1
4477: FROM jai_rgm_trx_refs rgtf
4478: WHERE rgtf.source = 'AP'
4508: , ai.invoice_date
4509: , aid.accounting_date
4510: , ai.cancelled_date
4511: FROM ap_invoices_all ai
4512: , ap_invoice_distributions_all aid
4513: WHERE aid.invoice_id = ai.invoice_id
4514: AND aid.set_of_books_id = cp_sob_id
4515: AND ai.invoice_type_lookup_code IN ('STANDARD', 'DEBIT', 'CREDIT')
4516: AND ai.cancelled_date IS NOT NULL
4523: AND EXISTS(SELECT 1
4524: FROM jai_rgm_trx_records rgtr
4525: WHERE rgtr.source = 'AP'
4526: AND rgtr.organization_id = p_organization_id
4527: AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4528: AND rgtr.source_document_id = aid.parent_reversal_id )
4529: AND NOT EXISTS(SELECT 1
4530: FROM jai_rgm_trx_records rgtr
4531: WHERE rgtr.source = 'AP'
4529: AND NOT EXISTS(SELECT 1
4530: FROM jai_rgm_trx_records rgtr
4531: WHERE rgtr.source = 'AP'
4532: AND rgtr.organization_id = p_organization_id
4533: AND rgtr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4534: AND rgtr.source_document_id = aid.invoice_distribution_id )
4535: AND EXISTS(SELECT 1
4536: FROM jai_rgm_trx_refs rgtf
4537: WHERE rgtf.source = 'AP'
4538: AND rgtf.invoice_id = aid.invoice_id
4539: AND rgtf.organization_id = p_organization_id--Added by zhiwei for POT Bug#12970828 on 20110915
4540: AND rgtf.party_type = 'V');
4541:
4542: lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AP_INVOICE_DISTRIBUTIONS_ALL';
4543: lv_called_from CONSTANT varchar2(100) := 'jai_cmn_rgm_processing_pkg.populate_repository';
4544: ln_repository_id NUMBER;
4545: ln_discounted_amt NUMBER := 0;
4546: ln_organization_id NUMBER := p_organization_id;
4620:
4621:
4622: CURSOR check_same_period_cur(pn_parent_reversal_id NUMBER) IS
4623: SELECT 'Y'
4624: FROM ap_invoice_distributions_all aid1,
4625: ap_invoice_distributions_all aid2
4626: WHERE aid1.parent_reversal_id = pn_parent_reversal_id
4627: AND aid2.invoice_distribution_id = pn_parent_reversal_id
4628: AND aid1.period_name = aid2.period_name;
4621:
4622: CURSOR check_same_period_cur(pn_parent_reversal_id NUMBER) IS
4623: SELECT 'Y'
4624: FROM ap_invoice_distributions_all aid1,
4625: ap_invoice_distributions_all aid2
4626: WHERE aid1.parent_reversal_id = pn_parent_reversal_id
4627: AND aid2.invoice_distribution_id = pn_parent_reversal_id
4628: AND aid1.period_name = aid2.period_name;
4629:
4673: SELECT jcta.tax_type, jcdt.tax_amt, aida.invoice_distribution_id,
4674: aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4675: FROM jai_cmn_document_taxes jcdt
4676: , jai_cmn_taxes_all jcta
4677: , ap_invoice_distributions_all aida
4678: , ap_invoices_all aia
4679: WHERE jcdt.source_doc_id = pn_invoice_id
4680: AND jcdt.source_doc_parent_line_no = pn_line_number
4681: AND aia.invoice_id = pn_invoice_id
4687: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4688: AND NOT EXISTS (SELECT 1
4689: FROM jai_rgm_trx_records
4690: WHERE SOURCE = 'AP_REVERSAL'
4691: AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4692: AND source_document_id = aida.invoice_distribution_id)
4693: AND EXISTS(SELECT 1
4694: FROM jai_rgm_trx_refs rgtf
4695: WHERE rgtf.source = 'AP'
4699: SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
4700: aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4701: FROM jai_ap_match_inv_taxes jamt
4702: , jai_cmn_taxes_all jcta
4703: , ap_invoice_distributions_all aida
4704: , ap_invoices_all aia
4705: WHERE jamt.invoice_id = pn_invoice_id
4706: --AND jamt.invoice_line_number <> pn_line_number
4707: AND jamt.parent_invoice_line_number = pn_line_number
4714: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4715: AND NOT EXISTS (SELECT 1
4716: FROM jai_rgm_trx_records
4717: WHERE SOURCE = 'AP_REVERSAL'
4718: AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4719: AND source_document_id = aida.invoice_distribution_id)
4720: AND EXISTS(SELECT 1
4721: FROM jai_rgm_trx_refs rgtf
4722: WHERE rgtf.source = 'AP'
4728: aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4729: FROM jai_cmn_document_taxes jcdt
4730: , jai_cmn_taxes_all jcta
4731: , ap_invoices_all aia
4732: , ap_invoice_distributions_all aida
4733: WHERE jcdt.source_doc_id = pn_invoice_id
4734: AND jcdt.source_doc_parent_line_no = pn_line_number
4735: AND aia.invoice_id = pn_invoice_id
4736: AND aida.invoice_id = pn_invoice_id
4741: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4742: AND EXISTS (SELECT 1
4743: FROM jai_rgm_trx_records
4744: WHERE SOURCE = 'AP_REVERSAL'
4745: AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4746: AND source_document_id = aida.invoice_distribution_id)
4747: AND NOT EXISTS (SELECT 1
4748: FROM jai_rgm_trx_records jrtr,
4749: jai_rgm_trx_refs jrtf --Added by Qiong for bug13439861
4761: SELECT jcta.tax_type, jamt.tax_amount tax_amt, aida.invoice_distribution_id,
4762: aida.accounting_date, aia.invoice_date, aida.po_distribution_id
4763: FROM jai_ap_match_inv_taxes jamt
4764: , jai_cmn_taxes_all jcta
4765: , ap_invoice_distributions_all aida
4766: , ap_invoices_all aia
4767: WHERE jamt.invoice_id = pn_invoice_id
4768: --AND jamt.invoice_line_number <> pn_line_number
4769: AND jamt.parent_invoice_line_number = pn_line_number
4776: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
4777: AND EXISTS (SELECT 1
4778: FROM jai_rgm_trx_records
4779: WHERE SOURCE = 'AP_REVERSAL'
4780: AND source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
4781: AND source_document_id = aida.invoice_distribution_id)
4782: AND NOT EXISTS (SELECT 1
4783: FROM jai_rgm_trx_records jrtr,
4784: jai_rgm_trx_refs jrtf--Added by Qiong for bug13439861
4836: -----------------------------------------
4837: --Added by Qiong for bug13439861 end
4838: )
4839: AND EXISTS (SELECT 1
4840: FROM ap_invoice_distributions_all aida
4841: WHERE aida.invoice_id = pn_invoice_id
4842: AND aida.invoice_line_number = pn_line_number
4843: AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aipa.accounting_date,
4844: aida.accounting_date, 'AP') = 'Y')
4875: -----------------------------------------
4876: --Added by Qiong for bug13439861 end
4877: )
4878: AND EXISTS (SELECT 1
4879: FROM ap_invoice_distributions_all aida
4880: WHERE aida.invoice_id = pn_invoice_id
4881: AND aida.invoice_line_number = pn_line_number
4882: AND jai_st_reversal_extract_pkg.is_paid_after_reversal(aila.accounting_date,
4883: aida.accounting_date, 'AP') = 'Y')
5167: p_location_id => ln_location_id,
5168: p_service_type_code => lv_service_type_code,
5169: p_source => 'AP_REVERSAL',
5170: p_source_trx_type => 'REVERSAL_ACCOUNTING',
5171: p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
5172: p_source_document_id => rec_reversal_entries.invoice_distribution_id,
5173: --p_transaction_date => rec_reversal_entries.invoice_date,--Commented by zhiwei for POT Bug#12970828 on 20110913
5174: p_transaction_date => reversal_trxn_rec.date_of_reversal, --Added by zhiwei for POT Bug#12970828 on 20110913
5175: p_account_name => NULL,