[Home] [Help]
25: third party invoices only once. All RECEIVE and CORRECT of RECEIVE
26: type of transactions are considered for generating third party invoices.
27:
28: If a CORRECT to RECEIVE happens after third party invoice is
29: generated, third_party_flag in JAI_RCV_TRANSACTIONS is set to 'G'
30: to indicate that third party invoice has already been generated.
31:
32: This clean up also has introduced two tables which help in tracking
33: third party invoices.
75: (1) Added conditions in procedure process_receipt
76: in cursor c_thirdparty_tax_rec.
77: (a) Added shipment header id condition
78: (b) added aliases.
79: (c) Removed two separate conditions on jai_rcv_Transactions
80: and clubbed into a single one.
81: (2) Added aliases for the following cursors.
82: (a) c_get_thirdparty_count
83: (b) c_get_thirdparty_null_site_cnt
176: )
177: is
178:
179: /* Added by Ramananda for removal of SQL LITERALs */
180: lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
181: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
182:
183: cursor c_pending_tp_receipts(cp_org_id number) is /* modified the cursor query for bug 8567640 */
184: SELECT
177: is
178:
179: /* Added by Ramananda for removal of SQL LITERALs */
180: lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
181: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
182:
183: cursor c_pending_tp_receipts(cp_org_id number) is /* modified the cursor query for bug 8567640 */
184: SELECT
185: /*+ no_expand */ jrt.shipment_header_id
182:
183: cursor c_pending_tp_receipts(cp_org_id number) is /* modified the cursor query for bug 8567640 */
184: SELECT
185: /*+ no_expand */ jrt.shipment_header_id
186: FROM jai_rcv_transactions jrt,
187: jai_rcv_lines jrl
188: WHERE(jrt.transaction_type = 'RECEIVE' OR(jrt.transaction_type = 'CORRECT'
189: AND jrt.parent_transaction_type = 'RECEIVE'))
190: AND jrt.third_party_flag = 'N'
318: );
319:
320:
321: if nvl(p_simulation, 'N') <> 'Y' then
322: update JAI_RCV_TRANSACTIONS jrt
323: set third_party_flag = lv_process_flag
324: where shipment_header_id = r_pending_tp_receipts.shipment_header_id
325: and ( transaction_type = 'RECEIVE'
326: or
422: select receipt_num
423: from rcv_shipment_headers
424: where shipment_header_id = p_shipment_header_id;
425:
426: cursor c_rcv_transactions
427: (p_shipment_header_id number)is
428: select
429: vendor_id,
430: vendor_site_id, --added by eric for inclusive tax on 20-dec-2007
436: currency_code,
437: currency_conversion_type,
438: currency_conversion_date,
439: currency_conversion_rate
440: from rcv_transactions
441: where shipment_header_id = p_shipment_header_id
442: and transaction_type = 'RECEIVE';
443:
444: /* Added by Ramananda for removal of SQL LITERALs */
441: where shipment_header_id = p_shipment_header_id
442: and transaction_type = 'RECEIVE';
443:
444: /* Added by Ramananda for removal of SQL LITERALs */
445: lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
446: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
447:
448: /* Bug 4941642. Added by Lakshmi Gopalsami
449: Added aliases for the following cursors and
442: and transaction_type = 'RECEIVE';
443:
444: /* Added by Ramananda for removal of SQL LITERALs */
445: lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
446: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
447:
448: /* Bug 4941642. Added by Lakshmi Gopalsami
449: Added aliases for the following cursors and
450: Added alias and shipment_header_id and
465: where jrlt.shipment_header_id = p_shipment_header_id
466: and EXISTS
467: (
468: select 1
469: from JAI_RCV_TRANSACTIONS jrt
470: where jrt.shipment_header_id = jrlt.shipment_header_id
471: AND jrt.shipment_line_id = jrlt.shipment_line_id
472: AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
473: or
498: where jrlt.shipment_header_id = p_shipment_header_id
499: and EXISTS
500: (
501: select 1
502: from JAI_RCV_TRANSACTIONS jrt
503: where jrt.shipment_header_id = jrlt.shipment_header_id
504: AND jrt.shipment_line_id = jrlt.shipment_line_id
505: AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
506: or
532: where jrlt.shipment_header_id = p_shipment_header_id
533: and EXISTS
534: (
535: select 1
536: from JAI_RCV_TRANSACTIONS jrt
537: where jrt.shipment_header_id = jrlt.shipment_header_id
538: AND jrt.shipment_line_id = jrlt.shipment_line_id
539: AND ( jrt.transaction_type = lv_ttype_receive
540: or
621: from dual;
622:
623: cursor c_check_if_already_processed(p_shipment_header_id number) is
624: select count(transaction_id)
625: from JAI_RCV_TRANSACTIONS
626: where shipment_header_id = p_shipment_header_id
627: and third_party_flag in ('N', 'X'); -- for bug 14172169 by anupgupt
628:
629: cursor c_jai_regimes (cpv_regime_code jai_rgm_definitions.regime_code%type) is /* added by vumaasha for bug 8238608 */
630: select regime_id
631: from jai_rgm_definitions
632: where regime_code = cpv_regime_code;
633:
634: cursor c_trx_dtls(cp_transaction_id rcv_transactions.transaction_id%TYPE) is /* added by vumaasha for bug 8238608 */
635: SELECT rt.po_distribution_id,
636: rt.po_line_location_id ,
637: rt.po_line_id ,
638: rt.organization_id,
638: rt.organization_id,
639: pll.ship_to_organization_id,
640: pll.ship_to_location_id
641: FROM
642: rcv_transactions rt,
643: po_line_locations_all pll
644: where rt.po_line_location_id=pll.line_location_id AND
645: rt.transaction_id=cp_transaction_id;
646:
653: ln_accrual_account number;
654: ld_goods_received_date date;
655: lv_receipt_num rcv_shipment_headers.receipt_num%type;
656:
657: r_rcv_transactions c_rcv_transactions%rowtype;
658: r_get_vendor_details c_get_vendor_details%rowtype;
659:
660: lv_description ap_invoices_interface.description%type;
661: ln_tax_amount number;
658: r_get_vendor_details c_get_vendor_details%rowtype;
659:
660: lv_description ap_invoices_interface.description%type;
661: ln_tax_amount number;
662: lv_currency_conversion_type rcv_transactions.currency_conversion_type%type;
663: lv_currency_conversion_rate rcv_transactions.currency_conversion_rate%type;
664: lv_currency_conversion_date date;
665: ln_uid number; --File.Sql.35 Cbabu := fnd_global.user_id;
666: ln_inv_run_no number;
659:
660: lv_description ap_invoices_interface.description%type;
661: ln_tax_amount number;
662: lv_currency_conversion_type rcv_transactions.currency_conversion_type%type;
663: lv_currency_conversion_rate rcv_transactions.currency_conversion_rate%type;
664: lv_currency_conversion_date date;
665: ln_uid number; --File.Sql.35 Cbabu := fnd_global.user_id;
666: ln_inv_run_no number;
667: lv_invoice_num ap_invoices_all.invoice_num%type;
686: ln_lines_to_insert NUMBER default 1; --added by eric for inclusive tax on 20-dec-2007
687: ln_tax_line_amount NUMBER; --added by eric for inclusive tax on 20-dec-2007
688: orig_vndr_details_rec c_get_vendor_details%rowtype; --added by eric for inclusive tax on 20-dec-2007
689: ld_orig_goods_recv_date DATE; --added by eric for inclusive tax on 20-dec-2007
690: lv_orig_currcy_conver_type rcv_transactions.currency_conversion_type%type; --added by eric for inclusive tax on 20-dec-2007
691: lv_orig_currcy_conver_rate rcv_transactions.currency_conversion_rate%type; --added by eric for inclusive tax on 20-dec-2007
692: lv_orig_currcy_conver_date date; --added by eric for inclusive tax on 20-dec-2007
693: r_trx_dtls c_trx_dtls%ROWTYPE; /* added by vumaasha for bug 8238608 */
694: ln_regime_id jai_rgm_definitions.regime_id%TYPE; /* added by vumaasha for bug 8238608 */
687: ln_tax_line_amount NUMBER; --added by eric for inclusive tax on 20-dec-2007
688: orig_vndr_details_rec c_get_vendor_details%rowtype; --added by eric for inclusive tax on 20-dec-2007
689: ld_orig_goods_recv_date DATE; --added by eric for inclusive tax on 20-dec-2007
690: lv_orig_currcy_conver_type rcv_transactions.currency_conversion_type%type; --added by eric for inclusive tax on 20-dec-2007
691: lv_orig_currcy_conver_rate rcv_transactions.currency_conversion_rate%type; --added by eric for inclusive tax on 20-dec-2007
692: lv_orig_currcy_conver_date date; --added by eric for inclusive tax on 20-dec-2007
693: r_trx_dtls c_trx_dtls%ROWTYPE; /* added by vumaasha for bug 8238608 */
694: ln_regime_id jai_rgm_definitions.regime_id%TYPE; /* added by vumaasha for bug 8238608 */
695: ln_accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE; /* added by vumaasha for bug 8238608 */
712: , jai_cmn_taxes_all jcta --added by eric for inclusive tax
713: where
714: ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
715: ( select transaction_id, shipment_line_id
716: from JAI_RCV_TRANSACTIONS jrt
717: where shipment_header_id = pn_shipment_header_id
718: and ( transaction_type = lv_ttype_receive --'RECEIVE'
719: or
720: (transaction_type = lv_ttype_correct
771: end if;
772:
773:
774: /* Validation#1 : Check if PO details exist */
775: open c_rcv_transactions(p_shipment_header_id);
776: fetch c_rcv_transactions into r_rcv_transactions;
777: close c_rcv_transactions;
778:
779: if r_rcv_transactions.vendor_id is null then
772:
773:
774: /* Validation#1 : Check if PO details exist */
775: open c_rcv_transactions(p_shipment_header_id);
776: fetch c_rcv_transactions into r_rcv_transactions;
777: close c_rcv_transactions;
778:
779: if r_rcv_transactions.vendor_id is null then
780:
773:
774: /* Validation#1 : Check if PO details exist */
775: open c_rcv_transactions(p_shipment_header_id);
776: fetch c_rcv_transactions into r_rcv_transactions;
777: close c_rcv_transactions;
778:
779: if r_rcv_transactions.vendor_id is null then
780:
781: if p_debug >= 1 then
775: open c_rcv_transactions(p_shipment_header_id);
776: fetch c_rcv_transactions into r_rcv_transactions;
777: close c_rcv_transactions;
778:
779: if r_rcv_transactions.vendor_id is null then
780:
781: if p_debug >= 1 then
782: Fnd_File.put_line(Fnd_File.LOG,
783: ' Debug Level 1 : Details from rcv_transactions are not found for this shipment header, cannot process' );
779: if r_rcv_transactions.vendor_id is null then
780:
781: if p_debug >= 1 then
782: Fnd_File.put_line(Fnd_File.LOG,
783: ' Debug Level 1 : Details from rcv_transactions are not found for this shipment header, cannot process' );
784: end if;
785:
786: p_process_flag := 'E';
787: p_process_message := 'Details from rcv_transactions are not found for this shipment header, cannot process';
783: ' Debug Level 1 : Details from rcv_transactions are not found for this shipment header, cannot process' );
784: end if;
785:
786: p_process_flag := 'E';
787: p_process_message := 'Details from rcv_transactions are not found for this shipment header, cannot process';
788: goto exit_from_procedure;
789:
790: end if;
791:
794: lv_ttype_correct := 'CORRECT' ;
795:
796: /* Validation#2 : Check if third party taxes exist */
797: open c_get_thirdparty_count
798: (p_shipment_header_id, r_rcv_transactions.vendor_id);
799: fetch c_get_thirdparty_count into ln_thirdparty_count;
800: close c_get_thirdparty_count;
801:
802: if nvl(ln_thirdparty_count, 0) = 0 then
816: lv_ttype_receive := 'RECEIVE' ;
817: lv_ttype_correct := 'CORRECT' ;
818:
819: open c_get_thirdparty_null_site_cnt
820: (p_shipment_header_id, r_rcv_transactions.vendor_id);
821: fetch c_get_thirdparty_null_site_cnt into ln_thirdparty_null_site_cnt;
822: close c_get_thirdparty_null_site_cnt;
823:
824: if nvl(ln_thirdparty_null_site_cnt, 0) > 0 then
839: /* Added by Ramananda for removal of SQL LITERALs */
840: lv_ttype_receive := 'RECEIVE' ;
841: lv_ttype_correct := 'CORRECT' ;
842: open c_get_tparty_invalid_comb_cnt
843: (p_shipment_header_id, r_rcv_transactions.vendor_id);
844: fetch c_get_tparty_invalid_comb_cnt into ln_tparty_invalid_comb_cnt;
845: close c_get_tparty_invalid_comb_cnt;
846:
847: if nvl(ln_tparty_invalid_comb_cnt, 0) > 0 then
870: open c_rcv_shipment_headers(p_shipment_header_id);
871: fetch c_rcv_shipment_headers into lv_receipt_num;
872: close c_rcv_shipment_headers;
873:
874: if r_rcv_transactions.po_distribution_id is not null then
875:
876: open c_get_po_dist_account(r_rcv_transactions.po_distribution_id);
877: fetch c_get_po_dist_account into ln_accrual_account;
878: close c_get_po_dist_account;
872: close c_rcv_shipment_headers;
873:
874: if r_rcv_transactions.po_distribution_id is not null then
875:
876: open c_get_po_dist_account(r_rcv_transactions.po_distribution_id);
877: fetch c_get_po_dist_account into ln_accrual_account;
878: close c_get_po_dist_account;
879:
880: elsif r_rcv_transactions.po_line_location_id is not null then
876: open c_get_po_dist_account(r_rcv_transactions.po_distribution_id);
877: fetch c_get_po_dist_account into ln_accrual_account;
878: close c_get_po_dist_account;
879:
880: elsif r_rcv_transactions.po_line_location_id is not null then
881:
882: open c_get_latest_po_dist_account(r_rcv_transactions.po_line_location_id);
883: fetch c_get_latest_po_dist_account into ln_accrual_account;
884: close c_get_latest_po_dist_account;
878: close c_get_po_dist_account;
879:
880: elsif r_rcv_transactions.po_line_location_id is not null then
881:
882: open c_get_latest_po_dist_account(r_rcv_transactions.po_line_location_id);
883: fetch c_get_latest_po_dist_account into ln_accrual_account;
884: close c_get_latest_po_dist_account;
885:
886: end if;
893:
894: -- get the functional currency
895: jai_rcv_utils_pkg.get_func_curr
896: (
897: r_rcv_transactions.organization_id,
898: lv_func_currency,
899: ln_gl_set_of_books_id
900: );
901:
907: (
908: /*Bug 4941642. Added by Lakshmi Gopalsami
909: (1) Added shipment header id condition
910: (2) added aliases.
911: (3) Removed two separate conditions on jai_rcv_Transactions
912: and clubbed into a single one.
913: */
914: select
915: jrtv.vendor_id
923: , jai_cmn_taxes_all jcta --added by eric for inclusive tax
924: where
925: ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
926: ( select transaction_id, shipment_line_id
927: from JAI_RCV_TRANSACTIONS jrt
928: where shipment_header_id = p_shipment_header_id
929: and ( transaction_type = lv_ttype_receive --'RECEIVE'
930: or
931: (transaction_type = lv_ttype_correct
936: )
937: and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
938: and jrtv.vendor_id > 0
939: and nvl(jrtv.tax_amount, 0) is not null
940: and jrtv.vendor_id <> r_rcv_transactions.vendor_id /* bug#3957167 */
941: and jrtv.shipment_header_id = p_shipment_header_id
942: and jrtv.tax_id = jcta.tax_id --added by eric for inclusive tax
943: GROUP BY
944: jrtv.vendor_id
1063:
1064: ln_tax_amount := c_thirdparty_tax_rec.tax_amount;
1065:
1066: if c_thirdparty_tax_rec.currency <> lv_func_currency then
1067: lv_currency_conversion_type := r_rcv_transactions.currency_conversion_type;
1068: lv_currency_conversion_rate := r_rcv_transactions.currency_conversion_rate;
1069: lv_currency_conversion_date := r_rcv_transactions.currency_conversion_date;
1070: else
1071: lv_currency_conversion_type := null;
1064: ln_tax_amount := c_thirdparty_tax_rec.tax_amount;
1065:
1066: if c_thirdparty_tax_rec.currency <> lv_func_currency then
1067: lv_currency_conversion_type := r_rcv_transactions.currency_conversion_type;
1068: lv_currency_conversion_rate := r_rcv_transactions.currency_conversion_rate;
1069: lv_currency_conversion_date := r_rcv_transactions.currency_conversion_date;
1070: else
1071: lv_currency_conversion_type := null;
1072: lv_currency_conversion_rate := null;
1065:
1066: if c_thirdparty_tax_rec.currency <> lv_func_currency then
1067: lv_currency_conversion_type := r_rcv_transactions.currency_conversion_type;
1068: lv_currency_conversion_rate := r_rcv_transactions.currency_conversion_rate;
1069: lv_currency_conversion_date := r_rcv_transactions.currency_conversion_date;
1070: else
1071: lv_currency_conversion_type := null;
1072: lv_currency_conversion_rate := null;
1073: lv_currency_conversion_date := null;
1099: DECLARE
1100: /* cursor to fetch tax lines of shipment header id and shipment line id */
1101: CURSOR c_third_party_trans IS
1102: SELECT jrlt.transaction_id, jrlt.shipment_header_id, jrlt.shipment_line_id, jrlt.tax_type, jrlt.tax_amount, jcta.vat_flag, jcta.adhoc_flag, jcta.tax_name
1103: FROM JAI_RCV_TRANSACTIONS jrt, JAI_RCV_LINE_TAXES jrlt, jai_cmn_taxes_all jcta
1104: WHERE jrt.transaction_id = jrlt.transaction_id
1105: AND jrt.shipment_header_id = jrlt.shipment_header_id
1106: AND jrt.shipment_line_id = jrlt.shipment_line_id
1107: AND jrt.shipment_header_id = p_shipment_header_id
1109: AND jrt.third_party_flag = 'N'
1110: AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery)
1111: AND jrlt.vendor_id > 0
1112: AND NVL(jrlt.tax_amount, 0) IS NOT NULL
1113: AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
1114: AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
1115: AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1116: AND jrlt.currency = c_thirdparty_tax_rec.currency
1117: AND jrlt.tax_id = jcta.tax_id;
1115: AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1116: AND jrlt.currency = c_thirdparty_tax_rec.currency
1117: AND jrlt.tax_id = jcta.tax_id;
1118:
1119: v_transaction_type jai_rcv_transactions.transaction_type%type;
1120: v_transaction_id jai_rcv_transactions.transaction_id%type;
1121: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1122: v_corrected_quantity NUMBER;
1123: v_original_quantity NUMBER;
1116: AND jrlt.currency = c_thirdparty_tax_rec.currency
1117: AND jrlt.tax_id = jcta.tax_id;
1118:
1119: v_transaction_type jai_rcv_transactions.transaction_type%type;
1120: v_transaction_id jai_rcv_transactions.transaction_id%type;
1121: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1122: v_corrected_quantity NUMBER;
1123: v_original_quantity NUMBER;
1124: BEGIN
1117: AND jrlt.tax_id = jcta.tax_id;
1118:
1119: v_transaction_type jai_rcv_transactions.transaction_type%type;
1120: v_transaction_id jai_rcv_transactions.transaction_id%type;
1121: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1122: v_corrected_quantity NUMBER;
1123: v_original_quantity NUMBER;
1124: BEGIN
1125: ln_tax_amount := 0;
1126: FOR v_third_party_trans IN c_third_party_trans
1127: LOOP
1128: /* fetch transaction type of receive event transaction */
1129: SELECT transaction_type INTO v_transaction_type
1130: FROM jai_rcv_transactions
1131: WHERE transaction_id = v_third_party_trans.transaction_id;
1132:
1133: Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1.1. before apportion v_third_party_trans.transaction_id > '||v_third_party_trans.transaction_id
1134: ||' > v_third_party_trans.tax_name > '||v_third_party_trans.tax_name||' > v_third_party_trans.tax_type > '||v_third_party_trans.tax_type ||' > v_third_party_trans.tax_amount > '
1139: IF v_third_party_trans.tax_type NOT IN ('Freight','Insurance','Octrai','Other','PURCHASE TAX','ENTRY TAX') AND (NVL(v_third_party_trans.adhoc_flag,'N') = 'N'
1140: OR (NVL(v_third_party_trans.adhoc_flag,'N') = 'Y' AND NVL(v_third_party_trans.vat_flag,'N') = 'Q')) AND v_transaction_type <> lv_ttype_correct THEN
1141: /* fetch transaction id of receive event */
1142: SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
1143: FROM jai_rcv_transactions
1144: WHERE shipment_header_id = v_third_party_trans.shipment_header_id
1145: AND shipment_line_id = v_third_party_trans.shipment_line_id
1146: AND transaction_type = lv_ttype_receive;
1147:
1146: AND transaction_type = lv_ttype_receive;
1147:
1148: /* fetch total of quantity corrections done on receive event */
1149: SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
1150: FROM JAI_RCV_TRANSACTIONS
1151: WHERE parent_transaction_id = v_parent_transaction_id
1152: AND transaction_type = lv_ttype_correct;
1153:
1154: /* apportion tax amount based on before correction quantity and corrected quantity and add it to invoice header amount */
1243: p_jai_source => 'Third Party Invoices',
1244: p_invoice_id => ln_interface_invoice_id,
1245: p_invoice_num => lv_invoice_num,
1246: p_invoice_type_lookup_code => 'STANDARD',
1247: p_invoice_date => r_rcv_transactions.transaction_date, /* bug 9141528 */
1248: p_vendor_id => c_thirdparty_tax_rec.vendor_id,
1249: p_vendor_site_id => c_thirdparty_tax_rec.vendor_site_id,
1250: p_invoice_amount => round(ln_tax_amount,2),
1251: p_invoice_currency_code => c_thirdparty_tax_rec.currency,
1288: CLOSE get_totl_incl_tax_amount;
1289: -----------------------------------------------------------------------
1290: --added by eric for BUG#6988610 on Apr-24,2008, End
1291:
1292: OPEN c_get_vendor_site_dtls(r_rcv_transactions.vendor_site_id);
1293: FETCH c_get_vendor_site_dtls
1294: INTO orig_vndr_details_rec;
1295: CLOSE c_get_vendor_site_dtls;
1296:
1299: OR orig_vndr_details_rec.payment_method_lookup_code IS NULL
1300: OR orig_vndr_details_rec.pay_group_lookup_code IS NULL
1301: )
1302: THEN
1303: OPEN c_get_vendor_details(r_rcv_transactions.vendor_id);
1304: FETCH c_get_vendor_details
1305: INTO orig_vndr_details_rec;
1306: CLOSE c_get_vendor_details;
1307: END IF; -- IF( orig_vndr_details_rec.terms_id IS NULL)
1305: INTO orig_vndr_details_rec;
1306: CLOSE c_get_vendor_details;
1307: END IF; -- IF( orig_vndr_details_rec.terms_id IS NULL)
1308:
1309: OPEN c_get_goods_received_date(r_rcv_transactions.vendor_id , r_rcv_transactions.vendor_site_id);
1310: FETCH c_get_goods_received_date
1311: INTO ld_orig_goods_recv_date;
1312: CLOSE c_get_goods_received_date;
1313:
1310: FETCH c_get_goods_received_date
1311: INTO ld_orig_goods_recv_date;
1312: CLOSE c_get_goods_received_date;
1313:
1314: IF r_rcv_transactions.currency_code <> lv_func_currency then
1315: lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1316: lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1317: lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1318: ELSE
1311: INTO ld_orig_goods_recv_date;
1312: CLOSE c_get_goods_received_date;
1313:
1314: IF r_rcv_transactions.currency_code <> lv_func_currency then
1315: lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1316: lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1317: lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1318: ELSE
1319: lv_orig_currcy_conver_type := null;
1312: CLOSE c_get_goods_received_date;
1313:
1314: IF r_rcv_transactions.currency_code <> lv_func_currency then
1315: lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1316: lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1317: lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1318: ELSE
1319: lv_orig_currcy_conver_type := null;
1320: lv_orig_currcy_conver_rate := null;
1313:
1314: IF r_rcv_transactions.currency_code <> lv_func_currency then
1315: lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1316: lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1317: lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1318: ELSE
1319: lv_orig_currcy_conver_type := null;
1320: lv_orig_currcy_conver_rate := null;
1321: lv_orig_currcy_conver_date := null;
1328: p_invoice_id => ln_interface_invoice_id,
1329: p_invoice_num => lv_invoice_num,
1330: p_invoice_type_lookup_code => 'CREDIT', /* CREDIT Memo*/ --changed by eric for inclusive tax
1331: p_invoice_date => SYSDATE,
1332: p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
1333: p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
1334: --p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
1335: p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
1336: p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
1329: p_invoice_num => lv_invoice_num,
1330: p_invoice_type_lookup_code => 'CREDIT', /* CREDIT Memo*/ --changed by eric for inclusive tax
1331: p_invoice_date => SYSDATE,
1332: p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
1333: p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
1334: --p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
1335: p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
1336: p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
1337: p_exchange_rate => lv_orig_currcy_conver_type, --changed by eric for inclusive tax
1332: p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
1333: p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
1334: --p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
1335: p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
1336: p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
1337: p_exchange_rate => lv_orig_currcy_conver_type, --changed by eric for inclusive tax
1338: p_exchange_rate_type => lv_orig_currcy_conver_rate, --changed by eric for inclusive tax
1339: p_exchange_date => lv_orig_currcy_conver_date, --changed by eric for inclusive tax
1340: p_terms_id => orig_vndr_details_rec.terms_id, --changed by eric for inclusive tax
1379:
1380: /*Bug 4941642. Added by Lakshmi Gopalsami
1381: (1) Added shipment header id condition
1382: (2) added aliases.
1383: (3) Removed two separate conditions on jai_rcv_Transactions
1384: and clubbed into a single one.
1385: */
1386: FOR Tax_rec IN
1387: ( SELECT
1394: , jai_cmn_taxes_all jcta --added by eric for inclusive tax
1395: WHERE jrlt.shipment_header_id = p_shipment_header_id
1396: AND (jrlt.transaction_id, jrlt.shipment_header_id,jrlt.shipment_line_id) in /*modified for bug 8567640 */
1397: ( SELECT jrt.transaction_id,jrt.shipment_header_id,jrt.shipment_line_id
1398: FROM JAI_RCV_TRANSACTIONS jrt
1399: WHERE jrt.shipment_header_id = p_shipment_header_id
1400: AND ( jrt.transaction_type = lv_ttype_receive
1401: or
1402: (jrt.transaction_type = lv_ttype_correct
1407: )
1408: AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
1409: AND jrlt.vendor_id > 0
1410: AND nvl(jrlt.tax_amount, 0) IS NOT NULL
1411: AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
1412: AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
1413: AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1414: AND jrlt.currency = c_thirdparty_tax_rec.currency
1415: AND jrlt.tax_id = jcta.tax_id --added by eric for inclusive tax
1432:
1433: -- start for bug 13528285 by anupgupt
1434: /* code added to apportioning of third party invoice lines amounts based on correction of quantity in receipt*/
1435: DECLARE
1436: v_transaction_type jai_rcv_transactions.transaction_type%type;
1437: v_transaction_id jai_rcv_transactions.transaction_id%type;
1438: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1439: v_corrected_quantity NUMBER;
1440: v_original_quantity NUMBER;
1433: -- start for bug 13528285 by anupgupt
1434: /* code added to apportioning of third party invoice lines amounts based on correction of quantity in receipt*/
1435: DECLARE
1436: v_transaction_type jai_rcv_transactions.transaction_type%type;
1437: v_transaction_id jai_rcv_transactions.transaction_id%type;
1438: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1439: v_corrected_quantity NUMBER;
1440: v_original_quantity NUMBER;
1441: BEGIN
1434: /* code added to apportioning of third party invoice lines amounts based on correction of quantity in receipt*/
1435: DECLARE
1436: v_transaction_type jai_rcv_transactions.transaction_type%type;
1437: v_transaction_id jai_rcv_transactions.transaction_id%type;
1438: v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1439: v_corrected_quantity NUMBER;
1440: v_original_quantity NUMBER;
1441: BEGIN
1442: /* fetch transaction type of transaction*/
1440: v_original_quantity NUMBER;
1441: BEGIN
1442: /* fetch transaction type of transaction*/
1443: SELECT transaction_type INTO v_transaction_type
1444: FROM jai_rcv_transactions
1445: WHERE transaction_id = tax_rec.transaction_id;
1446:
1447: Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7.1. before apportion tax_rec.transaction_id > '||tax_rec.transaction_id||' > tax_rec.tax_name > '||tax_rec.tax_name||' > tax_rec.tax_type > '
1448: ||tax_rec.tax_type ||' > tax_Rec.tax_amount > '||tax_Rec.tax_amount||' > tax_rec.adhoc_flag > '||tax_rec.adhoc_flag||' > tax_rec.vat_flag > '||tax_rec.vat_flag||' > tax_rec.tax_type > '||tax_rec.tax_type
1452: IF tax_rec.tax_type NOT IN ('Freight','Insurance','Octrai','Other','PURCHASE TAX','ENTRY TAX') AND (NVL(tax_rec.adhoc_flag,'N') = 'N' OR (NVL(tax_rec.adhoc_flag,'N') = 'Y'
1453: AND NVL(tax_rec.vat_flag,'N') = 'Q')) AND v_transaction_type <> lv_ttype_correct THEN
1454: /* fetch transaction id of receive event */
1455: SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
1456: FROM jai_rcv_transactions
1457: WHERE shipment_header_id = Tax_rec.shipment_header_id
1458: AND shipment_line_id = Tax_rec.shipment_line_id
1459: AND transaction_type = lv_ttype_receive;
1460:
1459: AND transaction_type = lv_ttype_receive;
1460:
1461: /* fetch total correction of quantity done on receive event */
1462: SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
1463: FROM JAI_RCV_TRANSACTIONS
1464: WHERE parent_transaction_id = v_parent_transaction_id
1465: AND transaction_type = lv_ttype_correct;
1466:
1467: /* apportion tax amount based on quantity of receive before correction and quantity correccted */
1578: p_line_type_lookup_code => 'ITEM', --added by eric FOR BUG bug#6790599
1579: --Modified by eric for inclusive tax ,begin
1580: p_amount => ln_tax_line_amount,-- round(tax_Rec.tax_amount,2),
1581: --Modified by eric for inclusive tax ,end
1582: p_accounting_date => r_rcv_transactions.transaction_date,
1583: p_description => lv_description,
1584: p_dist_code_combination_id => ln_accrual_account,
1585: p_assets_tracking_flag => lv_assets_tracking_flag,
1586: p_created_by => ln_uid,