DBA Data[Home] [Help]

APPS.ZX_JL_EXTRACT_PKG dependencies on ZX_REP_TRX_DETAIL_T

Line 147: CL_DOC_SUB_TYPE ZX_REP_TRX_DETAIL_T.DOCUMENT_SUB_TYPE%TYPE,

143: TYPE GDF_AP_INV_ATT12_TBL IS TABLE OF
144: ZX_REP_TRX_JX_EXT_T.GDF_AP_INVOICES_ATT12%TYPE INDEX BY BINARY_INTEGER;
145:
146: TYPE zxclpplr_doc_sub_typ_rec IS RECORD(
147: CL_DOC_SUB_TYPE ZX_REP_TRX_DETAIL_T.DOCUMENT_SUB_TYPE%TYPE,
148: CL_NUM_OF_DOC ZX_REP_TRX_JX_EXT_T.NUMERIC3%TYPE,
149: CL_TOTAL_VAT_TAX ZX_REP_TRX_JX_EXT_T.NUMERIC6%TYPE,
150: CL_TOTAL_OTHER_TAX ZX_REP_TRX_JX_EXT_T.NUMERIC11%type,
151: CL_TOTAL_EXEMPT_AMT ZX_REP_TRX_JX_EXT_T.NUMERIC4%TYPE,

Line 360: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

356: );
357:
358: FUNCTION GET_TAX_AUTHORITY_CODE
359: (
360: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
361: P_ORG_ID IN NUMBER
362: )
363: return ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE;
364:

Line 480: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

476: );
477:
478: PROCEDURE GET_REC_COUNT
479: (
480: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
481: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
482: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
483: P_REQUEST_ID IN NUMBER,
484: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL

Line 481: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

477:
478: PROCEDURE GET_REC_COUNT
479: (
480: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
481: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
482: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
483: P_REQUEST_ID IN NUMBER,
484: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL
485: );

Line 489: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

485: );
486:
487: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
488: (
489: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
490: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
491: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
492: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
493: P_REQUEST_ID IN NUMBER,

Line 490: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

486:
487: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
488: (
489: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
490: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
491: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
492: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
493: P_REQUEST_ID IN NUMBER,
494: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL

Line 491: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

487: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
488: (
489: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
490: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
491: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
492: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
493: P_REQUEST_ID IN NUMBER,
494: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL
495: );

Line 560: DELETE from ZX_REP_TRX_DETAIL_T DET

556: END IF;
557:
558: IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JLARTPFF' THEN
559:
560: DELETE from ZX_REP_TRX_DETAIL_T DET
561: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
562: AND DET.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
563: /* DELETE from ZX_REP_TRX_DETAIL_T DET
564: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and

Line 563: /* DELETE from ZX_REP_TRX_DETAIL_T DET

559:
560: DELETE from ZX_REP_TRX_DETAIL_T DET
561: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
562: AND DET.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
563: /* DELETE from ZX_REP_TRX_DETAIL_T DET
564: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
565: NOT EXISTS
566: (SELECT 1
567: FROM jl_zz_ar_tx_categ_all catg,

Line 585: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

581: AND det.tax = P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX);
582: */
583: IF ( g_level_statement>= g_current_runtime_level ) THEN
584: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES',
585: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
586: END IF;
587: END IF;
588:
589: IF (g_level_procedure >= g_current_runtime_level ) THEN

Line 793: FROM zx_rep_trx_detail_t itf,

789: l_trx_id_tbl,
790: l_tax_rate_tbl,
791: l_document_sub_type_tbl,
792: l_trx_line_dist_id_tbl
793: FROM zx_rep_trx_detail_t itf,
794: ap_invoices apinv
795: WHERE itf.request_id = p_request_id
796: AND itf.trx_id = apinv.invoice_id
797: AND itf.tax_type_code = P_TAX_TYPE_CODE

Line 868: DELETE from zx_rep_trx_detail_t itf

864: END IF;
865:
866: -- Delete Unwanted lines from Detail ITF
867:
868: DELETE from zx_rep_trx_detail_t itf
869: WHERE itf.request_id = p_request_id
870: AND NOT EXISTS ( SELECT 1
871: FROM zx_rep_trx_jx_ext_t ext
872: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 876: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

872: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
873:
874: IF ( g_level_statement>= g_current_runtime_level ) THEN
875: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
876: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
877: END IF;
878:
879:
880: EXCEPTION

Line 911: FROM zx_rep_trx_detail_t itf,

907: l_trx_id_tbl,
908: l_tax_rate_tbl,
909: l_document_sub_type_tbl,
910: l_trx_line_dist_id_tbl
911: FROM zx_rep_trx_detail_t itf,
912: ap_invoices_all apinv --Bug 5415028
913: WHERE itf.request_id = P_REQUEST_ID
914: AND itf.tax_type_code = P_VAT_TAX_TYPE
915: -- OR itf.tax_rate = 0)

Line 1080: FROM zx_rep_trx_detail_t itf,

1076:
1077:
1078: DELETE FROM zx_rep_trx_jx_ext_t
1079: WHERE detail_tax_line_id not in ( SELECT min(itf.detail_tax_line_id)
1080: FROM zx_rep_trx_detail_t itf,
1081: ap_invoices_all apinv
1082: WHERE itf.request_id = P_REQUEST_ID
1083: AND itf.tax_type_code = P_VAT_TAX_TYPE
1084: AND itf.trx_id = apinv.invoice_id

Line 1091: DELETE from zx_rep_trx_detail_t itf

1087: GROUP BY itf.tax_rate,
1088: itf.trx_line_id,
1089: itf.trx_id);
1090:
1091: DELETE from zx_rep_trx_detail_t itf
1092: WHERE itf.request_id = p_request_id
1093: AND NOT EXISTS ( SELECT 1
1094: FROM zx_rep_trx_jx_ext_t ext
1095: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 1104: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

1100: END IF;
1101:
1102: IF ( g_level_statement>= g_current_runtime_level ) THEN
1103: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
1104: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
1105: END IF;
1106:
1107: EXCEPTION
1108:

Line 1174: FROM zx_rep_trx_detail_t itf1,

1170: itf1.billing_tp_address_id) bill_from_site_id,
1171: NVL(itf1.billing_trading_partner_id,
1172: itf1.shipping_trading_partner_id) bill_from_party_id,
1173: itf1.internal_organization_id
1174: FROM zx_rep_trx_detail_t itf1,
1175: ap_invoices apinv
1176: WHERE itf1.request_id = P_REQUEST_ID
1177: AND apinv.invoice_id = itf1.trx_id
1178: -- AND itf1.posted_flag = 'Y'

Line 1209: FROM zx_rep_trx_detail_t itf1,

1205: itf1.billing_tp_address_id) bill_from_site_id,
1206: NVL(itf1.billing_trading_partner_id,
1207: itf1.shipping_trading_partner_id) bill_from_party_id,
1208: itf1.internal_organization_id
1209: FROM zx_rep_trx_detail_t itf1,
1210: ap_invoices apinv
1211: WHERE itf1.request_id = P_REQUEST_ID
1212: AND apinv.invoice_id = itf1.trx_id
1213: -- AND itf1.posted_flag = 'Y'

Line 1245: FROM zx_rep_trx_detail_t itf1,

1241: itf1.billing_tp_address_id) bill_from_site_id,
1242: NVL(itf1.billing_trading_partner_id,
1243: itf1.shipping_trading_partner_id) bill_from_party_id,
1244: itf1.internal_organization_id
1245: FROM zx_rep_trx_detail_t itf1,
1246: ap_invoices apinv
1247: WHERE itf1.request_id = P_REQUEST_ID
1248: AND apinv.invoice_id = itf1.trx_id
1249: -- AND itf1.posted_flag = 'Y'

Line 1271: FROM zx_rep_trx_detail_t itf2,

1267: null currency_conversion_rate,
1268: null tax_rate,
1269: null tax_rate_id,
1270: null document_sub_type
1271: FROM zx_rep_trx_detail_t itf2,
1272: ap_invoices apinv2
1273: WHERE itf2.request_id = P_REQUEST_ID
1274: AND apinv2.invoice_id = itf2.trx_id
1275: AND itf2.tax_type_code = P_NON_TAXAB_TAX_TYPE

Line 1542: DELETE from zx_rep_trx_detail_t itf

