[Home] [Help]
70: b.cancelled_date,
71: b.invoice_type_lookup_code invoice_type,
72: a.creation_date,
73: a.po_distribution_id
74: FROM ap_invoice_distributions_all a, ap_invoices_all b
75: WHERE a.invoice_id = b.invoice_id
76: AND invoice_distribution_id = cp_invoice_distribution_id;
77:
78: PROCEDURE get_org_location(p_invoice_id IN NUMBER,
159: aida.rcv_transaction_id,
160: aida.po_distribution_id
161: FROM ap_invoice_payments_all a,
162: ap_checks_all b,
163: ap_invoice_distributions_all aida
164: WHERE a.invoice_id = cp_invoice_id
165: AND a.check_id = b.check_id
166: AND a.invoice_id = aida.invoice_id
167: AND a.accounting_date < cp_start_date
203: apinvp.org_id,
204: apinvp.accounting_date
205: FROM ap_invoice_payments_all apinvp,
206: ap_checks_all apc,
207: ap_invoice_distributions_all ainvd,
208: jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
209: WHERE apinvp.org_id = p_org_id
210: AND apinvp.check_id = apc.check_id
211: AND nvl(apc.future_pay_due_date, SYSDATE) <= SYSDATE
211: AND nvl(apc.future_pay_due_date, SYSDATE) <= SYSDATE
212: AND apinvp.accounting_date BETWEEN cp_start_date AND cp_till_date
213: AND ainvd.invoice_id IN
214: (SELECT invoice_id
215: FROM ap_invoice_distributions_all
216: WHERE org_id = p_org_id
217: AND po_distribution_id IN
218: (SELECT pda.po_distribution_id
219: FROM po_line_locations_all pll,
246: ap_dist.org_id,
247: ap_dist.invoice_line_number,
248: prepay.accounting_date,
249: ap_dist.po_distribution_id
250: FROM ap_invoice_distributions_all prepay,
251: ap_invoice_distributions_all ap_dist,
252: jai_rgm_trx_refs jrtr
253: WHERE prepay.invoice_id = cp_invoice_id
254: AND ap_dist.invoice_id = prepay.invoice_id
247: ap_dist.invoice_line_number,
248: prepay.accounting_date,
249: ap_dist.po_distribution_id
250: FROM ap_invoice_distributions_all prepay,
251: ap_invoice_distributions_all ap_dist,
252: jai_rgm_trx_refs jrtr
253: WHERE prepay.invoice_id = cp_invoice_id
254: AND ap_dist.invoice_id = prepay.invoice_id
255: AND prepay.line_type_lookup_code = jai_constants.prepay_line
282: ap_dist.org_id,
283: ap_dist.invoice_line_number,
284: prepay.accounting_date,
285: ap_dist.po_distribution_id
286: FROM ap_invoice_distributions_all prepay,
287: ap_invoice_distributions_all ap_dist,
288: jai_rgm_trx_refs jrtr
289: WHERE ap_dist.org_id = p_org_id
290: AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
283: ap_dist.invoice_line_number,
284: prepay.accounting_date,
285: ap_dist.po_distribution_id
286: FROM ap_invoice_distributions_all prepay,
287: ap_invoice_distributions_all ap_dist,
288: jai_rgm_trx_refs jrtr
289: WHERE ap_dist.org_id = p_org_id
290: AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
291: AND ap_dist.invoice_id = prepay.invoice_id
675: abs(ap_dist.amount) tax_amt,
676: tax.tax_rate,
677: tax.tax_type
678: FROM ap_invoice_lines_all ap_line,
679: ap_invoice_distributions_all ap_dist,
680: jai_cmn_taxes_all tax
681: WHERE ap_line.invoice_id = ap_dist.invoice_id
682: AND ap_dist.invoice_distribution_id = pn_distribution_id
683: AND ap_dist.invoice_line_number = ap_line.line_number
721: b.cancelled_date,
722: b.invoice_type_lookup_code invoice_type,
723: a.creation_date,
724: a.po_distribution_id
725: FROM ap_invoice_distributions_all a, ap_invoices_all b
726: WHERE a.invoice_id = b.invoice_id
727: AND invoice_distribution_id = cp_invoice_distribution_id;
728:
729: CURSOR c_reference(cp_source IN VARCHAR2,
743: AND line_id = cp_invoice_distribution_id;
744:
745: CURSOR c_total_inv_amount(cp_invoice_id NUMBER) IS
746: select sum(amount)
747: from ap_invoice_distributions_all a
748: where invoice_id = cp_invoice_id
749: and prepay_distribution_id is null;
750:
751: r_ref c_reference%ROWTYPE;
756: r_prepayment c_invoice_distribution%ROWTYPE;
757: --Added by Junjian for bug#14459664 on 13-Aug-2012 start
758: CURSOR c_get_inv_amount(cp_invoice_id NUMBER) IS
759: select sum(amount)
760: from ap_invoice_distributions_all
761: where invoice_id = cp_invoice_id
762: and cancellation_flag is null;
763: --Added by Junjian for bug#14459664 on 13-Aug-2012 end
764:
1230: lv_flag VARCHAR2(1) := 'N';
1231: CURSOR c_reverse_charge_flag IS
1232: SELECT 'Y'
1233: FROM AP_INVOICE_LINES_ALL ap_line,
1234: AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
1235: JAI_CMN_TAXES_ALL tax
1236: WHERE ap_line.invoice_id = ap_dist.invoice_id
1237: AND ap_dist.invoice_distribution_id = pn_distribution_id
1238: AND ap_dist.invoice_line_number = ap_line.line_number
1278: lv_flag VARCHAR2(1) := 'N';
1279: CURSOR c_reverse_charge_flag IS
1280: SELECT 'Y'
1281: FROM AP_INVOICE_LINES_ALL ap_line,
1282: AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
1283: JAI_CMN_TAXES_ALL tax
1284: WHERE ap_line.invoice_id = ap_dist.invoice_id
1285: AND ap_dist.invoice_distribution_id = pn_distribution_id
1286: AND ap_dist.invoice_line_number = ap_line.line_number
1924: CURSOR c_get_reference_id ( pn_invoice_id NUMBER, pn_invoice_line_id NUMBER, pn_tax_id NUMBER )IS
1925: SELECT jrf.reference_id
1926: FROM jai_rgm_trx_refs jrf,
1927: ap_invoice_lines_all aila,
1928: ap_invoice_distributions_all aida
1929: Where aida.invoice_id = aila.invoice_id
1930: AND aida.INVOICE_LINE_NUMBER = aila.line_number
1931: AND aida.invoice_distribution_id <> pn_invoice_line_id
1932: AND aila.reference_key1 = pn_tax_id
1933: AND aila.reference_key2 =
1934: (
1935: SELECT al.reference_key2
1936: from ap_invoice_lines_all al,
1937: ap_invoice_distributions_all ad
1938: where al.invoice_id = ad.invoice_id
1939: and ad.INVOICE_LINE_NUMBER = al.line_number
1940: and al.invoice_id = pn_invoice_id
1941: and ad.invoice_distribution_id = pn_invoice_line_id