[Home] [Help]
40: --Add by Wenqiong for POT, reg bug#12397015 on 26-Apr-2011, begin
41: ---------------------------------------------------------------------------
42: CURSOR get_invoice_type_cur IS
43: SELECT invoice_type_lookup_code
44: FROM ap_invoices_all
45: WHERE invoice_id = pr_new.invoice_id;
46:
47: CURSOR get_tax_type_cur IS
48: SELECT jcta.tax_type
103: -----------------------------------------------
104: , invoice_currency_code
105: -----------------------------------------------
106: --Add by qiong for bug12934221 2011.09.07 end
107: FROM ap_invoices_all
108: WHERE invoice_id = pr_new.invoice_id ;
109:
110: CURSOR get_regime_id_cur IS
111: SELECT regime_id
168: );
169:
170: CURSOR get_cancell_date_cur IS
171: SELECT cancelled_date
172: FROM ap_invoices_all
173: WHERE invoice_id = pr_new.invoice_id;
174: --start additions for bug#16241506
175: cursor get_acct_exists(CP_INVOICE_DISTIRBUTION_ID in number)
176: is
891: lv_process_flag varchar2(20);
892: lv_process_message varchar2(200);
893: ln_final_tds_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
894:
895: cursor c_ap_invoices_all is
896: select ai.vendor_id,
897: ai.vendor_site_id,
898: ai.invoice_currency_code,
899: ai.exchange_rate,
903: -- Bug#5131075(4683207). Added by Lakshmi Gopalsami
904: ai.invoice_type_lookup_code,
905: ai.invoice_num, /*added for bug 6493858 ref-6318997*/
906: pv.vendor_type_lookup_code /* Bug 8330522. Added by Lakshmi Gopalsami */
907: from ap_invoices_all ai, po_vendors pv
908: where ai.invoice_id = pr_new.invoice_id
909: and ai.vendor_id = pv.vendor_id;
910:
911: --Added by Sanjikum for Bug#5131075(4722011)
922:
923: lv_prepay_flag VARCHAR2(1);
924: --End addition by Sanjikum for Bug#5131075(4722011)
925:
926: c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
927: lv_codepath VARCHAR2(1996);
928: lv_is_invoice_validated varchar2(1);
929: lv_new_transaction_si varchar2(1);
930: lv_new_transaction_pp varchar2(1);
927: lv_codepath VARCHAR2(1996);
928: lv_is_invoice_validated varchar2(1);
929: lv_new_transaction_si varchar2(1);
930: lv_new_transaction_pp varchar2(1);
931: ln_org_id ap_invoices_all.org_id%type;
932: ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
933: /* bug 5640993 FP of 5553489. Added by JMEENA
934: * Created variable which decides whether the prepayment
935: * created prior to upgrade(TDS-Threshold) has to be processed
928: lv_is_invoice_validated varchar2(1);
929: lv_new_transaction_si varchar2(1);
930: lv_new_transaction_pp varchar2(1);
931: ln_org_id ap_invoices_all.org_id%type;
932: ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
933: /* bug 5640993 FP of 5553489. Added by JMEENA
934: * Created variable which decides whether the prepayment
935: * created prior to upgrade(TDS-Threshold) has to be processed
936: */
1018: -- end for bug 83305222.
1019:
1020: r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
1021:
1022: lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
1023:
1024: FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
1025: RETURN VARCHAR2
1026: IS
1070: p_invoice_distribution_id => pr_new.invoice_distribution_id,
1071: p_prepay_distribution_id => pr_new.prepay_distribution_id,
1072: p_parent_reversal_id => pr_new.parent_reversal_id,
1073: p_prepay_amount => pr_new.amount,
1074: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1075: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076: p_accounting_date => pr_new.accounting_date,
1077: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1071: p_prepay_distribution_id => pr_new.prepay_distribution_id,
1072: p_parent_reversal_id => pr_new.parent_reversal_id,
1073: p_prepay_amount => pr_new.amount,
1074: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1075: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076: p_accounting_date => pr_new.accounting_date,
1077: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1079: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1073: p_prepay_amount => pr_new.amount,
1074: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1075: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076: p_accounting_date => pr_new.accounting_date,
1077: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1079: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1080: p_org_id => pr_new.org_id,
1081: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
1074: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1075: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076: p_accounting_date => pr_new.accounting_date,
1077: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1079: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1080: p_org_id => pr_new.org_id,
1081: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
1082: p_process_flag => lv_process_flag,
1075: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076: p_accounting_date => pr_new.accounting_date,
1077: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1079: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1080: p_org_id => pr_new.org_id,
1081: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
1082: p_process_flag => lv_process_flag,
1083: p_process_message => lv_process_message,
2109:
2110: --end additions for bug#13422310
2111:
2112: /* TDS Tax Defaultation Functionality Bug # 4088186 */
2113: open c_ap_invoices_all;
2114: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
2115: close c_ap_invoices_all;
2116:
2117: /* Bug 5640993 FP of 5553489. Added by JMEENA
2110: --end additions for bug#13422310
2111:
2112: /* TDS Tax Defaultation Functionality Bug # 4088186 */
2113: open c_ap_invoices_all;
2114: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
2115: close c_ap_invoices_all;
2116:
2117: /* Bug 5640993 FP of 5553489. Added by JMEENA
2118: * Initialized the variable lv_process_old_trxn to 'Y' so that
2111:
2112: /* TDS Tax Defaultation Functionality Bug # 4088186 */
2113: open c_ap_invoices_all;
2114: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
2115: close c_ap_invoices_all;
2116:
2117: /* Bug 5640993 FP of 5553489. Added by JMEENA
2118: * Initialized the variable lv_process_old_trxn to 'Y' so that
2119: * prepayments will be processed during insert event.
2160: if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
2161:
2162: if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
2163: pr_new.line_type_lookup_code <> 'PREPAY' and
2164: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2165: c_rec_ap_invoices_all.cancelled_date is null
2166: then
2167:
2168: jai_ap_tds_tax_defaultation.process_invoice
2161:
2162: if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
2163: pr_new.line_type_lookup_code <> 'PREPAY' and
2164: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2165: c_rec_ap_invoices_all.cancelled_date is null
2166: then
2167:
2168: jai_ap_tds_tax_defaultation.process_invoice
2169: (
2174: p_distribution_line_number => pr_new.distribution_line_number,
2175: p_parent_reversal_id => pr_new.parent_reversal_id,
2176: p_reversal_flag => pr_new.reversal_flag,
2177: p_amount => pr_new.amount,
2178: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2179: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2180: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181: p_po_distribution_id => pr_new.po_distribution_id,
2182: p_rcv_transaction_id => pr_new.rcv_transaction_id,
2175: p_parent_reversal_id => pr_new.parent_reversal_id,
2176: p_reversal_flag => pr_new.reversal_flag,
2177: p_amount => pr_new.amount,
2178: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2179: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2180: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181: p_po_distribution_id => pr_new.po_distribution_id,
2182: p_rcv_transaction_id => pr_new.rcv_transaction_id,
2183: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2176: p_reversal_flag => pr_new.reversal_flag,
2177: p_amount => pr_new.amount,
2178: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2179: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2180: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181: p_po_distribution_id => pr_new.po_distribution_id,
2182: p_rcv_transaction_id => pr_new.rcv_transaction_id,
2183: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2184: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2179: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2180: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181: p_po_distribution_id => pr_new.po_distribution_id,
2182: p_rcv_transaction_id => pr_new.rcv_transaction_id,
2183: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2184: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2185: p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
2186: p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
2187: p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
2180: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181: p_po_distribution_id => pr_new.po_distribution_id,
2182: p_rcv_transaction_id => pr_new.rcv_transaction_id,
2183: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2184: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2185: p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
2186: p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
2187: p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
2188: p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
2276: if pv_action = jai_constants.updating then
2277:
2278: if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
2279: pr_new.match_status_flag = 'A' and
2280: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2281: c_rec_ap_invoices_all.cancelled_date is null
2282: then
2283:
2284: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2277:
2278: if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
2279: pr_new.match_status_flag = 'A' and
2280: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2281: c_rec_ap_invoices_all.cancelled_date is null
2282: then
2283:
2284: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2285: Don't proceed for TDS invoice creation if the invoice type
2285: Don't proceed for TDS invoice creation if the invoice type
2286: is either 'CREDIT' or 'DEBIT'
2287: */
2288:
2289: If c_rec_ap_invoices_all.invoice_type_lookup_code
2290: IN ('CREDIT', 'DEBIT')
2291: Then
2292: return;
2293: End if;
2317: lv_codepath := null;
2318: jai_ap_tds_generation_pkg.process_tds_at_inv_validate
2319: (
2320: p_invoice_id => pr_new.invoice_id,
2321: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2322: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323: p_accounting_date => pr_new.accounting_date,
2324: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2318: jai_ap_tds_generation_pkg.process_tds_at_inv_validate
2319: (
2320: p_invoice_id => pr_new.invoice_id,
2321: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2322: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323: p_accounting_date => pr_new.accounting_date,
2324: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2326: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2320: p_invoice_id => pr_new.invoice_id,
2321: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2322: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323: p_accounting_date => pr_new.accounting_date,
2324: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2326: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2327: p_org_id => pr_new.org_id,
2328: p_call_from => 'ja_in_ap_aida_before_trg',
2321: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2322: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323: p_accounting_date => pr_new.accounting_date,
2324: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2326: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2327: p_org_id => pr_new.org_id,
2328: p_call_from => 'ja_in_ap_aida_before_trg',
2329: p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
2322: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323: p_accounting_date => pr_new.accounting_date,
2324: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2326: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2327: p_org_id => pr_new.org_id,
2328: p_call_from => 'ja_in_ap_aida_before_trg',
2329: p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
2330: p_process_flag => lv_process_flag,
2402: /*END, by amandali for Bug#10430662*/
2403: /* Prepayment functionality */
2404: if pv_action = jai_constants.inserting then
2405: if pr_new.line_type_lookup_code = 'PREPAY' and
2406: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2407: c_rec_ap_invoices_all.cancelled_date is null
2408: then
2409:
2410: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2403: /* Prepayment functionality */
2404: if pv_action = jai_constants.inserting then
2405: if pr_new.line_type_lookup_code = 'PREPAY' and
2406: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2407: c_rec_ap_invoices_all.cancelled_date is null
2408: then
2409:
2410: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2411: Don't proceed for TDS invoice creation if the invoice type
2411: Don't proceed for TDS invoice creation if the invoice type
2412: is either 'CREDIT' or 'DEBIT'
2413: */
2414:
2415: If c_rec_ap_invoices_all.invoice_type_lookup_code
2416: IN ('CREDIT', 'DEBIT')
2417: Then
2418: return;
2419: End if;
2483: p_invoice_distribution_id => pr_new.invoice_distribution_id,
2484: p_prepay_distribution_id => pr_new.prepay_distribution_id,
2485: p_parent_reversal_id => pr_new.parent_reversal_id,
2486: p_prepay_amount => pr_new.amount,
2487: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2488: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489: p_accounting_date => pr_new.accounting_date,
2490: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2484: p_prepay_distribution_id => pr_new.prepay_distribution_id,
2485: p_parent_reversal_id => pr_new.parent_reversal_id,
2486: p_prepay_amount => pr_new.amount,
2487: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2488: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489: p_accounting_date => pr_new.accounting_date,
2490: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2492: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2486: p_prepay_amount => pr_new.amount,
2487: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2488: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489: p_accounting_date => pr_new.accounting_date,
2490: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2492: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2493: p_org_id => pr_new.org_id,
2494: p_process_flag => lv_process_flag,
2487: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2488: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489: p_accounting_date => pr_new.accounting_date,
2490: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2492: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2493: p_org_id => pr_new.org_id,
2494: p_process_flag => lv_process_flag,
2495: p_process_message => lv_process_message,
2488: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489: p_accounting_date => pr_new.accounting_date,
2490: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2492: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2493: p_org_id => pr_new.org_id,
2494: p_process_flag => lv_process_flag,
2495: p_process_message => lv_process_message,
2496: p_codepath => lv_codepath
2537:
2538: end if; /* Prepayment functionality */
2539:
2540: /*Code Started start changes for bug 6493858 - cancellation functionality*/
2541: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source <> 'INDIA TDS'
2542: THEN
2543: --commented the following for bug#9052839
2544: /*OPEN c_tds_invoice_id(pr_new.invoice_id);
2545: FETCH c_tds_invoice_id INTO r_tds_invoice_id;
2560: END IF;
2561: /*end changes for bug 6493858 -Code Ended */
2562:
2563: /* Bug 8330522. Added by Lakshmi Gopalsami */
2564: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
2565: AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
2566: THEN
2567: --commented the following for bug#9052839
2568: /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
2561: /*end changes for bug 6493858 -Code Ended */
2562:
2563: /* Bug 8330522. Added by Lakshmi Gopalsami */
2564: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
2565: AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
2566: THEN
2567: --commented the following for bug#9052839
2568: /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
2569: FETCH c_tds_invoice_id1 INTO r_tds_invoice_id;