[Home] [Help]
332: from JAI_AR_TRX_TAX_LINES
333: where link_to_cust_trx_line_id
334: in
335: (select customer_trx_line_id
336: from JAI_AR_TRX_LINES
337: where customer_trx_id = pr_new.customer_trx_id
338: )
339: and tax_id in
340: (select tax_id
578: THEN
579: Update JAI_AR_TRXS
580: Set Customer_Trx_ID = pr_new.Customer_Trx_ID
581: Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
582: Update JAI_AR_TRX_LINES
583: Set Customer_Trx_Id = pr_new.Customer_Trx_ID
584: Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
585: END IF;
586:
683: regime_id ,
684: regime_code
685: FROM
686: JAI_AR_TRX_TAX_LINES jcttl,
687: JAI_AR_TRX_LINES jctl,
688: JAI_CMN_TAXES_ALL jtc ,
689: jai_regime_tax_types_v jrttv
690: WHERE
691: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
725: SELECT
726: 1
727: FROM
728: JAI_AR_TRX_TAX_LINES jcttl,
729: JAI_AR_TRX_LINES jctl,
730: JAI_CMN_TAXES_ALL jtc
731: WHERE
732: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
733: jctl.customer_trx_id = pr_new.customer_trx_id AND
1111:
1112: CURSOR line_cur IS
1113: SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
1114: excise_exempt_type, assessable_value
1115: FROM JAI_AR_TRX_LINES
1116: WHERE customer_trx_id = v_customer_trx_id
1117: ORDER BY customer_trx_line_id;
1118:
1119: CURSOR matched_qty_cur (p_customer_trx_line_id NUMBER) IS
1241: --3661746
1242: CURSOR c_total_Excise_amt IS
1243: SELECT nvl(sum(jrtl.func_tax_amount),0)
1244: FROM JAI_AR_TRXS jtrx,
1245: JAI_AR_TRX_LINES jtl,
1246: JAI_AR_TRX_TAX_LINES jrtl,
1247: JAI_CMN_TAXES_ALL jtc ,
1248: JAI_INV_ITM_SETUPS jmtl
1249: WHERE jrtl.tax_id = jtc.tax_id
1269: JAI_CMN_TAXES_ALL jtc
1270: WHERE jtc.tax_id = jrctl.tax_id
1271: AND link_to_cust_trx_line_id IN
1272: (SELECT customer_trx_line_id
1273: FROM JAI_AR_TRX_LINES
1274: WHERE customer_trx_id = pr_new.customer_trx_id
1275: )
1276: AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1277:
1322: regime_id ,
1323: regime_code
1324: FROM
1325: JAI_AR_TRX_TAX_LINES jcttl,
1326: JAI_AR_TRX_LINES jctl,
1327: JAI_CMN_TAXES_ALL jtc ,
1328: jai_regime_tax_types_v jrttv
1329: WHERE
1330: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1343: SELECT
1344: 1
1345: FROM
1346: JAI_AR_TRX_TAX_LINES jcttl,
1347: JAI_AR_TRX_LINES jctl,
1348: JAI_CMN_TAXES_ALL jtc
1349: WHERE
1350: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1351: jctl.customer_trx_id = pr_new.customer_trx_id AND
1391: IS
1392: SELECT
1393: excise_invoice_no
1394: FROM
1395: JAI_AR_TRX_LINES
1396: WHERE
1397: customer_trx_id = pr_new.customer_trx_id ;
1398:
1399:
1432: lv_enable_gst_flag VARCHAR2(1);
1433: --Added by Bo Li for bug#10043656 Begin
1434: ------------------------------------------------------------
1435: /*
1436: lv_gst_invoice_no JAI_AR_TRX_LINES.gst_invoice_no%TYPE;
1437:
1438: ln_cgst_regime_id NUMBER;
1439: ln_sgst_regime_id NUMBER;
1440: lv_cgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
1451: SELECT regime_id ,
1452: regime_code
1453: FROM
1454: JAI_AR_TRX_TAX_LINES jcttl,
1455: JAI_AR_TRX_LINES jctl,
1456: JAI_CMN_TAXES_ALL jtc ,
1457: jai_regime_tax_types_v jrttv
1458: WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1459: AND jctl.customer_trx_id = pr_new.customer_trx_id
1481: --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1482: ------------------------------------------------------------------------------
1483: CURSOR get_external_flag_cur IS
1484: SELECT COUNT(lines.customer_trx_id)
1485: FROM jai_ar_trx_lines lines,
1486: jai_interface_lines_all intfs
1487: WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
1488: AND lines.customer_trx_id = pr_new.customer_trx_id
1489: AND lines.interface_flag = 'Y'
1490: AND intfs.taxable_event = 'EXTERNAL';
1491:
1492: CURSOR get_open_source_cur IS
1493: SELECT COUNT(interface_flag)
1494: FROM jai_ar_trx_lines
1495: WHERE customer_trx_id = pr_new.customer_trx_id
1496: AND interface_flag = 'Y';
1497:
1498: CURSOR get_vat_invoice_no_cur IS
1520: regime_id ,
1521: regime_code
1522: FROM
1523: JAI_AR_TRX_TAX_LINES jcttl,
1524: JAI_AR_TRX_LINES jctl,
1525: JAI_CMN_TAXES_ALL jtc ,
1526: jai_regime_tax_types_v jrttv
1527: WHERE
1528: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1541: FROM dual
1542: WHERE EXISTS
1543: (SELECT 1
1544: FROM JAI_AR_TRX_TAX_LINES jcttl,
1545: JAI_AR_TRX_LINES jctl,
1546: JAI_CMN_TAXES_ALL jtc
1547: WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1548: AND jctl.customer_trx_id = pr_new.customer_trx_id
1549: AND jcttl.tax_id = jtc.tax_id
1978: END IF;
1979:
1980: IF NVL(v_item_class,'~') not in ('OTIN') THEN
1981:
1982: UPDATE JAI_AR_TRX_LINES
1983: SET EXCISE_INVOICE_NO = v_invoice_no ,
1984: EXCISE_INVOICE_DATE = SYSDATE
1985: WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1986: INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2244: /*
2245: Ends here
2246: */
2247:
2248: UPDATE JAI_AR_TRX_LINES
2249: SET PAYMENT_REGISTER = v_excise_paid_register
2250: WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2251: INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2252: CUSTOMER_TRX_ID = v_customer_trx_id;
2368: END IF;--Added by Zhiwei for Open Interface ER bug#11683927.
2369: /*
2370: Ends here - Additions by Sriram
2371: */
2372: UPDATE JAI_AR_TRX_LINES
2373: SET PAYMENT_REGISTER = v_excise_paid_register
2374: WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2375: INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2376: CUSTOMER_TRX_ID = v_customer_trx_id;
2851: -- CURSOR ADDED BY SRIRAM - BUG # 2654567
2852:
2853: CURSOR C_GET_TRX_DETAILS
2854: IS
2855: SELECT * FROM JAI_AR_TRX_LINES
2856: WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID;
2857:
2858:
2859: CURSOR C_GET_TRX_COUNT
2945: SELECT
2946: 1
2947: FROM
2948: JAI_AR_TRX_TAX_LINES jcttl,
2949: JAI_AR_TRX_LINES jctl,
2950: JAI_CMN_TAXES_ALL jtc
2951: WHERE
2952: jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
2953: jctl.customer_trx_id = pr_new.customer_trx_id AND
3381: -- And NVL(a.org_id,0) = v_org_id;
3382:
3383: Cursor Ar_Line_Cur IS
3384: Select Customer_Trx_Line_ID, Inventory_Item_ID, Unit_Code, Line_Amount, Quantity,unit_selling_price
3385: From JAI_AR_TRX_LINES
3386: Where Customer_Trx_ID = v_header_id;
3387:
3388: /* Bug 5243532. Added by Lakshmi Gopalsami
3389: Removed the reference to set_of_books_cur
3389: Removed the reference to set_of_books_cur
3390: which is selecting SOB from org_organization_definitions
3391: as the SOB will never by null in base table.
3392: */
3393: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3394:
3395: ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;
3396:
3397: -- Added by sacsethi for bug 5631784 on 30-01-2007
3391: as the SOB will never by null in base table.
3392: */
3393: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3394:
3395: ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;
3396:
3397: -- Added by sacsethi for bug 5631784 on 30-01-2007
3398: -- START 5631784
3399: LN_TCS_EXISTS NUMBER;
3763:
3764: END IF;
3765:
3766: v_service_type:=JAI_AR_RCTLA_TRIGGER_PKG.get_service_type( v_customer_id,v_ship_to_site_use_id ,'C'); --added by csahoo for Bug#5879769
3767: UPDATE JAI_AR_TRX_LINES
3768: SET tax_category_id = v_tax_category_id,
3769: service_type_code = v_service_type, --added by csahoo for Bug#5879769
3770: assessable_value = nvl(v_price_list,0),
3771: vat_assessable_value = ln_vat_assessable_value,
3821: --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
3822: ------------------------------------------------------------------------------
3823: CURSOR get_external_flag_cur IS
3824: SELECT COUNT(lines.customer_trx_id)
3825: FROM jai_ar_trx_lines lines,
3826: jai_interface_lines_all intfs
3827: WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
3828: AND lines.customer_trx_id = v_header_id
3829: AND lines.interface_flag = 'Y'
3830: AND intfs.taxable_event = 'EXTERNAL';
3831:
3832: CURSOR get_open_source_cur IS
3833: SELECT COUNT(interface_flag)
3834: FROM jai_ar_trx_lines
3835: WHERE customer_trx_id = v_header_id
3836: AND interface_flag = 'Y';
3837:
3838: ln_open_source NUMBER;
4073: SELECT regime_id ,
4074: regime_code
4075: FROM
4076: JAI_AR_TRX_TAX_LINES jcttl,
4077: JAI_AR_TRX_LINES jctl,
4078: JAI_CMN_TAXES_ALL jtc ,
4079: jai_regime_tax_types_v jrttv
4080: WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
4081: AND jctl.customer_trx_id = pr_new.customer_trx_id
4105: CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
4106: IS
4107: SELECT 1
4108: FROM JAI_AR_TRX_TAX_LINES jcttl,
4109: JAI_AR_TRX_LINES jctl,
4110: JAI_CMN_TAXES_ALL jtc
4111: WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
4112: AND jctl.customer_trx_id = pr_new.customer_trx_id
4113: AND jcttl.tax_id = jtc.tax_id
4166: jcttl.creation_date ,
4167: jtc.tax_type ,
4168: jcttl.tax_rate
4169: FROM
4170: JAI_AR_TRX_LINES jctl ,
4171: JAI_AR_TRX_TAX_LINES jcttl ,
4172: JAI_CMN_TAXES_ALL jtc ,
4173: (
4174: SELECT jrttv1.tax_type tax_type
4553: CURSOR LINES_INFO_CUR(p_parent_header_id IN Number) IS
4554: SELECT customer_trx_line_id, line_number, description, inventory_item_id, unit_code, quantity, tax_category_id,
4555: auto_invoice_flag, unit_selling_price, line_amount, gl_date,
4556: tax_amount,total_amount,assessable_value
4557: FROM JAI_AR_TRX_LINES
4558: WHERE customer_trx_id = p_parent_header_id
4559: ORDER BY customer_trx_line_id;
4560:
4561: CURSOR TAX_INFO_CUR(p_parent_line_id IN NUMBER) IS
4634: LOOP
4635:
4636: -- SELECT ra_customer_trx_lines_s.nextval INTO v_customer_trx_line_id FROM Dual;
4637:
4638: INSERT INTO JAI_AR_TRX_LINES
4639: (customer_trx_line_id, line_number,
4640: customer_trx_id, description,
4641: inventory_item_id, unit_code,
4642: quantity, tax_category_id,auto_invoice_flag ,
4689: v_header_tax_amount := nvl(v_header_tax_amount,0) + nvl(rec1.tax_amount,0);
4690: END IF;
4691:
4692: END LOOP;
4693: UPDATE JAI_AR_TRX_LINES
4694: SET tax_amount = v_line_tax_amount,
4695: total_amount = nvl(line_amount,0) + v_line_tax_amount
4696: WHERE customer_trx_line_id = v_customer_trx_line_id;
4697: v_line_tax_amount := 0;