1538: END IF;
1539:
1540: -- Delete Unwanted lines from Detail ITF
1541:
1542: DELETE from zx_rep_trx_detail_t itf
1543: WHERE itf.request_id = p_request_id
1544: AND NOT EXISTS ( SELECT 1
1545: FROM zx_rep_trx_jx_ext_t ext
1546: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 1585: FROM zx_rep_trx_detail_t itf1 ,

1581: l_tax_rate_tbl,
1582: l_document_sub_type_tbl,
1583: l_tax_amt_tbl,
1584: l_tax_amt_func_tbl
1585: FROM zx_rep_trx_detail_t itf1 ,
1586: zx_fc_codes_denorm_b fc
1587: WHERE itf1.request_id = P_REQUEST_ID
1588: AND itf1.cancel_flag = 'N'
1589: -- AND itf1.posted_flag = 'Y' --Bug 5413860

Line 1613: FROM zx_rep_trx_detail_t itf1

1609: BULK COLLECT INTO l_detail_tax_line_id_tbl,
1610: l_trx_id_tbl,
1611: l_tax_rate_tbl,
1612: l_document_sub_type_tbl
1613: FROM zx_rep_trx_detail_t itf1
1614: WHERE itf1.request_id = P_REQUEST_ID
1615: AND itf1.cancel_flag = 'N'
1616: AND itf1.posted_flag = 'Y'
1617: AND ( itf1.reverse_flag IS NULL OR itf1.reverse_flag <>'Y')

Line 1783: UPDATE zx_rep_trx_detail_t itf

1779: 'After insertion into zx_rep_trx_jx_ext_t ');
1780: END IF;
1781:
1782: FORALL i in NVL(l_detail_tax_line_id_tbl.first,-99)..NVL(l_detail_tax_line_id_tbl.last,0)
1783: UPDATE zx_rep_trx_detail_t itf
1784: SET itf.TAX_AMT_FUNCL_CURR = l_tax_amt_func_tbl(i),
1785: itf.TAX_AMT = l_tax_amt_tbl(i)
1786: WHERE itf.detail_tax_line_id = l_detail_tax_line_id_tbl(i);
1787:

Line 1790: 'After updating zx_rep_trx_detail_t ');

1786: WHERE itf.detail_tax_line_id = l_detail_tax_line_id_tbl(i);
1787:
1788: IF ( g_level_statement>= g_current_runtime_level ) THEN
1789: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
1790: 'After updating zx_rep_trx_detail_t ');
1791: END IF;
1792:
1793: -- Delete Unwanted lines from Detail ITF
1794: DELETE from zx_rep_trx_detail_t itf

Line 1794: DELETE from zx_rep_trx_detail_t itf

