[Home] [Help]
14: lv_process_flag varchar2(20);
15: lv_process_message varchar2(200);
16: ln_final_tds_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
17:
18: cursor c_ap_invoices_all is
19: select ai.vendor_id,
20: ai.vendor_site_id,
21: ai.invoice_currency_code,
22: ai.exchange_rate,
26: -- Bug#5131075(4683207). Added by Lakshmi Gopalsami
27: ai.invoice_type_lookup_code,
28: ai.invoice_num, /*added for bug 6493858 ref-6318997*/
29: pv.vendor_type_lookup_code /* Bug 8330522. Added by Lakshmi Gopalsami */
30: from ap_invoices_all ai, po_vendors pv
31: where ai.invoice_id = pr_new.invoice_id
32: and ai.vendor_id = pv.vendor_id;
33:
34: --Added by Sanjikum for Bug#5131075(4722011)
45:
46: lv_prepay_flag VARCHAR2(1);
47: --End addition by Sanjikum for Bug#5131075(4722011)
48:
49: c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
50: lv_codepath VARCHAR2(1996);
51: lv_is_invoice_validated varchar2(1);
52: lv_new_transaction_si varchar2(1);
53: lv_new_transaction_pp varchar2(1);
50: lv_codepath VARCHAR2(1996);
51: lv_is_invoice_validated varchar2(1);
52: lv_new_transaction_si varchar2(1);
53: lv_new_transaction_pp varchar2(1);
54: ln_org_id ap_invoices_all.org_id%type;
55: ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
56: /* bug 5640993 FP of 5553489. Added by JMEENA
57: * Created variable which decides whether the prepayment
58: * created prior to upgrade(TDS-Threshold) has to be processed
51: lv_is_invoice_validated varchar2(1);
52: lv_new_transaction_si varchar2(1);
53: lv_new_transaction_pp varchar2(1);
54: ln_org_id ap_invoices_all.org_id%type;
55: ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
56: /* bug 5640993 FP of 5553489. Added by JMEENA
57: * Created variable which decides whether the prepayment
58: * created prior to upgrade(TDS-Threshold) has to be processed
59: */
141: -- end for bug 83305222.
142:
143: r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
144:
145: lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
146:
147: FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
148: RETURN VARCHAR2
149: IS
198: p_invoice_distribution_id => pr_new.invoice_distribution_id,
199: p_prepay_distribution_id => pr_new.prepay_distribution_id,
200: p_parent_reversal_id => pr_new.parent_reversal_id,
201: p_prepay_amount => pr_new.amount,
202: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
203: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204: p_accounting_date => pr_new.accounting_date,
205: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
199: p_prepay_distribution_id => pr_new.prepay_distribution_id,
200: p_parent_reversal_id => pr_new.parent_reversal_id,
201: p_prepay_amount => pr_new.amount,
202: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
203: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204: p_accounting_date => pr_new.accounting_date,
205: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
207: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
201: p_prepay_amount => pr_new.amount,
202: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
203: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204: p_accounting_date => pr_new.accounting_date,
205: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
207: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
208: p_org_id => pr_new.org_id,
209: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
202: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
203: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204: p_accounting_date => pr_new.accounting_date,
205: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
207: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
208: p_org_id => pr_new.org_id,
209: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
210: p_process_flag => lv_process_flag,
203: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204: p_accounting_date => pr_new.accounting_date,
205: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
207: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
208: p_org_id => pr_new.org_id,
209: p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
210: p_process_flag => lv_process_flag,
211: p_process_message => lv_process_message,
1282:
1283: --end additions for bug#13422310
1284:
1285: /* TDS Tax Defaultation Functionality Bug # 4088186 */
1286: open c_ap_invoices_all;
1287: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
1288: close c_ap_invoices_all;
1289:
1290: /* Bug 5640993 FP of 5553489. Added by JMEENA
1283: --end additions for bug#13422310
1284:
1285: /* TDS Tax Defaultation Functionality Bug # 4088186 */
1286: open c_ap_invoices_all;
1287: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
1288: close c_ap_invoices_all;
1289:
1290: /* Bug 5640993 FP of 5553489. Added by JMEENA
1291: * Initialized the variable lv_process_old_trxn to 'Y' so that
1284:
1285: /* TDS Tax Defaultation Functionality Bug # 4088186 */
1286: open c_ap_invoices_all;
1287: fetch c_ap_invoices_all into c_rec_ap_invoices_all;
1288: close c_ap_invoices_all;
1289:
1290: /* Bug 5640993 FP of 5553489. Added by JMEENA
1291: * Initialized the variable lv_process_old_trxn to 'Y' so that
1292: * prepayments will be processed during insert event.
1336: if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
1337:
1338: if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
1339: pr_new.line_type_lookup_code <> 'PREPAY' and
1340: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1341: c_rec_ap_invoices_all.cancelled_date is null
1342: then
1343: -- comment for debug DTC by xin on 28-Dec-2011
1344: /*jai_ap_tds_tax_defaultation.process_invoice
1337:
1338: if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
1339: pr_new.line_type_lookup_code <> 'PREPAY' and
1340: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1341: c_rec_ap_invoices_all.cancelled_date is null
1342: then
1343: -- comment for debug DTC by xin on 28-Dec-2011
1344: /*jai_ap_tds_tax_defaultation.process_invoice
1345: (
1350: p_distribution_line_number => pr_new.distribution_line_number,
1351: p_parent_reversal_id => pr_new.parent_reversal_id,
1352: p_reversal_flag => pr_new.reversal_flag,
1353: p_amount => pr_new.amount,
1354: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1355: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1356: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357: p_po_distribution_id => pr_new.po_distribution_id,
1358: p_rcv_transaction_id => pr_new.rcv_transaction_id,
1351: p_parent_reversal_id => pr_new.parent_reversal_id,
1352: p_reversal_flag => pr_new.reversal_flag,
1353: p_amount => pr_new.amount,
1354: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1355: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1356: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357: p_po_distribution_id => pr_new.po_distribution_id,
1358: p_rcv_transaction_id => pr_new.rcv_transaction_id,
1359: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1352: p_reversal_flag => pr_new.reversal_flag,
1353: p_amount => pr_new.amount,
1354: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1355: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1356: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357: p_po_distribution_id => pr_new.po_distribution_id,
1358: p_rcv_transaction_id => pr_new.rcv_transaction_id,
1359: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1360: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1355: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1356: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357: p_po_distribution_id => pr_new.po_distribution_id,
1358: p_rcv_transaction_id => pr_new.rcv_transaction_id,
1359: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1360: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1361: p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
1362: p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
1363: p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
1356: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357: p_po_distribution_id => pr_new.po_distribution_id,
1358: p_rcv_transaction_id => pr_new.rcv_transaction_id,
1359: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1360: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1361: p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
1362: p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
1363: p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
1364: p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
1453: -- if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
1454: -- pr_new.match_status_flag = 'A' and
1455: if
1456: --Update by Chong.Lei for ZX integration test 20120411 end
1457: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1458: c_rec_ap_invoices_all.cancelled_date is null
1459: then
1460:
1461: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1454: -- pr_new.match_status_flag = 'A' and
1455: if
1456: --Update by Chong.Lei for ZX integration test 20120411 end
1457: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1458: c_rec_ap_invoices_all.cancelled_date is null
1459: then
1460:
1461: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1462: Don't proceed for TDS invoice creation if the invoice type
1462: Don't proceed for TDS invoice creation if the invoice type
1463: is either 'CREDIT' or 'DEBIT'
1464: */
1465:
1466: If c_rec_ap_invoices_all.invoice_type_lookup_code
1467: IN ('CREDIT', 'DEBIT')
1468: Then
1469: return;
1470: End if;
1518: ------------------------------------------------------------------
1519: --Updated by zhiwei for DTC bug#13359892 on 20111219 end
1520: (
1521: p_invoice_id => pr_new.invoice_id,
1522: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1523: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524: p_accounting_date => pr_new.accounting_date,
1525: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1519: --Updated by zhiwei for DTC bug#13359892 on 20111219 end
1520: (
1521: p_invoice_id => pr_new.invoice_id,
1522: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1523: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524: p_accounting_date => pr_new.accounting_date,
1525: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1527: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1521: p_invoice_id => pr_new.invoice_id,
1522: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1523: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524: p_accounting_date => pr_new.accounting_date,
1525: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1527: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1528: p_org_id => pr_new.org_id,
1529: p_call_from => 'ja_in_ap_aida_before_trg',
1522: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1523: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524: p_accounting_date => pr_new.accounting_date,
1525: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1527: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1528: p_org_id => pr_new.org_id,
1529: p_call_from => 'ja_in_ap_aida_before_trg',
1530: p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
1523: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524: p_accounting_date => pr_new.accounting_date,
1525: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1527: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1528: p_org_id => pr_new.org_id,
1529: p_call_from => 'ja_in_ap_aida_before_trg',
1530: p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
1531: p_process_flag => lv_process_flag,
1603: /*END, by amandali for Bug#10430662*/
1604: /* Prepayment functionality */
1605: if pv_action = jai_constants.inserting then
1606: if pr_new.line_type_lookup_code = 'PREPAY' and
1607: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1608: c_rec_ap_invoices_all.cancelled_date is null
1609: then
1610:
1611: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1604: /* Prepayment functionality */
1605: if pv_action = jai_constants.inserting then
1606: if pr_new.line_type_lookup_code = 'PREPAY' and
1607: c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1608: c_rec_ap_invoices_all.cancelled_date is null
1609: then
1610:
1611: /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1612: Don't proceed for TDS invoice creation if the invoice type
1612: Don't proceed for TDS invoice creation if the invoice type
1613: is either 'CREDIT' or 'DEBIT'
1614: */
1615:
1616: If c_rec_ap_invoices_all.invoice_type_lookup_code
1617: IN ('CREDIT', 'DEBIT')
1618: Then
1619: return;
1620: End if;
1684: p_invoice_distribution_id => pr_new.invoice_distribution_id,
1685: p_prepay_distribution_id => pr_new.prepay_distribution_id,
1686: p_parent_reversal_id => pr_new.parent_reversal_id,
1687: p_prepay_amount => pr_new.amount,
1688: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1689: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690: p_accounting_date => pr_new.accounting_date,
1691: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1685: p_prepay_distribution_id => pr_new.prepay_distribution_id,
1686: p_parent_reversal_id => pr_new.parent_reversal_id,
1687: p_prepay_amount => pr_new.amount,
1688: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1689: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690: p_accounting_date => pr_new.accounting_date,
1691: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1693: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1687: p_prepay_amount => pr_new.amount,
1688: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1689: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690: p_accounting_date => pr_new.accounting_date,
1691: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1693: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1694: p_org_id => pr_new.org_id,
1695: p_process_flag => lv_process_flag,
1688: p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1689: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690: p_accounting_date => pr_new.accounting_date,
1691: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1693: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1694: p_org_id => pr_new.org_id,
1695: p_process_flag => lv_process_flag,
1696: p_process_message => lv_process_message,
1689: p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690: p_accounting_date => pr_new.accounting_date,
1691: p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692: p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1693: p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1694: p_org_id => pr_new.org_id,
1695: p_process_flag => lv_process_flag,
1696: p_process_message => lv_process_message,
1697: p_codepath => lv_codepath
1738:
1739: end if; /* Prepayment functionality */
1740:
1741: /*Code Started start changes for bug 6493858 - cancellation functionality*/
1742: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source <> 'INDIA TDS'
1743: THEN
1744: --commented the following for bug#9052839
1745: /*OPEN c_tds_invoice_id(pr_new.invoice_id);
1746: FETCH c_tds_invoice_id INTO r_tds_invoice_id;
1761: END IF;
1762: /*end changes for bug 6493858 -Code Ended */
1763:
1764: /* Bug 8330522. Added by Lakshmi Gopalsami */
1765: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
1766: AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
1767: THEN
1768: --commented the following for bug#9052839
1769: /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
1762: /*end changes for bug 6493858 -Code Ended */
1763:
1764: /* Bug 8330522. Added by Lakshmi Gopalsami */
1765: IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
1766: AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
1767: THEN
1768: --commented the following for bug#9052839
1769: /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
1770: FETCH c_tds_invoice_id1 INTO r_tds_invoice_id;