1790: 'After updating zx_rep_trx_detail_t ');
1791: END IF;
1792:
1793: -- Delete Unwanted lines from Detail ITF
1794: DELETE from zx_rep_trx_detail_t itf
1795: WHERE itf.request_id = p_request_id
1796: AND NOT EXISTS ( SELECT 1
1797: FROM zx_rep_trx_jx_ext_t ext
1798: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 1803: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

1799:
1800:
1801: IF ( g_level_statement>= g_current_runtime_level ) THEN
1802: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
1803: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
1804: END IF;
1805:
1806: EXCEPTION
1807:

Line 1819: DELETE from zx_rep_trx_detail_t itf

1815: END;
1816:
1817: ELSIF P_REPORT_NAME = 'JLARPPTF' THEN
1818:
1819: DELETE from zx_rep_trx_detail_t itf
1820: WHERE itf.request_id = p_request_id
1821: AND (NVL(itf.reverse_flag,'N') = 'Y'
1822: OR itf.tax_type_code <> NVL(p_trl_global_variables_rec.per_tax_type_code_high,
1823: p_trl_global_variables_rec.per_tax_type_code_low));

Line 1832: AND NOT EXISTS ( SELECT 1 FROM zx_rep_trx_detail_t itf

1828: AND p_trl_global_variables_rec.gl_date_high)*/
1829:
1830: DELETE from zx_rep_actg_ext_t actg
1831: WHERE actg.request_id = p_request_id
1832: AND NOT EXISTS ( SELECT 1 FROM zx_rep_trx_detail_t itf
1833: WHERE actg.detail_tax_line_id = itf.detail_tax_line_id);
1834:
1835: SELECT detail_tax_line_id,
1836: trx_line_id,

Line 1847: FROM zx_rep_trx_detail_t

1843: l_trx_id_tbl,
1844: l_tax_rate_tbl,
1845: l_tax_rate_id_tbl,
1846: l_tax_regime_code_tbl
1847: FROM zx_rep_trx_detail_t
1848: WHERE request_id = p_request_id;
1849:
1850:
1851: GET_TAX_AUTH_CATEG(

Line 2033: -- DELETE from ZX_REP_TRX_DETAIL_T DET

2029: IF (g_level_procedure >= g_current_runtime_level ) THEN
2030: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2031: 'JLARTPFF : Filter ');
2032: END IF;
2033: -- DELETE from ZX_REP_TRX_DETAIL_T DET
2034: -- WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
2035: -- AND det.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
2036:
2037: SELECT detail_tax_line_id,

Line 2051: FROM zx_rep_trx_detail_t itf1

2047: l_internal_org_id_tbl,
2048: l_tax_rate_tbl,
2049: l_document_sub_type_tbl,
2050: l_exchange_rate_tbl
2051: FROM zx_rep_trx_detail_t itf1
2052: -- ra_customer_trx rct
2053: WHERE itf1.request_id = P_REQUEST_ID
2054: -- AND rct.customer_trx_id = itf1.trx_id
2055: AND itf1.trx_line_class in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')

Line 2182: DELETE from zx_rep_trx_detail_t itf

2178: 'After insertion into zx_rep_trx_jx_ext_t ');
2179: END IF;
2180: -- Delete Unwanted lines from Detail ITF
2181:
2182: DELETE from zx_rep_trx_detail_t itf
2183: WHERE itf.request_id = p_request_id
2184: AND NOT EXISTS ( SELECT 1
2185: FROM zx_rep_trx_jx_ext_t ext
2186: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 2190: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

2186: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
2187:
2188: IF ( g_level_statement>= g_current_runtime_level ) THEN
2189: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2190: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2191: END IF;
2192:
2193: IF (g_level_procedure >= g_current_runtime_level ) THEN
2194: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 2262: FROM zx_rep_trx_detail_t itf1

2258: itf1.trx_type_id,
2259: itf1.tax_rate tax_rate,
2260: itf1.tax_rate_id,
2261: null document_sub_type
2262: FROM zx_rep_trx_detail_t itf1
2263: WHERE itf1.request_id = P_REQUEST_ID
2264: AND itf1.tax = p_vat_tax
2265: AND nvl(itf1.tax_type_code,'VAT') = 'VAT'
2266: AND itf1.tax_rate <> 0

Line 2286: FROM zx_rep_trx_detail_t itf2

2282: itf2.trx_type_id,
2283: 0 tax_rate,
2284: itf2.tax_rate_id,
2285: null document_sub_type
2286: FROM zx_rep_trx_detail_t itf2
2287: WHERE itf2.request_id = P_REQUEST_ID
2288: AND NVL(itf2.tax_type_code, 'VAT') = 'VAT'
2289: AND nvl(itf2.TAX_RATE,0) = 0
2290: AND itf2.tax = p_vat_tax

Line 2292: FROM zx_rep_trx_detail_t itf3

2288: AND NVL(itf2.tax_type_code, 'VAT') = 'VAT'
2289: AND nvl(itf2.TAX_RATE,0) = 0
2290: AND itf2.tax = p_vat_tax
2291: AND not exists (SELECT 1
2292: FROM zx_rep_trx_detail_t itf3
2293: WHERE itf3.request_id = P_REQUEST_ID
2294: AND itf2.trx_id = itf3.trx_id
2295: AND NVL(itf3.tax_type_code, 'VAT') = 'VAT'
2296: AND nvl(itf3.tax_rate,0) <> 0

Line 2312: FROM zx_rep_trx_detail_t itf4

2308: itf4.trx_type_id,
2309: itf4.tax_rate,
2310: itf4.tax_rate_id,
2311: null document_sub_type
2312: FROM zx_rep_trx_detail_t itf4
2313: WHERE itf4.request_id = P_REQUEST_ID
2314: AND not exists ( SELECT 1
2315: FROM zx_rep_trx_detail_t itf5
2316: WHERE itf5.request_id = P_REQUEST_ID

Line 2315: FROM zx_rep_trx_detail_t itf5

2311: null document_sub_type
2312: FROM zx_rep_trx_detail_t itf4
2313: WHERE itf4.request_id = P_REQUEST_ID
2314: AND not exists ( SELECT 1
2315: FROM zx_rep_trx_detail_t itf5
2316: WHERE itf5.request_id = P_REQUEST_ID
2317: AND itf5.trx_id = itf4.trx_id
2318: AND itf5.tax = p_vat_tax )
2319: AND itf4.ROWID = ( SELECT Min(itf6.ROWID)

Line 2320: FROM zx_rep_trx_detail_t itf6

2316: WHERE itf5.request_id = P_REQUEST_ID
2317: AND itf5.trx_id = itf4.trx_id
2318: AND itf5.tax = p_vat_tax )
2319: AND itf4.ROWID = ( SELECT Min(itf6.ROWID)
2320: FROM zx_rep_trx_detail_t itf6
2321: WHERE itf4.trx_id = itf6.trx_id
2322: AND itf6.request_id = P_REQUEST_ID )
2323: )order by trx_id , trx_line_id
2324: );

Line 2499: DELETE from zx_rep_trx_detail_t itf

2495: 'After insertion into zx_rep_trx_jx_ext_t ');
2496: END IF;
2497: -- Delete Unwanted lines from Detail ITF
2498:
2499: DELETE from zx_rep_trx_detail_t itf
2500: WHERE itf.request_id = p_request_id
2501: AND NOT EXISTS ( SELECT 1
2502: FROM zx_rep_trx_jx_ext_t ext
2503: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 2508: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

2504:
2505:
2506: IF ( g_level_statement>= g_current_runtime_level ) THEN
2507: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2508: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2509: END IF;
2510:
2511: IF (g_level_procedure >= g_current_runtime_level ) THEN
2512: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 2587: FROM zx_rep_trx_detail_t dtl,

2583: dtl.tax_rate,
2584: dtl.tax_rate_id,
2585: null document_sub_type,
2586: dtl.tax_regime_code
2587: FROM zx_rep_trx_detail_t dtl,
2588: ar_vat_tax_all vat
2589: WHERE dtl.request_id = P_REQUEST_ID
2590: AND nvl(vat.tax_type,'VAT') = 'VAT'
2591: AND dtl.tax_rate_id = vat.vat_tax_id

Line 2614: FROM zx_rep_trx_detail_t itf2

2610: currency_conversion_rate,
2611: trx_type_id,
2612: null tax_rate,
2613: null document_sub_type
2614: FROM zx_rep_trx_detail_t itf2
2615: WHERE itf2.request_id = P_REQUEST_ID
2616: AND nvl(itf2.tax_type_code,'VAT') = 'VAT'
2617: GROUP BY itf2.internal_organization_id,
2618: itf2.trx_id, trx_line_id, trx_number, currency_conversion_rate,trx_type_id */

Line 2632: FROM zx_rep_trx_detail_t itf3

2628: null tax_rate,
2629: itf3.tax_rate_id,
2630: null document_sub_type,
2631: itf3.tax_regime_code
2632: FROM zx_rep_trx_detail_t itf3
2633: WHERE itf3.request_id = P_REQUEST_ID
2634: AND itf3.tax = P_VAT_NON_TAXAB_TAX
2635: AND itf3.tax_rate = 0
2636: GROUP BY itf3.internal_organization_id,

Line 2688: FROM zx_rep_trx_detail_t

2684: l_batch_source_id_tbl,
2685: l_tax_regime_code_tbl,
2686: L_EXCHANGE_RATE_TBL,
2687: l_trx_line_dist_id_tbl
2688: FROM zx_rep_trx_detail_t
2689: WHERE request_id = P_REQUEST_ID
2690: AND NVL(tax_type_code,'VAT') = 'VAT'
2691: AND tax_regime_code = p_tax_regime
2692: and TAX in (P_VAT_TAX,P_VAT_NON_TAXAB_TAX)

Line 3039: /* DELETE from zx_rep_trx_detail_t itf

3035: 'After insertion into zx_rep_trx_jx_ext_t ');
3036: END IF;
3037: -- Delete Unwanted lines from Detail ITF
3038:
3039: /* DELETE from zx_rep_trx_detail_t itf
3040: WHERE itf.request_id = p_request_id
3041: AND NOT EXISTS ( SELECT 1
3042: FROM zx_rep_trx_jx_ext_t ext
3043: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 3050: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

3046: UPDATE_DGI_CURR_CODE(p_request_id);
3047:
3048: IF ( g_level_statement>= g_current_runtime_level ) THEN
3049: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
3050: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3051: END IF;
3052:
3053: IF (g_level_procedure >= g_current_runtime_level ) THEN
3054: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 3095: FROM zx_rep_trx_detail_t itf1,

3091: l_tax_rate_tbl,
3092: l_tax_rate_id_tbl,
3093: l_tax_regime_code_tbl,
3094: l_document_sub_type_tbl
3095: FROM zx_rep_trx_detail_t itf1,
3096: jl_zz_ar_tx_categ categ
3097: WHERE itf1.request_id = P_REQUEST_ID
3098: -- AND itf1.tax_regime_code
3099: AND itf1.tax = categ.tax_category

Line 3202: DELETE from zx_rep_trx_detail_t itf

3198: 'After insertion into zx_rep_trx_jx_ext_t ');
3199: END IF;
3200: -- Delete Unwanted lines from Detail ITF
3201:
3202: DELETE from zx_rep_trx_detail_t itf
3203: WHERE itf.request_id = p_request_id
3204: AND NOT EXISTS ( SELECT 1
3205: FROM zx_rep_trx_jx_ext_t ext
3206: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 3210: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

3206: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
3207:
3208: IF ( g_level_statement>= g_current_runtime_level ) THEN
3209: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
3210: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3211: END IF;
3212:
3213: IF (g_level_procedure >= g_current_runtime_level ) THEN
3214: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 3255: FROM zx_rep_trx_detail_t itf,

3251: l_tax_rate_tbl,
3252: l_document_sub_type_tbl,
3253: l_validation_digit_tbl,
3254: l_trx_line_dist_id_tbl
3255: FROM zx_rep_trx_detail_t itf,
3256: zx_rates_b rates,
3257:
3258:
3259: ra_customer_trx_all ratrx,

Line 3407: DELETE from zx_rep_trx_detail_t itf

3403: 'After insertion into zx_rep_trx_jx_ext_t ');
3404: END IF;
3405: -- Delete Unwanted lines from Detail ITF
3406:
3407: DELETE from zx_rep_trx_detail_t itf
3408: WHERE itf.request_id = p_request_id
3409: AND NOT EXISTS ( SELECT 1
3410: FROM zx_rep_trx_jx_ext_t ext
3411: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 3457: FROM zx_rep_trx_detail_t dtl, --Bug 5396444

3453: l_tax_rate_tbl,
3454: l_tax_rate_id_tbl,
3455: l_document_sub_type_tbl,
3456: l_trx_line_dist_id_tbl
3457: FROM zx_rep_trx_detail_t dtl, --Bug 5396444
3458: ra_cust_trx_types_all tt
3459: WHERE dtl.request_id = P_REQUEST_ID
3460: AND dtl.internal_organization_id = tt.org_id
3461: AND dtl.trx_type_id = tt.cust_trx_type_id

Line 3551: DELETE from zx_rep_trx_detail_t itf

3547: END IF;
3548:
3549: -- Delete Unwanted lines from Detail ITF
3550:
3551: DELETE from zx_rep_trx_detail_t itf
3552: WHERE itf.request_id = p_request_id
3553: AND NOT EXISTS ( SELECT 1
3554: FROM zx_rep_trx_jx_ext_t ext
3555: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 3559: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );

3555: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
3556:
3557: IF ( g_level_statement>= g_current_runtime_level ) THEN
3558: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
3559: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3560: END IF;
3561: EXCEPTION
3562: WHEN OTHERS THEN
3563: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 3616: FROM zx_rep_trx_detail_t dtl

3612: dtl.last_updated_by,
3613: dtl.last_update_date,
3614: dtl.last_update_login,
3615: p_request_id
3616: FROM zx_rep_trx_detail_t dtl
3617: WHERE dtl.request_id = p_request_id
3618: );
3619:
3620: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 3701: FROM zx_rep_trx_detail_t itf

3697: BEGIN
3698: IF P_REPORT_NAME = 'ZXARPVBR' THEN
3699: SELECT SUM(NVL(itf.tax_amt_funcl_curr,itf.tax_amt))
3700: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3701: FROM zx_rep_trx_detail_t itf
3702: WHERE itf.request_id = p_request_id
3703: AND itf.trx_id = p_trx_id_tbl(i)
3704: AND itf.trx_line_id = P_TRX_LINE_ID(i)
3705: --AND itf.detail_tax_line_id = p_detail_tax_line_id(i)

Line 3720: FROM zx_rep_trx_detail_t itf

3716: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'JL_CL_CREDIT_MEMO' THEN
3717:
3718: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3719: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3720: FROM zx_rep_trx_detail_t itf
3721: WHERE itf.request_id = p_request_id
3722: --AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3723: AND itf.trx_id = p_trx_id_tbl(i)
3724: AND itf.trx_line_id = p_trx_line_id(i)

Line 3734: FROM zx_rep_trx_detail_t itf

3730: AND itf.tax_rate <> 0;
3731: ELSE
3732: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3733: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3734: FROM zx_rep_trx_detail_t itf
3735: WHERE itf.request_id = p_request_id
3736: --AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3737: AND itf.trx_id = p_trx_id_tbl(i)
3738: AND itf.trx_line_id = p_trx_line_id(i)

Line 3748: FROM zx_rep_trx_detail_t itf

3744: ELSIF P_REPORT_NAME = 'ZXZZTVSR' THEN
3745:
3746: SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
3747: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3748: FROM zx_rep_trx_detail_t itf
3749: WHERE itf.request_id = p_request_id
3750: AND itf.trx_id = p_trx_id_tbl(i)
3751: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3752: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021

Line 3790: FROM zx_rep_trx_detail_t itf

3786: -- OPEN ISSUE --
3787: --Bug 5438742 : Added VAT Amt logic for the Bug
3788: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3789: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3790: FROM zx_rep_trx_detail_t itf
3791: WHERE itf.request_id = p_request_id
3792: AND itf.trx_id = p_trx_id_tbl(i)
3793: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3794: AND itf.tax_type_code = 'VAT'

Line 3805: FROM zx_rep_trx_detail_t itf

3801: END IF;
3802: ELSIF P_REPORT_NAME = 'ZXCOARSB' THEN --Bug 5396444 : Vat Amt
3803: SELECT sum(nvl(itf.tax_amt,0))
3804: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3805: FROM zx_rep_trx_detail_t itf
3806: WHERE itf.request_id = p_request_id
3807: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3808: AND itf.trx_id = p_trx_id_tbl(i);
3809:

Line 3850: FROM zx_rep_trx_detail_t

3846: BEGIN
3847:
3848: SELECT count(distinct trx_id)
3849: INTO l_trx_counter
3850: FROM zx_rep_trx_detail_t
3851: WHERE request_id = p_request_id;
3852:
3853: IF ( g_level_statement>= g_current_runtime_level ) THEN
3854: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 3916: FROM zx_rep_trx_detail_t itf

3912:
3913: --SELECT SUM(nvl(itf.tax_amt_funcl_curr,0))
3914: SELECT SUM(nvl(itf.tax_amt,0))
3915: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3916: FROM zx_rep_trx_detail_t itf
3917: WHERE itf.request_id = p_request_id
3918: AND itf.trx_id = p_trx_id_tbl(i)
3919: AND nvl(itf.reverse_flag,'N') <> 'Y'
3920: AND itf.tax_type_code = p_vat_tax;

Line 3937: FROM zx_rep_trx_detail_t itf

3933: END IF;
3934:
3935: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
3936: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3937: FROM zx_rep_trx_detail_t itf
3938: WHERE itf.request_id = p_request_id
3939: AND itf.trx_id = p_trx_id_tbl(i)
3940: AND itf.trx_line_id = p_trx_line_id(i)
3941: -- AND itf.detail_tax_line_id = p_detail_tax_line_id(i)

Line 4020: FROM zx_rep_trx_detail_t itf,

4016: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
4017:
4018: SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
4019: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
4020: FROM zx_rep_trx_detail_t itf,
4021: ar_vat_tax_all vat
4022: WHERE itf.request_id = p_request_id
4023: AND itf.trx_id = p_trx_id_tbl(i)
4024: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)

Line 4071: FROM zx_rep_trx_detail_t itf

4067: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
4068:
4069: SELECT sum(abs(nvl(itf.tax_amt,0)))
4070: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
4071: FROM zx_rep_trx_detail_t itf
4072: WHERE itf.request_id = p_request_id
4073: AND itf.trx_id = p_trx_id_tbl(i)
4074: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4075: AND itf.tax_regime_code = p_tax_regime

Line 4258: FROM zx_rep_trx_detail_t itf

4254: IF l_taxable_amt_tbl(k) is NULL THEN
4255: BEGIN
4256: SELECT SUM(NVL(itf.taxable_amt_funcl_curr,itf.taxable_amt))
4257: INTO l_taxable_amt_tbl(k)
4258: FROM zx_rep_trx_detail_t itf
4259: WHERE itf.request_id = p_request_id
4260: AND itf.trx_id = p_trx_id_tbl(i)
4261: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4262: AND itf.trx_line_id = p_trx_line_id(i)

Line 4326: FROM zx_rep_trx_detail_t itf

4322: IF l_taxable_amt_tbl(k) is NULL THEN
4323: BEGIN
4324: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
4325: INTO l_taxable_amt_tbl(k)
4326: FROM zx_rep_trx_detail_t itf
4327: WHERE itf.request_id = p_request_id
4328: AND itf.trx_id = p_trx_id_tbl(i)
4329: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4330: AND itf.trx_line_id = p_trx_line_id(i)

Line 4411: FROM zx_rep_trx_detail_t itf,

4407: NVL(cur.minimum_accountable_unit,power(10,(-1* cur.precision)))
4408: ) *
4409: NVL(cur.minimum_accountable_unit,power(10,(-1 * cur.precision)))
4410: INTO l_taxable_amt_tbl(k)
4411: FROM zx_rep_trx_detail_t itf,
4412: fnd_currencies cur,
4413: gl_sets_of_books sob
4414: WHERE itf.request_id = p_request_id
4415: AND itf.trx_id = p_trx_id_tbl(i)

Line 4430: FROM zx_rep_trx_detail_t itf,

4426: NVL(cur.minimum_accountable_unit,power(10,(-1* cur.precision)))
4427: ) *
4428: NVL(cur.minimum_accountable_unit,power(10,(-1 * cur.precision)))
4429: INTO l_taxable_amt_tbl(k)
4430: FROM zx_rep_trx_detail_t itf,
4431: fnd_currencies cur,
4432: gl_sets_of_books sob
4433: WHERE itf.request_id = p_request_id
4434: AND itf.trx_id = p_trx_id_tbl(i)

Line 4545: FROM zx_rep_trx_detail_t itf

4541:
4542: IF l_gdf_ra_cust_trx_att19_tbl(k) = 'IS_NULL' THEN
4543: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
4544: INTO l_taxable_amt_tbl(k)
4545: FROM zx_rep_trx_detail_t itf
4546: WHERE itf.request_id = p_request_id
4547: AND itf.trx_id = p_trx_id_tbl(i)
4548: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4549: AND itf.trx_line_id = p_trx_line_id(i)

Line 4595: FROM zx_rep_trx_detail_t

4591: ELSE
4592: BEGIN
4593: SELECT count(distinct trx_id)
4594: INTO l_trx_counter
4595: FROM zx_rep_trx_detail_t
4596: WHERE request_id = p_request_id;
4597:
4598: IF ( g_level_statement>= g_current_runtime_level ) THEN
4599: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 4657: FROM zx_rep_trx_detail_t itf

4653: 'get_taxable : '|| to_char(k)||' '||to_char(p_trx_id_tbl(i))||' '||to_char(p_tax_rate_tbl(i)));
4654: END IF;
4655: SELECT sum(nvl(itf.taxable_amt_funcl_curr, itf.taxable_amt))
4656: INTO l_taxable_amt_tbl(k)
4657: FROM zx_rep_trx_detail_t itf
4658: WHERE itf.request_id = p_request_id
4659: AND itf.trx_id = p_trx_id_tbl(i)
4660: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4661: AND itf.trx_line_id = p_trx_line_id(i)

Line 4674: FROM zx_rep_trx_detail_t itf

4670: ELSIF P_REPORT_NAME = 'JLARTPFF' THEN
4671:
4672: SELECT ABS(SUM(itf.taxable_amt_funcl_curr))
4673: INTO l_taxable_amt_tbl(k)
4674: FROM zx_rep_trx_detail_t itf
4675: WHERE itf.request_id = p_request_id
4676: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4677: AND itf.trx_line_id = p_trx_line_id(i)
4678: AND itf.trx_id = p_trx_id_tbl(i);

Line 4718: FROM zx_rep_trx_detail_t itf

4714: IF P_REPORT_NAME= 'JLARTDFF' THEN
4715:
4716: SELECT NVL(sum(abs(nvl(itf.taxable_amt,0))),0)
4717: INTO l_taxable_amt_tbl(k)
4718: FROM zx_rep_trx_detail_t itf
4719: WHERE itf.request_id = p_request_id
4720: AND itf.trx_id = p_trx_id_tbl(i)
4721: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4722: AND itf.trx_line_id = p_trx_line_id(i)

Line 4732: FROM zx_rep_trx_detail_t itf

4728: ELSIF P_REPORT_NAME IN ('JLARTSFF','JLZZTCFF') THEN
4729:
4730: SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt)),0)
4731: INTO l_taxable_amt_tbl(k)
4732: FROM zx_rep_trx_detail_t itf
4733: WHERE itf.request_id = p_request_id
4734: AND itf.trx_id = p_trx_id_tbl(i)
4735: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4736: AND itf.trx_line_id = p_trx_line_id(i)

Line 4837: FROM zx_rep_trx_detail_t

4833: BEGIN
4834:
4835: SELECT count(distinct trx_id)
4836: INTO l_trx_counter
4837: FROM zx_rep_trx_detail_t
4838: WHERE request_id = p_request_id;
4839:
4840: IF ( g_level_statement>= g_current_runtime_level ) THEN
4841: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 4896: FROM zx_rep_trx_detail_t itf

4892: IF P_REPORT_NAME in ('ZXARPVBR','JLARPPFF') THEN
4893:
4894: SELECT SUM(NVL(itf.taxable_amt_funcl_curr,itf.taxable_amt))
4895: INTO l_non_taxable_amt_tbl(k)
4896: FROM zx_rep_trx_detail_t itf
4897: WHERE itf.request_id = p_request_id
4898: AND itf.trx_id = p_trx_id_tbl(i)
4899: AND itf.trx_line_id = p_trx_line_id(i)
4900: AND itf.tax_type_code = p_non_taxab_tax;

Line 4953: FROM zx_rep_trx_detail_t itf

4949:
4950: IF P_REPORT_NAME = 'JLARTDFF' THEN
4951: SELECT sum(nvl(itf.tax_amt ,0))
4952: INTO l_non_taxable_amt_tbl(p_trx_id_tbl(i))
4953: FROM zx_rep_trx_detail_t itf
4954: WHERE itf.request_id = p_request_id
4955: AND itf.trx_id = p_trx_id_tbl(i)
4956: AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
4957: AND itf.tax = p_non_taxab_tax;

Line 4961: FROM zx_rep_trx_detail_t itf

4957: AND itf.tax = p_non_taxab_tax;
4958: ELSIF P_REPORT_NAME in ('JLARTSFF','JLZZTCFF') THEN
4959: SELECT sum(nvl(itf.tax_amt ,0))
4960: INTO l_non_taxable_amt_tbl(p_trx_id_tbl(i))
4961: FROM zx_rep_trx_detail_t itf
4962: WHERE itf.request_id = p_request_id
4963: AND itf.trx_id = p_trx_id_tbl(i)
4964: AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
4965: AND itf.tax = p_non_taxab_tax;

Line 5064: FROM zx_rep_trx_detail_t

5060: BEGIN
5061:
5062: SELECT count(distinct trx_id)
5063: INTO l_trx_counter
5064: FROM zx_rep_trx_detail_t
5065: WHERE request_id = p_request_id;
5066:
5067: IF ( g_level_statement>= g_current_runtime_level ) THEN
5068: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5106: FROM zx_rep_trx_detail_t itf

5102: IF P_REPORT_NAME IN ('ZXZZTVSR','JLARTSFF','JLZZTCFF') THEN
5103:
5104: SELECT SUM(NVL(itf.tax_amt_funcl_curr,itf.tax_amt))
5105: INTO l_vat_additional_amt_tbl(p_trx_id_tbl(i))
5106: FROM zx_rep_trx_detail_t itf
5107: WHERE itf.request_id = p_request_id
5108: AND itf.trx_id = p_trx_id_tbl(i)
5109: -- AND itf.tax_rate_id = p_tax_rate_id_tbl(i)
5110: ANd itf.tax = p_vat_addit_tax;

Line 5224: FROM zx_rep_trx_detail_t

5220: BEGIN
5221:
5222: SELECT count(distinct trx_id)
5223: INTO l_trx_counter
5224: FROM zx_rep_trx_detail_t
5225: WHERE request_id = p_request_id;
5226:
5227: IF ( g_level_statement>= g_current_runtime_level ) THEN
5228: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5303: FROM ZX_REP_TRX_DETAIL_T itf

5299: END IF;
5300:
5301: SELECT SUM(NVL(itf.taxable_amt_funcl_curr,itf.taxable_amt))
5302: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5303: FROM ZX_REP_TRX_DETAIL_T itf
5304: WHERE itf.request_id = p_request_id
5305: AND itf.trx_id = p_trx_id_tbl(i)
5306: AND itf.trx_line_id = p_trx_line_id_tbl(i)
5307: AND itf.detail_tax_line_id = P_DETAIL_TAX_LINE_ID(i)

Line 5349: FROM zx_rep_trx_detail_t itf,

5345: NVL(cur.minimum_accountable_unit,power(10,(-1* cur.precision)))
5346: ) *
5347: NVL(cur.minimum_accountable_unit,power(10,(-1 * cur.precision)))
5348: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5349: FROM zx_rep_trx_detail_t itf,
5350: fnd_currencies cur,
5351: gl_sets_of_books sob
5352: WHERE itf.request_id = p_request_id
5353: AND itf.trx_id = p_trx_id_tbl(i)

Line 5371: FROM zx_rep_trx_detail_t itf,

5367: NVL(cur.minimum_accountable_unit,power(10,(-1* cur.precision)))
5368: ) *
5369: NVL(cur.minimum_accountable_unit,power(10,(-1 * cur.precision)))
5370: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5371: FROM zx_rep_trx_detail_t itf,
5372: fnd_currencies cur,
5373: gl_sets_of_books sob
5374: WHERE itf.request_id = p_request_id
5375: AND itf.trx_id = p_trx_id_tbl(i)

Line 5419: FROM zx_rep_trx_detail_t itf

5415: IF l_vat_0_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
5416:
5417: SELECT sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
5418: INTO l_vat_0_amt_tbl(p_trx_id_tbl(i))
5419: FROM zx_rep_trx_detail_t itf
5420: WHERE itf.request_id = p_request_id
5421: AND itf.trx_id = p_trx_id_tbl(i)
5422: AND nvl(itf.tax_type_code,'VAT') = 'VAT'
5423: AND itf.tax_rate = 0

Line 5442: FROM zx_rep_trx_detail_t itf

5438: IF l_no_vat_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
5439:
5440: SELECT sum(nvl(itf.taxable_amt_funcl_curr,0))
5441: INTO l_no_vat_amt_tbl(p_trx_id_tbl(i))
5442: FROM zx_rep_trx_detail_t itf
5443: WHERE itf.request_id = p_request_id
5444: AND itf.trx_id = p_trx_id_tbl(i)
5445: AND itf.tax_rate = 0
5446: AND ( itf.tax_regime_code <> p_tax_regime OR itf.tax <> p_vat_tax )

Line 5515: FROM zx_rep_trx_detail_t itf

5511:
5512: SELECT NVL(sum(abs(nvl(itf.taxable_amt,0))),0)
5513: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5514: --INTO l_vat_exempt_amt_tbl(k)
5515: FROM zx_rep_trx_detail_t itf
5516: WHERE itf.request_id = p_request_id
5517: AND itf.trx_id = p_trx_id_tbl(i)
5518: AND itf.trx_line_id = p_trx_line_id_tbl(i) -- new join
5519: AND nvl(itf.tax_type_code,'VAT') = 'VAT'

Line 5527: FROM zx_rep_trx_detail_t itf

5523:
5524: SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,0)),0)
5525: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5526: --INTO l_vat_exempt_amt_tbl(k)
5527: FROM zx_rep_trx_detail_t itf
5528: WHERE itf.request_id = p_request_id
5529: AND itf.trx_id = p_trx_id_tbl(i)
5530: AND itf.trx_line_id = p_trx_line_id_tbl(i) -- new join
5531: AND nvl(itf.tax_type_code,'VAT') = 'VAT'

Line 5579: FROM zx_rep_trx_detail_t itf

5575: (nvl(itf.EXEMPT_RATE_MODIFIER,0) * coalesce(itf.taxable_amt_funcl_curr,itf.taxable_amt,0))
5576: )
5577: ),0)
5578: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5579: FROM zx_rep_trx_detail_t itf
5580: WHERE itf.request_id = p_request_id
5581: AND itf.trx_line_id = p_trx_line_id_tbl(i)
5582: AND itf.trx_id = p_trx_id_tbl(i);
5583:

Line 5696: FROM zx_rep_trx_detail_t

5692: BEGIN
5693:
5694: SELECT count(distinct trx_id)
5695: INTO l_trx_counter
5696: FROM zx_rep_trx_detail_t
5697: WHERE request_id = p_request_id;
5698:
5699: IF ( g_level_statement>= g_current_runtime_level ) THEN
5700: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5733: FROM zx_rep_trx_detail_t itf

5729: IF l_vat_perc_amt_tbl(k) is NULL THEN
5730:
5731: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5732: INTO l_vat_perc_amt_tbl(k)
5733: FROM zx_rep_trx_detail_t itf
5734: WHERE itf.request_id = p_request_id
5735: AND itf.trx_id = p_trx_id_tbl(i)
5736: AND itf.trx_line_id = p_trx_line_id_tbl(i)
5737: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021

Line 5813: FROM zx_rep_trx_detail_t itf

5809: END IF;
5810:
5811: SELECT SUM(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
5812: INTO l_vat_perc_amt_tbl(k)
5813: FROM zx_rep_trx_detail_t itf
5814: WHERE itf.request_id = p_request_id
5815: AND itf.trx_id = p_trx_id_tbl(i)
5816: AND itf.tax_type_code = P_VAT_PERC_TAX;
5817:

Line 5823: FROM zx_rep_trx_detail_t itf

5819:
5820: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
5821: -- nvl(SUM(itf.tax_amt),0)
5822: INTO l_vat_perc_amt_tbl(k)
5823: FROM zx_rep_trx_detail_t itf
5824: WHERE itf.request_id = p_request_id
5825: AND itf.trx_id = p_trx_id_tbl(i)
5826: AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
5827: AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);

Line 5833: FROM zx_rep_trx_detail_t itf

5829: ELSIF P_REPORT_NAME in ('ZXZZTVSR') THEN
5830:
5831: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
5832: INTO l_vat_perc_amt_tbl(k)
5833: FROM zx_rep_trx_detail_t itf
5834: WHERE itf.request_id = p_request_id
5835: AND itf.trx_id = p_trx_id_tbl(i)
5836: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
5837: AND itf.tax = p_vat_perc_tax

Line 5882: FROM zx_rep_trx_detail_t itf

5878: IF P_REPORT_NAME = 'JLARTDFF' THEN
5879:
5880: SELECT sum(abs(nvl(itf.tax_amt,0)))
5881: INTO l_vat_perc_amt_tbl(k)
5882: FROM zx_rep_trx_detail_t itf
5883: WHERE itf.request_id = p_request_id
5884: AND itf.trx_id = p_trx_id_tbl(i)
5885: AND itf.tax_regime_code = p_tax_regime
5886: AND itf.tax = p_vat_perc_tax

Line 5893: FROM zx_rep_trx_detail_t itf

5889: ELSIF P_REPORT_NAME IN ('JLARTSFF','JLZZTCFF') THEN
5890:
5891: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5892: INTO l_vat_perc_amt_tbl(k)
5893: FROM zx_rep_trx_detail_t itf
5894: WHERE itf.request_id = p_request_id
5895: AND itf.trx_id = p_trx_id_tbl(i)
5896: AND itf.tax_regime_code = p_tax_regime
5897: AND itf.tax = p_vat_perc_tax

Line 5944: FROM zx_rep_trx_detail_t itf

5940:
5941:
5942: SELECT nvl(SUM(itf.tax_amt),0)
5943: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
5944: FROM zx_rep_trx_detail_t itf
5945: WHERE itf.request_id = p_request_id
5946: AND itf.trx_id = p_trx_id_tbl(i)
5947: AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
5948: AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);

Line 5954: FROM zx_rep_trx_detail_t itf

5950: ELSIF P_REPORT_NAME in ('ZXZZTVSR') THEN
5951:
5952: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5953: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
5954: FROM zx_rep_trx_detail_t itf
5955: WHERE itf.request_id = p_request_id
5956: AND itf.trx_id = p_trx_id_tbl(i)
5957: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
5958: AND itf.tax = p_vat_perc_tax

Line 6001: FROM zx_rep_trx_detail_t itf

5997: -- IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
5998:
5999: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
6000: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
6001: FROM zx_rep_trx_detail_t itf
6002: WHERE itf.request_id = p_request_id
6003: AND itf.trx_id = p_trx_id_tbl(i)
6004: AND itf.tax_regime_code = p_tax_regime
6005: AND itf.tax = p_vat_perc_tax

Line 6113: FROM zx_rep_trx_detail_t

6109: BEGIN
6110:
6111: SELECT count(distinct trx_id)
6112: INTO l_trx_counter
6113: FROM zx_rep_trx_detail_t
6114: WHERE request_id = p_request_id;
6115:
6116: IF ( g_level_statement>= g_current_runtime_level ) THEN
6117: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6151: FROM zx_rep_trx_detail_t itf

6147:
6148: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
6149: -- NVL(SUM(itf.tax_amt),0)
6150: INTO l_other_fed_perc_amt_tbl(p_trx_id_tbl(i))
6151: FROM zx_rep_trx_detail_t itf
6152: WHERE itf.request_id = p_request_id
6153: AND itf.trx_id = p_trx_id_tbl(i)
6154: AND itf.tax_type_code >= P_FED_PERC_TAX_TYPE_FROM
6155: AND itf.tax_type_code <= NVL(P_FED_PERC_TAX_TYPE_TO, P_FED_PERC_TAX_TYPE_FROM);

Line 6257: FROM zx_rep_trx_detail_t

6253: BEGIN
6254:
6255: SELECT count(distinct trx_id)
6256: INTO l_trx_counter
6257: FROM zx_rep_trx_detail_t
6258: WHERE request_id = p_request_id;
6259:
6260: IF ( g_level_statement>= g_current_runtime_level ) THEN
6261: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6286: FROM zx_rep_trx_detail_t itf

6282: BEGIN
6283: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'DOCUMENT TYPE.JLCLPPLR' THEN
6284: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6285: INTO X_OTHER_TAX_AMT_TBL(i)
6286: FROM zx_rep_trx_detail_t itf
6287: WHERE itf.request_id = P_REQUEST_ID
6288: --AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
6289: AND itf.trx_id = p_trx_id_tbl(i)
6290: AND itf.trx_line_id = p_trx_line_id(i)

Line 6299: FROM zx_rep_trx_detail_t itf

6295: AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y');
6296: ELSE
6297: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6298: INTO X_OTHER_TAX_AMT_TBL(i)
6299: FROM zx_rep_trx_detail_t itf
6300: WHERE itf.request_id = p_request_id
6301: --AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
6302: AND itf.trx_id = p_trx_id_tbl(i)
6303: AND itf.trx_line_id = p_trx_line_id(i)

Line 6369: FROM zx_rep_trx_detail_t itf

6365: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
6366: --Bug 5438742
6367: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6368: INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
6369: FROM zx_rep_trx_detail_t itf
6370: WHERE itf.request_id = P_REQUEST_ID
6371: AND itf.trx_id = p_trx_id_tbl(i)
6372: AND itf.tax_type_code <> 'VAT' ;
6373: ELSE

Line 6471: FROM zx_rep_trx_detail_t

6467: BEGIN
6468:
6469: SELECT count(distinct trx_id)
6470: INTO l_trx_counter
6471: FROM zx_rep_trx_detail_t
6472: WHERE request_id = p_request_id;
6473:
6474: IF ( g_level_statement>= g_current_runtime_level ) THEN
6475: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6549: FROM zx_rep_trx_detail_t itf

6545: IF P_REPORT_NAME = 'JLARTDFF' THEN
6546:
6547: SELECT sum(abs(nvl(itf.tax_amt,0)))
6548: INTO l_vat_addit_tax_amt_tbl(p_trx_id_tbl(i))
6549: FROM zx_rep_trx_detail_t itf
6550: WHERE itf.request_id = p_request_id
6551: AND itf.trx_id = p_trx_id_tbl(i)
6552: AND itf.tax_regime_code = p_tax_regime
6553: AND itf.tax = p_vat_addit_tax

Line 6559: FROM zx_rep_trx_detail_t itf

6555: ELSIF P_REPORT_NAME in ('JLARTSFF','JLZZTCFF') THEN
6556:
6557: SELECT sum(nvl(itf.tax_amt,0))
6558: INTO l_vat_addit_tax_amt_tbl(p_trx_id_tbl(i))
6559: FROM zx_rep_trx_detail_t itf
6560: WHERE itf.request_id = p_request_id
6561: AND itf.trx_id = p_trx_id_tbl(i)
6562: AND itf.tax_regime_code = p_tax_regime
6563: AND itf.tax = p_vat_addit_tax

Line 6578: FROM zx_rep_trx_detail_t itf

6574: -- IF l_not_categ_tax_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
6575: BEGIN
6576: SELECT sum(nvl(itf.tax_amt,0))
6577: INTO l_not_categ_tax_amt_tbl(p_trx_id_tbl(i))
6578: FROM zx_rep_trx_detail_t itf
6579: WHERE itf.request_id = p_request_id
6580: AND itf.trx_id = p_trx_id_tbl(i)
6581: AND itf.tax_regime_code = p_tax_regime
6582: AND itf.tax = p_vat_not_categ_tax

Line 6709: FROM zx_rep_trx_detail_t

6705: BEGIN
6706:
6707: SELECT count(distinct trx_id)
6708: INTO l_trx_counter
6709: FROM zx_rep_trx_detail_t
6710: WHERE request_id = p_request_id;
6711:
6712: l_count := p_trx_id_tbl.count;
6713:

Line 6755: FROM zx_rep_trx_detail_t itf

6751:
6752:
6753: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt)) -- NVL(SUM(itf.tax_amt),0)
6754: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6755: FROM zx_rep_trx_detail_t itf
6756: WHERE itf.request_id = p_request_id
6757: AND itf.trx_id = p_trx_id_tbl(i)
6758: AND itf.tax_type_code >= P_PROV_TAX_TYPE_FROM
6759: AND itf.tax_type_code <= P_PROV_TAX_TYPE_TO;

Line 6791: FROM zx_rep_trx_detail_t itf

6787:
6788: IF P_REPORT_NAME = 'JLARTDFF' THEN
6789: SELECT sum(abs(nvl(itf.tax_amt,0)))
6790: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6791: FROM zx_rep_trx_detail_t itf
6792: WHERE itf.request_id = p_request_id
6793: AND itf.trx_id = p_trx_id_tbl(i)
6794: AND itf.tax_regime_code = p_prov_tax_regime
6795: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 6799: FROM zx_rep_trx_detail_t itf

6795: AND nvl(itf.tax_type_code,'VAT') = 'VAT';
6796: ELSIF P_REPORT_NAME in ('JLARTSFF','JLZZTCFF') THEN
6797: SELECT sum(nvl(itf.tax_amt,0))
6798: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6799: FROM zx_rep_trx_detail_t itf
6800: WHERE itf.request_id = p_request_id
6801: AND itf.trx_id = p_trx_id_tbl(i)
6802: AND itf.tax_regime_code = p_prov_tax_regime
6803: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 6821: FROM zx_rep_trx_detail_t itf,

6817: END IF;
6818:
6819: SELECT sum(nvl(itf.tax_amt,0))
6820: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6821: FROM zx_rep_trx_detail_t itf,
6822: jl_zz_ar_tx_categ categ
6823: WHERE itf.request_id = p_request_id
6824: AND itf.trx_id = p_trx_id_tbl(i)
6825: -- AND itf.tax_regime_code = p_prov_tax_regime

Line 6933: FROM zx_rep_trx_detail_t

6929: BEGIN
6930:
6931: SELECT count(distinct trx_id)
6932: INTO l_trx_counter
6933: FROM zx_rep_trx_detail_t
6934: WHERE request_id = p_request_id;
6935:
6936: IF ( g_level_statement>= g_current_runtime_level ) THEN
6937: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6969: FROM zx_rep_trx_detail_t itf

6965: IF P_REPORT_NAME = 'JLARPPFF' THEN
6966:
6967: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt)) -- nvl(SUM(itf.tax_amt),0)
6968: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
6969: FROM zx_rep_trx_detail_t itf
6970: WHERE itf.request_id = p_request_id
6971: AND itf.trx_id = p_trx_id_tbl(i)
6972: AND itf.tax_type_code >= P_MUN_TAX_TYPE_FROM
6973: AND itf.tax_type_code <= P_MUN_TAX_TYPE_TO;

Line 7008: FROM zx_rep_trx_detail_t itf

7004: IF P_REPORT_NAME = 'JLARTDFF' THEN
7005:
7006: SELECT sum(abs(nvl(itf.tax_amt,0)))
7007: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
7008: FROM zx_rep_trx_detail_t itf
7009: WHERE itf.request_id = p_request_id
7010: AND itf.trx_id = p_trx_id_tbl(i)
7011: AND itf.tax_regime_code = p_mun_tax_regime
7012: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 7016: FROM zx_rep_trx_detail_t itf

7012: AND nvl(itf.tax_type_code,'VAT') = 'VAT';
7013: ELSIF P_REPORT_NAME in ('JLARTSFF','JLZZTCFF') THEN
7014: SELECT sum(nvl(itf.tax_amt,0))
7015: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
7016: FROM zx_rep_trx_detail_t itf
7017: WHERE itf.request_id = p_request_id
7018: AND itf.trx_id = p_trx_id_tbl(i)
7019: AND itf.tax_regime_code = p_mun_tax_regime
7020: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 7039: FROM zx_rep_trx_detail_t itf,

7035: l_municipal_perc_amt_tbl(p_trx_id_tbl(i)):= NULL;
7036:
7037: SELECT sum(nvl(itf.tax_amt,0))
7038: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
7039: FROM zx_rep_trx_detail_t itf,
7040: jl_zz_ar_tx_categ categ
7041: WHERE itf.request_id = p_request_id
7042: AND itf.trx_id = p_trx_id_tbl(i)
7043: -- AND itf.tax_regime_code = p_mun_tax_regime

Line 7062: FROM zx_rep_trx_detail_t itf

7058:
7059:
7060: /* SELECT sum(nvl(itf.tax_amt,0))
7061: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
7062: FROM zx_rep_trx_detail_t itf
7063: WHERE itf.request_id = p_request_id
7064: AND itf.trx_id = p_trx_id_tbl(i)
7065: AND itf.tax_regime_code = p_mun_tax_regime
7066: AND nvl(itf.tax_type_code,'VAT') = 'VAT'

Line 7163: FROM zx_rep_trx_detail_t

7159: BEGIN
7160:
7161: SELECT count(distinct trx_id)
7162: INTO l_trx_counter
7163: FROM zx_rep_trx_detail_t
7164: WHERE request_id = p_request_id;
7165:
7166: IF ( g_level_statement>= g_current_runtime_level ) THEN
7167: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 7197: FROM zx_rep_trx_detail_t itf

7193: IF P_REPORT_NAME = 'JLARPPFF' THEN
7194:
7195: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt)) -- nvl(SUM(itf.tax_amt),0)
7196: INTO l_excise_amt_tbl(p_trx_id_tbl(i))
7197: FROM zx_rep_trx_detail_t itf
7198: WHERE itf.request_id = p_request_id
7199: AND itf.trx_id = p_trx_id_tbl(i)
7200: AND itf.tax_type_code >= P_EXC_TAX_TYPE_FROM
7201: AND itf.tax_type_code <= P_EXC_TAX_TYPE_TO;

Line 7243: FROM zx_rep_trx_detail_t itf

7239: IF P_REPORT_NAME = 'JLARTDFF' THEN
7240:
7241: SELECT sum(abs(nvl(itf.tax_amt,0)))
7242: INTO l_excise_amt_tbl(p_trx_id_tbl(i))
7243: FROM zx_rep_trx_detail_t itf
7244: WHERE itf.request_id = p_request_id
7245: AND itf.trx_id = p_trx_id_tbl(i)
7246: --AND itf.tax_regime_code = p_exc_tax_regime
7247: AND itf.tax = p_exc_tax_regime

Line 7252: FROM zx_rep_trx_detail_t itf

7248: AND nvl(itf.tax_type_code,'VAT') = 'VAT';
7249: ELSIF P_REPORT_NAME in ('JLARTSFF','JLZZTCFF') THEN
7250: SELECT sum(nvl(itf.tax_amt,0))
7251: INTO l_excise_amt_tbl(p_trx_id_tbl(i))
7252: FROM zx_rep_trx_detail_t itf
7253: WHERE itf.request_id = p_request_id
7254: AND itf.trx_id = p_trx_id_tbl(i)
7255: --AND itf.tax_regime_code = p_exc_tax_regime
7256: AND itf.tax = p_exc_tax_regime

Line 7372: FROM ZX_REP_TRX_DETAIL_T DET

7368: coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0) TAX,
7369: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0) TXBL,
7370: ROW_NUMBER() OVER (PARTITION BY trx_id,trx_line_id
7371: ORDER BY taxable_item_source_id) AS trx_line_change
7372: FROM ZX_REP_TRX_DETAIL_T DET
7373: WHERE DET.REQUEST_ID = P_REQUEST_ID
7374: )
7375: )
7376: GROUP BY DOCUMENT_SUB_TYPE;

Line 7410: FROM ZX_REP_TRX_DETAIL_T DET

7406: coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0)))
7407: INTO X_CL_NUM_OF_DOC_TBL(i),
7408: X_CL_TOTAL_VAT_TAX_TBL(i),
7409: X_CL_TOTAL_OTHER_TAX_TBL(i)
7410: FROM ZX_REP_TRX_DETAIL_T DET
7411: WHERE DET.REQUEST_ID = P_REQUEST_ID
7412: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7413: GROUP BY DET.DOCUMENT_SUB_TYPE;
7414: EXCEPTION

Line 7431: FROM ZX_REP_TRX_DETAIL_T DET

7427: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
7428: INTO
7429: X_CL_TOTAL_EXEMPT_TBL(i),
7430: X_CL_TOTAL_EFFECTIVE_TBL(i)
7431: FROM ZX_REP_TRX_DETAIL_T DET
7432: WHERE DET.REQUEST_ID = P_REQUEST_ID
7433: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7434: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7435: WHERE det1.trx_id = det.trx_id

Line 7434: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1

7430: X_CL_TOTAL_EFFECTIVE_TBL(i)
7431: FROM ZX_REP_TRX_DETAIL_T DET
7432: WHERE DET.REQUEST_ID = P_REQUEST_ID
7433: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7434: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7435: WHERE det1.trx_id = det.trx_id
7436: AND det1.request_id = P_REQUEST_ID
7437: AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
7438: GROUP BY DET.DOCUMENT_SUB_TYPE;

Line 7559: FROM ZX_REP_TRX_DETAIL_T DET

7555: coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0)))
7556: INTO X_CL_NUM_OF_DOC_TBL(i),
7557: X_CL_TOTAL_VAT_TAX_TBL(i),
7558: X_CL_TOTAL_OTHER_TAX_TBL(i)
7559: FROM ZX_REP_TRX_DETAIL_T DET
7560: WHERE DET.REQUEST_ID = P_REQUEST_ID
7561: AND DET.doc_seq_name = P_DOCUMENT_SUB_TYPE_TBL(i)
7562: GROUP BY DET.DOCUMENT_SUB_TYPE;
7563: EXCEPTION

Line 7577: FROM ZX_REP_TRX_DETAIL_T DET

7573: 0,
7574: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
7575: INTO
7576: X_CL_TOTAL_EFFECTIVE_TBL(i)
7577: FROM ZX_REP_TRX_DETAIL_T DET
7578: WHERE DET.REQUEST_ID = P_REQUEST_ID
7579: AND DET.DOC_SEQ_NAME = P_DOCUMENT_SUB_TYPE_TBL(i)
7580: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7581: WHERE det1.trx_id = det.trx_id

Line 7580: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1

7576: X_CL_TOTAL_EFFECTIVE_TBL(i)
7577: FROM ZX_REP_TRX_DETAIL_T DET
7578: WHERE DET.REQUEST_ID = P_REQUEST_ID
7579: AND DET.DOC_SEQ_NAME = P_DOCUMENT_SUB_TYPE_TBL(i)
7580: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7581: WHERE det1.trx_id = det.trx_id
7582: AND det1.request_id = P_REQUEST_ID
7583: AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
7584: GROUP BY DET.DOCUMENT_SUB_TYPE;

Line 7718: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

7714: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
7715: | |
7716: | Parameters : |
7717: | IN : p_vat_tax IN |
7718: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
7719: | |
7720: | MODIFICATION HISTORY |
7721: | 07-Nov-03 Hidetaka Kojima created |
7722: | |

Line 7729: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

7725:
7726:
7727: FUNCTION GET_TAX_AUTHORITY_CODE
7728: (
7729: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
7730: P_ORG_ID IN NUMBER
7731: )
7732: return ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE IS
7733:

Line 7914: FROM zx_rep_trx_detail_t det,

7910: LOOP
7911: BEGIN
7912: SELECT substr(cust.global_attribute12,1,1)
7913: INTO x_validation_digit_tbl(i)
7914: FROM zx_rep_trx_detail_t det,
7915: hz_cust_accounts cust
7916: WHERE det.request_id = p_request_id
7917: AND det.trx_id = p_trx_id_tbl(i)
7918: AND cust.cust_account_id = nvl( det.shipping_trading_partner_id,det.billing_trading_partner_id )

Line 7999: FROM zx_rep_trx_detail_t

7995: BEGIN
7996:
7997: SELECT count(distinct trx_id)
7998: INTO l_counted_trx_number
7999: FROM zx_rep_trx_detail_t
8000: WHERE request_id = p_request_id;
8001:
8002: EXCEPTION
8003:

Line 8176: FROM zx_rep_trx_detail_t

8172:
8173: BEGIN
8174: SELECT count(distinct trx_id)
8175: INTO l_counted_trx_number
8176: FROM zx_rep_trx_detail_t
8177: WHERE request_id = p_request_id;
8178: EXCEPTION
8179: WHEN OTHERS THEN
8180: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 8314: FROM zx_rep_trx_detail_t

8310: BEGIN
8311:
8312: SELECT count(distinct trx_id)
8313: INTO l_counted_trx_number
8314: FROM zx_rep_trx_detail_t
8315: WHERE request_id = p_request_id;
8316:
8317: IF (g_level_procedure >= g_current_runtime_level ) THEN
8318: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.GET_VAT_REG_STAT_CODE.BEGIN',

Line 8477: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

8473: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
8474: | |
8475: | Parameters : |
8476: | IN : p_vat_tax IN |
8477: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
8478: | |
8479: | MODIFICATION HISTORY |
8480: | 07-Nov-03 Hidetaka Kojima created |
8481: | |

Line 8488: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8484:
8485:
8486: PROCEDURE GET_REC_COUNT
8487: (
8488: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8489: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8490: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8491: P_REQUEST_ID IN NUMBER,
8492: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL

Line 8489: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

8485:
8486: PROCEDURE GET_REC_COUNT
8487: (
8488: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8489: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8490: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8491: P_REQUEST_ID IN NUMBER,
8492: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL
8493: )

Line 8520: FROM zx_rep_trx_detail_t

8516: BEGIN
8517:
8518: SELECT count(distinct tax_rate)
8519: INTO l_rec_count_tbl(p_trx_id_tbl(i))
8520: FROM zx_rep_trx_detail_t
8521: WHERE request_id = p_request_id
8522: AND trx_id = p_trx_id_tbl(i)
8523: AND tax_regime_code = p_tax_regime
8524: AND tax = p_vat_tax;

Line 8655: UPDATE zx_rep_trx_detail_t dtl

8651: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.UPDATE_DGI_CURR_CODE.END',
8652: 'UPDATE_DGI_CURR_CODE(+)');
8653: END IF;
8654:
8655: UPDATE zx_rep_trx_detail_t dtl
8656: SET gdf_fnd_currencies_att1= (SELECT global_attribute1
8657: FROM fnd_currencies
8658: WHERE currency_code = dtl.trx_currency_code)
8659: WHERE request_id = p_request_id;

Line 8682: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

8678: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
8679: | |
8680: | Parameters : |
8681: | IN : p_vat_tax IN |
8682: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
8683: | |
8684: | MODIFICATION HISTORY |
8685: | 07-Nov-03 Hidetaka Kojima created |
8686: | |

Line 8693: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8689:
8690:
8691: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8692: (
8693: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8694: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8695: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8696: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8697: P_REQUEST_ID IN NUMBER,

Line 8694: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8690:
8691: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8692: (
8693: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8694: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8695: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8696: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8697: P_REQUEST_ID IN NUMBER,
8698: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL

Line 8695: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

8691: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8692: (
8693: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8694: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8695: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8696: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8697: P_REQUEST_ID IN NUMBER,
8698: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL
8699: )

Line 8726: FROM zx_rep_trx_detail_t t1

8722: BEGIN
8723:
8724: SELECT count(distinct t1.tax_rate)
8725: INTO l_rate_count_tbl(p_trx_id_tbl(i))
8726: FROM zx_rep_trx_detail_t t1
8727: WHERE t1.request_id = p_request_id
8728: AND t1.trx_id = p_trx_id_tbl(i)
8729: AND NVL(t1.TAX_TYPE_CODE, 'VAT') = 'VAT'
8730: AND tax_regime_code = p_tax_regime

Line 8733: FROM zx_rep_trx_detail_t t2

8729: AND NVL(t1.TAX_TYPE_CODE, 'VAT') = 'VAT'
8730: AND tax_regime_code = p_tax_regime
8731: AND tax in (p_vat_tax, p_vat_non_tax)
8732: AND EXISTS ( SELECT 1
8733: FROM zx_rep_trx_detail_t t2
8734: WHERE t2.request_id = p_request_id
8735: AND t2.trx_id = t1.trx_id
8736: AND t2.tax_rate = 0
8737: AND t2.tax_regime_code = p_tax_regime

Line 9969: FROM ZX_REP_TRX_DETAIL_T DET

9965: SELECT SUM(DECODE(DET.TAX_RATE,0,
9966: 0,
9967: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
9968: INTO l_total_doc_taxab_amt_tbl(p_trx_id_tbl(i))
9969: FROM ZX_REP_TRX_DETAIL_T DET
9970: WHERE REQUEST_ID = P_REQUEST_ID
9971: AND TRX_ID = p_trx_id_tbl(i);
9972:
9973: X_TOTAL_DOC_TAXAB_AMT_TBL(i) := l_total_doc_taxab_amt_tbl(p_trx_id_tbl(i));