eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ZX_JL_EXTRACT_PKG dependencies on ZX_REP_TRX_DETAIL_T

Line 338: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

334: );
335:
336: FUNCTION GET_TAX_AUTHORITY_CODE
337: (
338: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
339: P_ORG_ID IN NUMBER
340: )
341: return ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE;
342:

Line 457: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

453: );
454:
455: PROCEDURE GET_REC_COUNT
456: (
457: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
458: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
459: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
460: P_REQUEST_ID IN NUMBER,
461: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL

Line 458: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

454:
455: PROCEDURE GET_REC_COUNT
456: (
457: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
458: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
459: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
460: P_REQUEST_ID IN NUMBER,
461: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL
462: );

Line 466: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

462: );
463:
464: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
465: (
466: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
467: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
468: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
469: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
470: P_REQUEST_ID IN NUMBER,

Line 467: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

463:
464: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
465: (
466: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
467: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
468: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
469: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
470: P_REQUEST_ID IN NUMBER,
471: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL

Line 468: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

464: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
465: (
466: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
467: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
468: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
469: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
470: P_REQUEST_ID IN NUMBER,
471: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL
472: );

Line 530: DELETE from ZX_REP_TRX_DETAIL_T DET

526: END IF;
527:
528: IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JLARTPFF' THEN
529:
530: DELETE from ZX_REP_TRX_DETAIL_T DET
531: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
532: AND DET.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
533: /* DELETE from ZX_REP_TRX_DETAIL_T DET
534: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and

Line 533: /* DELETE from ZX_REP_TRX_DETAIL_T DET

529:
530: DELETE from ZX_REP_TRX_DETAIL_T DET
531: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
532: AND DET.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
533: /* DELETE from ZX_REP_TRX_DETAIL_T DET
534: WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
535: NOT EXISTS
536: (SELECT 1
537: FROM jl_zz_ar_tx_categ_all catg,

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

551: AND det.tax = P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX);
552: */
553: IF ( g_level_statement>= g_current_runtime_level ) THEN
554: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES',
555: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
556: END IF;
557: END IF;
558:
559: IF (g_level_procedure >= g_current_runtime_level ) THEN

Line 753: FROM zx_rep_trx_detail_t itf,

749: BULK COLLECT INTO l_detail_tax_line_id_tbl,
750: l_trx_id_tbl,
751: l_tax_rate_tbl,
752: l_document_sub_type_tbl
753: FROM zx_rep_trx_detail_t itf,
754: ap_invoices apinv
755: WHERE itf.request_id = p_request_id
756: AND itf.trx_id = apinv.invoice_id
757: AND itf.tax_type_code = P_TAX_TYPE_CODE

Line 824: DELETE from zx_rep_trx_detail_t itf

820: END IF;
821:
822: -- Delete Unwanted lines from Detail ITF
823:
824: DELETE from zx_rep_trx_detail_t itf
825: WHERE itf.request_id = p_request_id
826: AND NOT EXISTS ( SELECT 1
827: FROM zx_rep_trx_jx_ext_t ext
828: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

828: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
829:
830: IF ( g_level_statement>= g_current_runtime_level ) THEN
831: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
832: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
833: END IF;
834:
835:
836: EXCEPTION

Line 865: FROM zx_rep_trx_detail_t itf,

861: l_trx_line_id_tbl,
862: l_trx_id_tbl,
863: l_tax_rate_tbl,
864: l_document_sub_type_tbl
865: FROM zx_rep_trx_detail_t itf,
866: ap_invoices_all apinv --Bug 5415028
867: WHERE itf.request_id = P_REQUEST_ID
868: AND itf.tax_type_code = P_VAT_TAX_TYPE
869: -- OR itf.tax_rate = 0)

Line 1024: DELETE from zx_rep_trx_detail_t itf

1020: END IF;
1021:
1022: -- Delete Unwanted lines from Detail ITF
1023:
1024: DELETE from zx_rep_trx_detail_t itf
1025: WHERE itf.request_id = p_request_id
1026: AND NOT EXISTS ( SELECT 1
1027: FROM zx_rep_trx_jx_ext_t ext
1028: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

1033: END IF;
1034:
1035: IF ( g_level_statement>= g_current_runtime_level ) THEN
1036: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
1037: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
1038: END IF;
1039:
1040: EXCEPTION
1041:

Line 1104: FROM zx_rep_trx_detail_t itf1,

1100: NVL(itf1.shipping_tp_address_id,
1101: itf1.billing_tp_address_id) bill_from_site_id,
1102: NVL(itf1.billing_trading_partner_id,
1103: itf1.shipping_trading_partner_id) bill_from_party_id
1104: FROM zx_rep_trx_detail_t itf1,
1105: ap_invoices apinv
1106: WHERE itf1.request_id = P_REQUEST_ID
1107: AND apinv.invoice_id = itf1.trx_id
1108: -- AND itf1.posted_flag = 'Y'

Line 1137: FROM zx_rep_trx_detail_t itf1,

1133: NVL(itf1.shipping_tp_address_id,
1134: itf1.billing_tp_address_id) bill_from_site_id,
1135: NVL(itf1.billing_trading_partner_id,
1136: itf1.shipping_trading_partner_id) bill_from_party_id
1137: FROM zx_rep_trx_detail_t itf1,
1138: ap_invoices apinv
1139: WHERE itf1.request_id = P_REQUEST_ID
1140: AND apinv.invoice_id = itf1.trx_id
1141: -- AND itf1.posted_flag = 'Y'

Line 1171: FROM zx_rep_trx_detail_t itf1,

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

Line 1196: FROM zx_rep_trx_detail_t itf2,

1192: null currency_conversion_rate,
1193: null tax_rate,
1194: null tax_rate_id,
1195: null document_sub_type
1196: FROM zx_rep_trx_detail_t itf2,
1197: ap_invoices apinv2
1198: WHERE itf2.request_id = P_REQUEST_ID
1199: AND apinv2.invoice_id = itf2.trx_id
1200: AND itf2.tax_type_code = P_NON_TAXAB_TAX_TYPE

Line 1462: DELETE from zx_rep_trx_detail_t itf

1458: END IF;
1459:
1460: -- Delete Unwanted lines from Detail ITF
1461:
1462: DELETE from zx_rep_trx_detail_t itf
1463: WHERE itf.request_id = p_request_id
1464: AND NOT EXISTS ( SELECT 1
1465: FROM zx_rep_trx_jx_ext_t ext
1466: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 1499: FROM zx_rep_trx_detail_t itf1 ,

1495: l_trx_line_id_tbl,
1496: l_trx_id_tbl,
1497: l_tax_rate_tbl,
1498: l_document_sub_type_tbl
1499: FROM zx_rep_trx_detail_t itf1 ,
1500: zx_fc_codes_denorm_b fc
1501: WHERE itf1.request_id = P_REQUEST_ID
1502: AND itf1.cancel_flag = 'N'
1503: -- AND itf1.posted_flag = 'Y' --Bug 5413860

Line 1521: FROM zx_rep_trx_detail_t itf1

1517: BULK COLLECT INTO l_detail_tax_line_id_tbl,
1518: l_trx_id_tbl,
1519: l_tax_rate_tbl,
1520: l_document_sub_type_tbl
1521: FROM zx_rep_trx_detail_t itf1
1522: WHERE itf1.request_id = P_REQUEST_ID
1523: AND itf1.cancel_flag = 'N'
1524: AND itf1.posted_flag = 'Y'
1525: AND ( itf1.reverse_flag IS NULL OR itf1.reverse_flag <>'Y')

Line 1683: DELETE from zx_rep_trx_detail_t itf

1679: END IF;
1680: -- Delete Unwanted lines from Detail ITF
1681:
1682:
1683: DELETE from zx_rep_trx_detail_t itf
1684: WHERE itf.request_id = p_request_id
1685: AND NOT EXISTS ( SELECT 1
1686: FROM zx_rep_trx_jx_ext_t ext
1687: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

1688:
1689:
1690: IF ( g_level_statement>= g_current_runtime_level ) THEN
1691: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
1692: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
1693: END IF;
1694:
1695: EXCEPTION
1696:

Line 1708: DELETE from zx_rep_trx_detail_t itf

1704: END;
1705:
1706: ELSIF P_REPORT_NAME = 'JLARPPTF' THEN
1707:
1708: DELETE from zx_rep_trx_detail_t itf
1709: WHERE itf.request_id = p_request_id
1710: AND (NVL(itf.reverse_flag,'N') = 'Y'
1711: OR itf.tax_type_code <> NVL(p_trl_global_variables_rec.per_tax_type_code_high,
1712: p_trl_global_variables_rec.per_tax_type_code_low));

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

1717: AND p_trl_global_variables_rec.gl_date_high)*/
1718:
1719: DELETE from zx_rep_actg_ext_t actg
1720: WHERE actg.request_id = p_request_id
1721: AND NOT EXISTS ( SELECT 1 FROM zx_rep_trx_detail_t itf
1722: WHERE actg.detail_tax_line_id = itf.detail_tax_line_id);
1723:
1724: SELECT detail_tax_line_id,
1725: trx_line_id,

Line 1736: FROM zx_rep_trx_detail_t

1732: l_trx_id_tbl,
1733: l_tax_rate_tbl,
1734: l_tax_rate_id_tbl,
1735: l_tax_regime_code_tbl
1736: FROM zx_rep_trx_detail_t
1737: WHERE request_id = p_request_id;
1738:
1739:
1740: GET_TAX_AUTH_CATEG(

Line 1932: FROM zx_rep_trx_detail_t itf,

1928: l_trx_id_tbl,
1929: l_internal_org_id_tbl,
1930: l_tax_rate_tbl,
1931: l_document_sub_type_tbl
1932: FROM zx_rep_trx_detail_t itf,
1933: ra_customer_trx_all rcta
1934: WHERE itf.request_id = P_REQUEST_ID
1935: AND rcta.customer_trx_id = itf.trx_id
1936: AND itf.extract_source_ledger = 'AR'

Line 1955: FROM zx_rep_trx_detail_t itf

1951: l_trx_number_tbl,
1952: l_internal_org_id_tbl,
1953: l_tax_rate_tbl,
1954: l_document_sub_type_tbl
1955: FROM zx_rep_trx_detail_t itf
1956: WHERE itf.request_id = P_REQUEST_ID
1957: AND itf.extract_source_ledger = 'AR'
1958: AND itf.trx_line_class = 'CREDIT_MEMO'
1959: AND NVL(itf.application_doc_status,'$')<>'VD'

Line 2022: DELETE from zx_rep_trx_detail_t itf

2018: 'After insertion into zx_rep_trx_jx_ext_t ');
2019: END IF;
2020: -- Delete Unwanted lines from Detail ITF
2021:
2022: DELETE from zx_rep_trx_detail_t itf
2023: WHERE itf.request_id = p_request_id
2024: AND NOT EXISTS ( SELECT 1
2025: FROM zx_rep_trx_jx_ext_t ext
2026: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

2026: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
2027:
2028: IF ( g_level_statement>= g_current_runtime_level ) THEN
2029: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2030: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2031: END IF;
2032:
2033: IF (g_level_procedure >= g_current_runtime_level ) THEN
2034: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 2056: -- DELETE from ZX_REP_TRX_DETAIL_T DET

2052: IF (g_level_procedure >= g_current_runtime_level ) THEN
2053: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2054: 'JLARTPFF : Filter ');
2055: END IF;
2056: -- DELETE from ZX_REP_TRX_DETAIL_T DET
2057: -- WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
2058: -- AND det.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
2059:
2060: SELECT detail_tax_line_id,

Line 2074: FROM zx_rep_trx_detail_t itf1

2070: l_internal_org_id_tbl,
2071: l_tax_rate_tbl,
2072: l_document_sub_type_tbl,
2073: l_exchange_rate_tbl
2074: FROM zx_rep_trx_detail_t itf1
2075: -- ra_customer_trx rct
2076: WHERE itf1.request_id = P_REQUEST_ID
2077: -- AND rct.customer_trx_id = itf1.trx_id
2078: AND itf1.trx_line_class in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')

Line 2201: DELETE from zx_rep_trx_detail_t itf

2197: 'After insertion into zx_rep_trx_jx_ext_t ');
2198: END IF;
2199: -- Delete Unwanted lines from Detail ITF
2200:
2201: DELETE from zx_rep_trx_detail_t itf
2202: WHERE itf.request_id = p_request_id
2203: AND NOT EXISTS ( SELECT 1
2204: FROM zx_rep_trx_jx_ext_t ext
2205: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

2205: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
2206:
2207: IF ( g_level_statement>= g_current_runtime_level ) THEN
2208: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2209: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2210: END IF;
2211:
2212: IF (g_level_procedure >= g_current_runtime_level ) THEN
2213: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 2267: FROM zx_rep_trx_detail_t itf1

2263: itf1.trx_type_id,
2264: itf1.tax_rate tax_rate,
2265: itf1.tax_rate_id,
2266: null document_sub_type
2267: FROM zx_rep_trx_detail_t itf1
2268: WHERE itf1.request_id = P_REQUEST_ID
2269: AND itf1.tax = p_vat_tax
2270: AND nvl(itf1.tax_type_code,'VAT') = 'VAT'
2271: AND itf1.tax_rate <> 0

Line 2292: FROM zx_rep_trx_detail_t itf2

2288: itf2.trx_type_id,
2289: 0 tax_rate,
2290: itf2.tax_rate_id,
2291: null document_sub_type
2292: FROM zx_rep_trx_detail_t itf2
2293: WHERE itf2.request_id = P_REQUEST_ID
2294: AND NVL(itf2.tax_type_code, 'VAT') = 'VAT'
2295: AND nvl(itf2.TAX_RATE,0) = 0
2296: AND itf2.tax = p_vat_tax

Line 2298: FROM zx_rep_trx_detail_t itf3

2294: AND NVL(itf2.tax_type_code, 'VAT') = 'VAT'
2295: AND nvl(itf2.TAX_RATE,0) = 0
2296: AND itf2.tax = p_vat_tax
2297: AND not exists (SELECT 1
2298: FROM zx_rep_trx_detail_t itf3
2299: WHERE itf3.request_id = P_REQUEST_ID
2300: AND itf2.trx_id = itf3.trx_id
2301: AND NVL(itf3.tax_type_code, 'VAT') = 'VAT'
2302: AND nvl(itf3.tax_rate,0) <> 0

Line 2318: FROM zx_rep_trx_detail_t itf4

2314: itf4.trx_type_id,
2315: itf4.tax_rate,
2316: itf4.tax_rate_id,
2317: null document_sub_type
2318: FROM zx_rep_trx_detail_t itf4
2319: WHERE itf4.request_id = P_REQUEST_ID
2320: AND not exists ( SELECT 1
2321: FROM zx_rep_trx_detail_t itf5
2322: WHERE itf5.request_id = P_REQUEST_ID

Line 2321: FROM zx_rep_trx_detail_t itf5

2317: null document_sub_type
2318: FROM zx_rep_trx_detail_t itf4
2319: WHERE itf4.request_id = P_REQUEST_ID
2320: AND not exists ( SELECT 1
2321: FROM zx_rep_trx_detail_t itf5
2322: WHERE itf5.request_id = P_REQUEST_ID
2323: AND itf5.trx_id = itf4.trx_id
2324: AND itf5.tax = p_vat_tax )
2325: AND itf4.ROWID = ( SELECT Min(itf6.ROWID)

Line 2326: FROM zx_rep_trx_detail_t itf6

2322: WHERE itf5.request_id = P_REQUEST_ID
2323: AND itf5.trx_id = itf4.trx_id
2324: AND itf5.tax = p_vat_tax )
2325: AND itf4.ROWID = ( SELECT Min(itf6.ROWID)
2326: FROM zx_rep_trx_detail_t itf6
2327: WHERE itf4.trx_id = itf6.trx_id
2328: AND itf6.request_id = P_REQUEST_ID )
2329: );
2330:

Line 2487: DELETE from zx_rep_trx_detail_t itf

2483: 'After insertion into zx_rep_trx_jx_ext_t ');
2484: END IF;
2485: -- Delete Unwanted lines from Detail ITF
2486:
2487: DELETE from zx_rep_trx_detail_t itf
2488: WHERE itf.request_id = p_request_id
2489: AND NOT EXISTS ( SELECT 1
2490: FROM zx_rep_trx_jx_ext_t ext
2491: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

2491: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
2492:
2493: IF ( g_level_statement>= g_current_runtime_level ) THEN
2494: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
2495: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2496: END IF;
2497:
2498: IF (g_level_procedure >= g_current_runtime_level ) THEN
2499: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 2560: FROM zx_rep_trx_detail_t dtl,

2556: dtl.tax_rate,
2557: dtl.tax_rate_id,
2558: null document_sub_type,
2559: dtl.tax_regime_code
2560: FROM zx_rep_trx_detail_t dtl,
2561: ar_vat_tax_all vat
2562: WHERE dtl.request_id = P_REQUEST_ID
2563: AND nvl(vat.tax_type,'VAT') = 'VAT'
2564: AND dtl.tax_rate_id = vat.vat_tax_id

Line 2587: FROM zx_rep_trx_detail_t itf2

2583: currency_conversion_rate,
2584: trx_type_id,
2585: null tax_rate,
2586: null document_sub_type
2587: FROM zx_rep_trx_detail_t itf2
2588: WHERE itf2.request_id = P_REQUEST_ID
2589: AND nvl(itf2.tax_type_code,'VAT') = 'VAT'
2590: GROUP BY itf2.internal_organization_id,
2591: itf2.trx_id, trx_line_id, trx_number, currency_conversion_rate,trx_type_id */

Line 2605: FROM zx_rep_trx_detail_t itf3

2601: null tax_rate,
2602: itf3.tax_rate_id,
2603: null document_sub_type,
2604: itf3.tax_regime_code
2605: FROM zx_rep_trx_detail_t itf3
2606: WHERE itf3.request_id = P_REQUEST_ID
2607: AND itf3.tax = P_VAT_NON_TAXAB_TAX
2608: AND itf3.tax_rate = 0
2609: GROUP BY itf3.internal_organization_id,

Line 2650: FROM zx_rep_trx_detail_t

2646: l_trx_type_id_tbl,
2647: l_tax_rate_tbl,
2648: l_tax_rate_id_tbl,
2649: l_document_sub_type_tbl
2650: FROM zx_rep_trx_detail_t
2651: WHERE request_id = P_REQUEST_ID
2652: AND nvl(tax_type_code,'VAT') = 'VAT'
2653: AND doc_event_status = 'FROZEN_FOR_TAX'
2654: AND tax_regime_code = p_tax_regime

Line 2989: /* DELETE from zx_rep_trx_detail_t itf

2985: 'After insertion into zx_rep_trx_jx_ext_t ');
2986: END IF;
2987: -- Delete Unwanted lines from Detail ITF
2988:
2989: /* DELETE from zx_rep_trx_detail_t itf
2990: WHERE itf.request_id = p_request_id
2991: AND NOT EXISTS ( SELECT 1
2992: FROM zx_rep_trx_jx_ext_t ext
2993: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

2996: UPDATE_DGI_CURR_CODE(p_request_id);
2997:
2998: IF ( g_level_statement>= g_current_runtime_level ) THEN
2999: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
3000: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3001: END IF;
3002:
3003: IF (g_level_procedure >= g_current_runtime_level ) THEN
3004: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 3045: FROM zx_rep_trx_detail_t itf1,

3041: l_tax_rate_tbl,
3042: l_tax_rate_id_tbl,
3043: l_tax_regime_code_tbl,
3044: l_document_sub_type_tbl
3045: FROM zx_rep_trx_detail_t itf1,
3046: jl_zz_ar_tx_categ categ
3047: WHERE itf1.request_id = P_REQUEST_ID
3048: -- AND itf1.tax_regime_code
3049: AND itf1.tax = categ.tax_category

Line 3152: DELETE from zx_rep_trx_detail_t itf

3148: 'After insertion into zx_rep_trx_jx_ext_t ');
3149: END IF;
3150: -- Delete Unwanted lines from Detail ITF
3151:
3152: DELETE from zx_rep_trx_detail_t itf
3153: WHERE itf.request_id = p_request_id
3154: AND NOT EXISTS ( SELECT 1
3155: FROM zx_rep_trx_jx_ext_t ext
3156: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

3156: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
3157:
3158: IF ( g_level_statement>= g_current_runtime_level ) THEN
3159: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',
3160: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3161: END IF;
3162:
3163: IF (g_level_procedure >= g_current_runtime_level ) THEN
3164: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AR',

Line 3201: FROM zx_rep_trx_detail_t itf,

3197: l_internal_org_id_tbl,
3198: l_trx_number_tbl,
3199: l_tax_rate_tbl,
3200: l_document_sub_type_tbl
3201: FROM zx_rep_trx_detail_t itf,
3202: zx_rates_b rates,
3203: -- zx_report_codes_assoc repc,
3204: -- zx_reporting_types_b reptypes,--Bug 5438742
3205: ra_customer_trx_all ratrx,

Line 3332: DELETE from zx_rep_trx_detail_t itf

3328: 'After insertion into zx_rep_trx_jx_ext_t ');
3329: END IF;
3330: -- Delete Unwanted lines from Detail ITF
3331:
3332: DELETE from zx_rep_trx_detail_t itf
3333: WHERE itf.request_id = p_request_id
3334: AND NOT EXISTS ( SELECT 1
3335: FROM zx_rep_trx_jx_ext_t ext
3336: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

Line 3380: FROM zx_rep_trx_detail_t dtl, --Bug 5396444

3376: l_exchange_rate_tbl,
3377: l_tax_rate_tbl,
3378: l_tax_rate_id_tbl,
3379: l_document_sub_type_tbl
3380: FROM zx_rep_trx_detail_t dtl, --Bug 5396444
3381: ra_cust_trx_types_all tt
3382: WHERE dtl.request_id = P_REQUEST_ID
3383: AND dtl.trx_type_id = tt.cust_trx_type_id
3384: AND tt.accounting_affect_flag = 'Y';

Line 3467: DELETE from zx_rep_trx_detail_t itf

3463: END IF;
3464:
3465: -- Delete Unwanted lines from Detail ITF
3466:
3467: DELETE from zx_rep_trx_detail_t itf
3468: WHERE itf.request_id = p_request_id
3469: AND NOT EXISTS ( SELECT 1
3470: FROM zx_rep_trx_jx_ext_t ext
3471: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);

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

3471: WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
3472:
3473: IF ( g_level_statement>= g_current_runtime_level ) THEN
3474: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG.POPULATE_JL_AP',
3475: 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
3476: END IF;
3477: EXCEPTION
3478: WHEN OTHERS THEN
3479: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 3530: FROM zx_rep_trx_detail_t dtl

3526: dtl.creation_date,
3527: dtl.last_updated_by,
3528: dtl.last_update_date,
3529: dtl.last_update_login
3530: FROM zx_rep_trx_detail_t dtl
3531: WHERE dtl.request_id = p_request_id
3532: );
3533:
3534: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 3613: FROM zx_rep_trx_detail_t itf

3609: BEGIN
3610: IF P_REPORT_NAME = 'ZXARPVBR' THEN
3611: SELECT SUM(itf.tax_amt)
3612: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3613: FROM zx_rep_trx_detail_t itf
3614: WHERE itf.request_id = p_request_id
3615: AND itf.trx_id = p_trx_id_tbl(i)
3616: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3617: AND itf.tax_rate = p_tax_rate_tbl(i)

Line 3631: FROM zx_rep_trx_detail_t itf

3627: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'JL_CL_CREDIT_MEMO' THEN
3628:
3629: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3630: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3631: FROM zx_rep_trx_detail_t itf
3632: WHERE itf.request_id = p_request_id
3633: AND itf.trx_id = p_trx_id_tbl(i)
3634: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3635: AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y')

Line 3641: FROM zx_rep_trx_detail_t itf

3637: AND itf.tax_rate <> 0;
3638: ELSE
3639: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3640: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3641: FROM zx_rep_trx_detail_t itf
3642: WHERE itf.request_id = p_request_id
3643: AND itf.trx_id = p_trx_id_tbl(i)
3644: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3645: AND itf.tax_type_code = 'VAT';

Line 3652: FROM zx_rep_trx_detail_t itf

3648: ELSIF P_REPORT_NAME = 'ZXZZTVSR' THEN
3649:
3650: SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
3651: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3652: FROM zx_rep_trx_detail_t itf
3653: WHERE itf.request_id = p_request_id
3654: AND itf.trx_id = p_trx_id_tbl(i)
3655: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3656: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021

Line 3694: FROM zx_rep_trx_detail_t itf

3690: -- OPEN ISSUE --
3691: --Bug 5438742 : Added VAT Amt logic for the Bug
3692: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
3693: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3694: FROM zx_rep_trx_detail_t itf
3695: WHERE itf.request_id = p_request_id
3696: AND itf.trx_id = p_trx_id_tbl(i)
3697: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3698: AND itf.tax_type_code = 'VAT'

Line 3709: FROM zx_rep_trx_detail_t itf

3705: END IF;
3706: ELSIF P_REPORT_NAME = 'ZXCOARSB' THEN --Bug 5396444 : Vat Amt
3707: SELECT sum(nvl(itf.tax_amt,0))
3708: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3709: FROM zx_rep_trx_detail_t itf
3710: WHERE itf.request_id = p_request_id
3711: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3712: AND itf.trx_id = p_trx_id_tbl(i);
3713:

Line 3747: FROM zx_rep_trx_detail_t

3743: BEGIN
3744:
3745: SELECT count(distinct trx_id)
3746: INTO l_trx_counter
3747: FROM zx_rep_trx_detail_t
3748: WHERE request_id = p_request_id;
3749:
3750: IF ( g_level_statement>= g_current_runtime_level ) THEN
3751: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 3813: FROM zx_rep_trx_detail_t itf

3809:
3810: --SELECT SUM(nvl(itf.tax_amt_funcl_curr,0))
3811: SELECT SUM(nvl(itf.tax_amt,0))
3812: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3813: FROM zx_rep_trx_detail_t itf
3814: WHERE itf.request_id = p_request_id
3815: AND itf.trx_id = p_trx_id_tbl(i)
3816: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3817: AND itf.reverse_flag is null

Line 3835: FROM zx_rep_trx_detail_t itf

3831: END IF;
3832:
3833: SELECT sum(itf.tax_amt)
3834: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3835: FROM zx_rep_trx_detail_t itf
3836: WHERE itf.request_id = p_request_id
3837: AND itf.trx_id = p_trx_id_tbl(i)
3838: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3839: -- AND itf.posted_flag = 'Y'

Line 3856: FROM zx_rep_trx_detail_t itf

3852: ELSIF P_REPORT_NAME = 'JLZZTCFF' THEN
3853:
3854: SELECT sum(nvl(itf.tax_amt,0))
3855: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3856: FROM zx_rep_trx_detail_t itf
3857: WHERE itf.request_id = p_request_id
3858: AND itf.trx_id = p_trx_id_tbl(i)
3859: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3860: AND itf.tax_regime_code = p_tax_regime

Line 3930: FROM zx_rep_trx_detail_t itf,

3926: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
3927:
3928: SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
3929: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3930: FROM zx_rep_trx_detail_t itf,
3931: ar_vat_tax_all vat
3932: WHERE itf.request_id = p_request_id
3933: AND itf.trx_id = p_trx_id_tbl(i)
3934: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)

Line 3981: FROM zx_rep_trx_detail_t itf

3977: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
3978:
3979: SELECT sum(nvl(itf.tax_amt,0))
3980: INTO l_vat_amt_tbl(p_trx_id_tbl(i))
3981: FROM zx_rep_trx_detail_t itf
3982: WHERE itf.request_id = p_request_id
3983: AND itf.trx_id = p_trx_id_tbl(i)
3984: -- AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
3985: AND itf.tax_regime_code = p_tax_regime

Line 4163: FROM zx_rep_trx_detail_t itf

4159: IF l_taxable_amt_tbl(k) is NULL THEN
4160: BEGIN
4161: SELECT SUM(NVL(itf.taxable_amt,0))
4162: INTO l_taxable_amt_tbl(k)
4163: FROM zx_rep_trx_detail_t itf
4164: WHERE itf.request_id = p_request_id
4165: AND itf.trx_id = p_trx_id_tbl(i)
4166: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4167: AND itf.trx_line_id = p_trx_line_id(i)

Line 4227: FROM zx_rep_trx_detail_t itf

4223: IF l_taxable_amt_tbl(p_trx_line_id(i)) is NULL THEN
4224: BEGIN
4225: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
4226: INTO l_taxable_amt_tbl(p_trx_line_id(i))
4227: FROM zx_rep_trx_detail_t itf
4228: WHERE itf.request_id = p_request_id
4229: AND itf.trx_id = p_trx_id_tbl(i)
4230: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4231: AND itf.trx_line_id = p_trx_line_id(i)

Line 4298: FROM zx_rep_trx_detail_t itf

4294: END IF;
4295: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'JL_CL_CREDIT_MEMO' THEN
4296: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
4297: INTO l_taxable_amt_tbl(k)
4298: FROM zx_rep_trx_detail_t itf
4299: WHERE itf.request_id = p_request_id
4300: AND itf.trx_id = p_trx_id_tbl(i)
4301: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4302: AND itf.trx_line_id = p_trx_line_id(i)

Line 4309: FROM zx_rep_trx_detail_t itf

4305: AND itf.tax_rate <> 0;
4306: ELSE
4307: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
4308: INTO l_taxable_amt_tbl(k)
4309: FROM zx_rep_trx_detail_t itf
4310: WHERE itf.request_id = p_request_id
4311: AND itf.trx_id = p_trx_id_tbl(i)
4312: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4313: AND itf.trx_line_id = p_trx_line_id(i)

Line 4414: FROM zx_rep_trx_detail_t itf

4410: -- null;
4411: -- OPEN ISSUE -- --Bug 5438742
4412: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
4413: INTO l_taxable_amt_tbl(p_trx_line_id(i))
4414: FROM zx_rep_trx_detail_t itf
4415: WHERE itf.request_id = p_request_id
4416: AND itf.trx_id = p_trx_id_tbl(i)
4417: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4418: AND itf.trx_line_id = p_trx_line_id(i)

Line 4471: FROM zx_rep_trx_detail_t

4467: ELSE
4468: BEGIN
4469: SELECT count(distinct trx_id)
4470: INTO l_trx_counter
4471: FROM zx_rep_trx_detail_t
4472: WHERE request_id = p_request_id;
4473:
4474: IF ( g_level_statement>= g_current_runtime_level ) THEN
4475: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 4536: FROM zx_rep_trx_detail_t itf

4532: 'get_taxable : '|| to_char(k)||' '||to_char(p_trx_id_tbl(i))||' '||to_char(p_tax_rate_tbl(i)));
4533: END IF;
4534: SELECT abs(nvl(SUM(itf.taxable_amt),0))
4535: INTO l_taxable_amt_tbl(k)
4536: FROM zx_rep_trx_detail_t itf
4537: WHERE itf.request_id = p_request_id
4538: AND itf.trx_id = p_trx_id_tbl(i)
4539: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4540: AND itf.trx_line_id = p_trx_line_id(i)

Line 4554: FROM zx_rep_trx_detail_t itf

4550: ELSIF P_REPORT_NAME = 'JLARTPFF' THEN
4551:
4552: SELECT ABS(SUM(itf.taxable_amt_funcl_curr))
4553: INTO l_taxable_amt_tbl(k)
4554: FROM zx_rep_trx_detail_t itf
4555: WHERE itf.request_id = p_request_id
4556: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4557: AND itf.trx_line_id = p_trx_line_id(i)
4558: AND itf.trx_id = p_trx_id_tbl(i);

Line 4597: FROM zx_rep_trx_detail_t itf

4593: END IF;
4594:
4595: SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt)),0)
4596: INTO l_taxable_amt_tbl(k)
4597: FROM zx_rep_trx_detail_t itf
4598: WHERE itf.request_id = p_request_id
4599: AND itf.trx_id = p_trx_id_tbl(i)
4600: AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
4601: AND itf.trx_line_id = p_trx_line_id(i)

Line 4708: FROM zx_rep_trx_detail_t

4704: BEGIN
4705:
4706: SELECT count(distinct trx_id)
4707: INTO l_trx_counter
4708: FROM zx_rep_trx_detail_t
4709: WHERE request_id = p_request_id;
4710:
4711: IF ( g_level_statement>= g_current_runtime_level ) THEN
4712: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 4774: FROM zx_rep_trx_detail_t itf

4770: IF P_REPORT_NAME in ('ZXARPVBR','JLARPPFF') THEN
4771:
4772: SELECT SUM(itf.taxable_amt)
4773: INTO l_non_taxable_amt_tbl(k)
4774: FROM zx_rep_trx_detail_t itf
4775: WHERE itf.request_id = p_request_id
4776: AND itf.trx_id = p_trx_id_tbl(i)
4777: AND itf.trx_line_id = p_trx_line_id(i)
4778: AND itf.tax_type_code = p_non_taxab_tax;

Line 4788: FROM zx_rep_trx_detail_t itf

4784: ELSIF P_REPORT_NAME = 'ZXZZTVSR' THEN
4785:
4786: SELECT SUM(itf.taxable_amt_funcl_curr)
4787: INTO l_non_taxable_amt_tbl(k)
4788: FROM zx_rep_trx_detail_t itf
4789: WHERE itf.request_id = p_request_id
4790: AND itf.trx_id = p_trx_line_id(i)
4791: AND itf.tax NOT IN (p_vat_tax, p_vat_addit_tax,p_vat_percep_tax);
4792:

Line 4819: FROM zx_rep_trx_detail_t itf

4815: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
4816:
4817: SELECT sum(nvl(itf.tax_amt ,0))
4818: INTO l_non_taxable_amt_tbl(p_trx_id_tbl(i))
4819: FROM zx_rep_trx_detail_t itf
4820: WHERE itf.request_id = p_request_id
4821: AND itf.trx_id = p_trx_id_tbl(i)
4822: AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
4823: AND itf.tax = p_non_taxab_tax;

Line 4925: FROM zx_rep_trx_detail_t

4921: BEGIN
4922:
4923: SELECT count(distinct trx_id)
4924: INTO l_trx_counter
4925: FROM zx_rep_trx_detail_t
4926: WHERE request_id = p_request_id;
4927:
4928: IF ( g_level_statement>= g_current_runtime_level ) THEN
4929: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 4967: FROM zx_rep_trx_detail_t itf

4963: IF P_REPORT_NAME IN ('ZXZZTVSR','JLARTSFF') THEN
4964:
4965: SELECT SUM(NVL(itf.tax_amt_funcl_curr,itf.tax_amt))
4966: INTO l_vat_additional_amt_tbl(p_trx_id_tbl(i))
4967: FROM zx_rep_trx_detail_t itf
4968: WHERE itf.request_id = p_request_id
4969: AND itf.trx_id = p_trx_id_tbl(i)
4970: -- AND itf.tax_rate_id = p_tax_rate_id_tbl(i)
4971: ANd itf.tax = p_vat_addit_tax;

Line 5084: FROM zx_rep_trx_detail_t

5080: BEGIN
5081:
5082: SELECT count(distinct trx_id)
5083: INTO l_trx_counter
5084: FROM zx_rep_trx_detail_t
5085: WHERE request_id = p_request_id;
5086:
5087: IF ( g_level_statement>= g_current_runtime_level ) THEN
5088: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5160: FROM ZX_REP_TRX_DETAIL_T itf

5156: END IF;
5157:
5158: SELECT nvl(SUM(itf.taxable_amt_funcl_curr),0)
5159: INTO l_vat_exempt_amt_tbl(k)
5160: FROM ZX_REP_TRX_DETAIL_T itf
5161: WHERE itf.request_id = p_request_id
5162: AND itf.trx_id = p_trx_id_tbl(i)
5163: AND itf.trx_line_id = p_trx_line_id_tbl(i)
5164: -- AND itf.tax_type_code = 'Exempt'

Line 5191: FROM zx_rep_trx_detail_t itf

5187: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'DOCUMENT TYPE.JL_CL_CREDIT_MEMO' THEN
5188:
5189: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
5190: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5191: FROM zx_rep_trx_detail_t itf
5192: WHERE itf.request_id = p_request_id
5193: AND itf.trx_id = p_trx_id_tbl(i)
5194: AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y')
5195: AND itf.tax_rate = 0;

Line 5201: FROM zx_rep_trx_detail_t itf

5197: ELSE
5198:
5199: SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
5200: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5201: FROM zx_rep_trx_detail_t itf
5202: WHERE itf.request_id = p_request_id
5203: AND itf.trx_id = p_trx_id_tbl(i)
5204: AND itf.tax_rate = 0;
5205:

Line 5237: FROM zx_rep_trx_detail_t itf

5233: IF l_vat_0_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
5234:
5235: SELECT sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
5236: INTO l_vat_0_amt_tbl(p_trx_id_tbl(i))
5237: FROM zx_rep_trx_detail_t itf
5238: WHERE itf.request_id = p_request_id
5239: AND itf.trx_id = p_trx_id_tbl(i)
5240: AND nvl(itf.tax_type_code,'VAT') = 'VAT'
5241: AND itf.tax_rate = 0

Line 5260: FROM zx_rep_trx_detail_t itf

5256: IF l_no_vat_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
5257:
5258: SELECT sum(nvl(itf.taxable_amt_funcl_curr,0))
5259: INTO l_no_vat_amt_tbl(p_trx_id_tbl(i))
5260: FROM zx_rep_trx_detail_t itf
5261: WHERE itf.request_id = p_request_id
5262: AND itf.trx_id = p_trx_id_tbl(i)
5263: AND itf.tax_rate = 0
5264: AND ( itf.tax_regime_code <> p_tax_regime OR itf.tax <> p_vat_tax )

Line 5344: FROM zx_rep_trx_detail_t itf

5340:
5341: SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,0)),0)
5342: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5343: --INTO l_vat_exempt_amt_tbl(k)
5344: FROM zx_rep_trx_detail_t itf
5345: WHERE itf.request_id = p_request_id
5346: AND itf.trx_id = p_trx_id_tbl(i)
5347: AND itf.trx_line_id = p_trx_line_id_tbl(i) -- new join
5348: AND nvl(itf.tax_type_code,'VAT') = 'VAT'

Line 5396: FROM zx_rep_trx_detail_t itf

5392: SELECT Nvl(
5393: Sum((itf.EXEMPT_RATE_MODIFIER * coalesce(itf.tax_amt_funcl_curr,itf.tax_amt,0))
5394: /(100 - itf.EXEMPT_RATE_MODIFIER)) ,0)
5395: INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
5396: FROM zx_rep_trx_detail_t itf
5397: WHERE itf.request_id = p_request_id
5398: AND itf.trx_id = p_trx_id_tbl(i)
5399: AND itf.TAX_EXEMPTION_ID IS NOT NULL ;
5400:

Line 5513: FROM zx_rep_trx_detail_t

5509: BEGIN
5510:
5511: SELECT count(distinct trx_id)
5512: INTO l_trx_counter
5513: FROM zx_rep_trx_detail_t
5514: WHERE request_id = p_request_id;
5515:
5516: IF ( g_level_statement>= g_current_runtime_level ) THEN
5517: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5543: FROM zx_rep_trx_detail_t itf

5539: IF l_vat_perc_amt_tbl(p_trx_line_id_tbl(i)) is NULL THEN
5540:
5541: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5542: INTO l_vat_perc_amt_tbl(p_trx_line_id_tbl(i))
5543: FROM zx_rep_trx_detail_t itf
5544: WHERE itf.request_id = p_request_id
5545: AND itf.trx_id = p_trx_id_tbl(i)
5546: AND itf.trx_line_id = p_trx_line_id_tbl(i)
5547: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021

Line 5618: FROM zx_rep_trx_detail_t itf

5614:
5615:
5616: SELECT nvl(SUM(itf.tax_amt),0)
5617: INTO l_vat_perc_amt_tbl(k)
5618: FROM zx_rep_trx_detail_t itf
5619: WHERE itf.request_id = p_request_id
5620: AND itf.trx_id = p_trx_id_tbl(i)
5621: AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
5622: AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);

Line 5628: FROM zx_rep_trx_detail_t itf

5624: ELSIF P_REPORT_NAME in ('ZXZZTVSR') THEN
5625:
5626: SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
5627: INTO l_vat_perc_amt_tbl(k)
5628: FROM zx_rep_trx_detail_t itf
5629: WHERE itf.request_id = p_request_id
5630: AND itf.trx_id = p_trx_id_tbl(i)
5631: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
5632: AND itf.tax = p_vat_perc_tax

Line 5675: FROM zx_rep_trx_detail_t itf

5671: -- IF l_gdf_ra_cust_trx_att19_tbl(k) = 'IS_NULL' THEN
5672:
5673: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5674: INTO l_vat_perc_amt_tbl(k)
5675: FROM zx_rep_trx_detail_t itf
5676: WHERE itf.request_id = p_request_id
5677: AND itf.trx_id = p_trx_id_tbl(i)
5678: AND itf.tax_regime_code = p_tax_regime
5679: AND itf.tax = p_vat_perc_tax

Line 5725: FROM zx_rep_trx_detail_t itf

5721:
5722:
5723: SELECT nvl(SUM(itf.tax_amt),0)
5724: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
5725: FROM zx_rep_trx_detail_t itf
5726: WHERE itf.request_id = p_request_id
5727: AND itf.trx_id = p_trx_id_tbl(i)
5728: AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
5729: AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);

Line 5735: FROM zx_rep_trx_detail_t itf

5731: ELSIF P_REPORT_NAME in ('ZXZZTVSR') THEN
5732:
5733: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5734: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
5735: FROM zx_rep_trx_detail_t itf
5736: WHERE itf.request_id = p_request_id
5737: AND itf.trx_id = p_trx_id_tbl(i)
5738: AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
5739: AND itf.tax = p_vat_perc_tax

Line 5782: FROM zx_rep_trx_detail_t itf

5778: -- IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
5779:
5780: SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
5781: INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
5782: FROM zx_rep_trx_detail_t itf
5783: WHERE itf.request_id = p_request_id
5784: AND itf.trx_id = p_trx_id_tbl(i)
5785: AND itf.tax_regime_code = p_tax_regime
5786: AND itf.tax = p_vat_perc_tax

Line 5894: FROM zx_rep_trx_detail_t

5890: BEGIN
5891:
5892: SELECT count(distinct trx_id)
5893: INTO l_trx_counter
5894: FROM zx_rep_trx_detail_t
5895: WHERE request_id = p_request_id;
5896:
5897: IF ( g_level_statement>= g_current_runtime_level ) THEN
5898: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 5931: FROM zx_rep_trx_detail_t itf

5927:
5928:
5929: SELECT NVL(SUM(itf.tax_amt),0)
5930: INTO l_other_fed_perc_amt_tbl(p_trx_id_tbl(i))
5931: FROM zx_rep_trx_detail_t itf
5932: WHERE itf.request_id = p_request_id
5933: AND itf.trx_id = p_trx_id_tbl(i)
5934: AND itf.tax_type_code >= P_FED_PERC_TAX_TYPE_FROM
5935: AND itf.tax_type_code <= NVL(P_FED_PERC_TAX_TYPE_TO, P_FED_PERC_TAX_TYPE_FROM);

Line 6034: FROM zx_rep_trx_detail_t

6030: BEGIN
6031:
6032: SELECT count(distinct trx_id)
6033: INTO l_trx_counter
6034: FROM zx_rep_trx_detail_t
6035: WHERE request_id = p_request_id;
6036:
6037: IF ( g_level_statement>= g_current_runtime_level ) THEN
6038: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6076: FROM zx_rep_trx_detail_t itf

6072: IF P_DOCUMENT_SUB_TYPE_TBL(i) = 'DOCUMENT TYPE.JLCLPPLR' THEN
6073:
6074: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6075: INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
6076: FROM zx_rep_trx_detail_t itf
6077: WHERE itf.request_id = P_REQUEST_ID
6078: AND itf.trx_id = p_trx_id_tbl(i)
6079: AND itf.tax_type_code <> 'VAT'
6080: AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y');

Line 6086: FROM zx_rep_trx_detail_t itf

6082: ELSE
6083:
6084: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6085: INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
6086: FROM zx_rep_trx_detail_t itf
6087: WHERE itf.request_id = p_request_id
6088: AND itf.trx_id = p_trx_id_tbl(i)
6089: AND itf.tax_type_code <> 'VAT';
6090:

Line 6124: FROM zx_rep_trx_detail_t itf

6120: -- OPEN ISSUE --
6121: --Bug 5438742
6122: SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
6123: INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
6124: FROM zx_rep_trx_detail_t itf
6125: WHERE itf.request_id = P_REQUEST_ID
6126: AND itf.trx_id = p_trx_id_tbl(i)
6127: AND itf.tax_type_code <> 'VAT' ;
6128: -- AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y');

Line 6240: FROM zx_rep_trx_detail_t

6236: BEGIN
6237:
6238: SELECT count(distinct trx_id)
6239: INTO l_trx_counter
6240: FROM zx_rep_trx_detail_t
6241: WHERE request_id = p_request_id;
6242:
6243: IF ( g_level_statement>= g_current_runtime_level ) THEN
6244: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6316: FROM zx_rep_trx_detail_t itf

6312: END IF;
6313:
6314: SELECT sum(nvl(itf.tax_amt,0))
6315: INTO l_vat_addit_tax_amt_tbl(p_trx_id_tbl(i))
6316: FROM zx_rep_trx_detail_t itf
6317: WHERE itf.request_id = p_request_id
6318: AND itf.trx_id = p_trx_id_tbl(i)
6319: AND itf.tax_regime_code = p_tax_regime
6320: AND itf.tax = p_vat_addit_tax

Line 6335: FROM zx_rep_trx_detail_t itf

6331: -- IF l_not_categ_tax_amt_tbl(p_trx_id_tbl(i)) is NULL THEN
6332: BEGIN
6333: SELECT sum(nvl(itf.tax_amt,0))
6334: INTO l_not_categ_tax_amt_tbl(p_trx_id_tbl(i))
6335: FROM zx_rep_trx_detail_t itf
6336: WHERE itf.request_id = p_request_id
6337: AND itf.trx_id = p_trx_id_tbl(i)
6338: AND itf.tax_regime_code = p_tax_regime
6339: AND itf.tax = p_vat_not_categ_tax

Line 6466: FROM zx_rep_trx_detail_t

6462: BEGIN
6463:
6464: SELECT count(distinct trx_id)
6465: INTO l_trx_counter
6466: FROM zx_rep_trx_detail_t
6467: WHERE request_id = p_request_id;
6468:
6469: l_count := p_trx_id_tbl.count;
6470:

Line 6512: FROM zx_rep_trx_detail_t itf

6508:
6509:
6510: SELECT NVL(SUM(itf.tax_amt),0)
6511: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6512: FROM zx_rep_trx_detail_t itf
6513: WHERE itf.request_id = p_request_id
6514: AND itf.trx_id = p_trx_id_tbl(i)
6515: AND itf.tax_type_code >= P_PROV_TAX_TYPE_FROM
6516: AND itf.tax_type_code <= P_PROV_TAX_TYPE_TO;

Line 6547: FROM zx_rep_trx_detail_t itf

6543: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
6544:
6545: SELECT sum(nvl(itf.tax_amt,0))
6546: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6547: FROM zx_rep_trx_detail_t itf
6548: WHERE itf.request_id = p_request_id
6549: AND itf.trx_id = p_trx_id_tbl(i)
6550: AND itf.tax_regime_code = p_prov_tax_regime
6551: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 6568: FROM zx_rep_trx_detail_t itf,

6564: END IF;
6565:
6566: SELECT sum(nvl(itf.tax_amt,0))
6567: INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
6568: FROM zx_rep_trx_detail_t itf,
6569: jl_zz_ar_tx_categ categ
6570: WHERE itf.request_id = p_request_id
6571: AND itf.trx_id = p_trx_id_tbl(i)
6572: -- AND itf.tax_regime_code = p_prov_tax_regime

Line 6680: FROM zx_rep_trx_detail_t

6676: BEGIN
6677:
6678: SELECT count(distinct trx_id)
6679: INTO l_trx_counter
6680: FROM zx_rep_trx_detail_t
6681: WHERE request_id = p_request_id;
6682:
6683: IF ( g_level_statement>= g_current_runtime_level ) THEN
6684: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6716: FROM zx_rep_trx_detail_t itf

6712: IF P_REPORT_NAME = 'JLARPPFF' THEN
6713:
6714: SELECT nvl(SUM(itf.tax_amt),0)
6715: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
6716: FROM zx_rep_trx_detail_t itf
6717: WHERE itf.request_id = p_request_id
6718: AND itf.trx_id = p_trx_id_tbl(i)
6719: AND itf.tax_type_code >= P_MUN_TAX_TYPE_FROM
6720: AND itf.tax_type_code <= P_MUN_TAX_TYPE_TO;

Line 6753: FROM zx_rep_trx_detail_t itf

6749: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
6750:
6751: SELECT sum(nvl(itf.tax_amt,0))
6752: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
6753: FROM zx_rep_trx_detail_t itf
6754: WHERE itf.request_id = p_request_id
6755: AND itf.trx_id = p_trx_id_tbl(i)
6756: AND itf.tax_regime_code = p_mun_tax_regime
6757: AND nvl(itf.tax_type_code,'VAT') = 'VAT';

Line 6775: FROM zx_rep_trx_detail_t itf,

6771: l_municipal_perc_amt_tbl(p_trx_id_tbl(i)):= NULL;
6772:
6773: SELECT sum(nvl(itf.tax_amt,0))
6774: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
6775: FROM zx_rep_trx_detail_t itf,
6776: jl_zz_ar_tx_categ categ
6777: WHERE itf.request_id = p_request_id
6778: AND itf.trx_id = p_trx_id_tbl(i)
6779: -- AND itf.tax_regime_code = p_mun_tax_regime

Line 6798: FROM zx_rep_trx_detail_t itf

6794:
6795:
6796: /* SELECT sum(nvl(itf.tax_amt,0))
6797: INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
6798: FROM zx_rep_trx_detail_t itf
6799: WHERE itf.request_id = p_request_id
6800: AND itf.trx_id = p_trx_id_tbl(i)
6801: AND itf.tax_regime_code = p_mun_tax_regime
6802: AND nvl(itf.tax_type_code,'VAT') = 'VAT'

Line 6899: FROM zx_rep_trx_detail_t

6895: BEGIN
6896:
6897: SELECT count(distinct trx_id)
6898: INTO l_trx_counter
6899: FROM zx_rep_trx_detail_t
6900: WHERE request_id = p_request_id;
6901:
6902: IF ( g_level_statement>= g_current_runtime_level ) THEN
6903: FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JL_EXTRACT_PKG',

Line 6933: FROM zx_rep_trx_detail_t itf

6929: IF P_REPORT_NAME = 'JLARPPFF' THEN
6930:
6931: SELECT nvl(SUM(itf.tax_amt),0)
6932: INTO l_excise_amt_tbl(p_trx_id_tbl(i))
6933: FROM zx_rep_trx_detail_t itf
6934: WHERE itf.request_id = p_request_id
6935: AND itf.trx_id = p_trx_id_tbl(i)
6936: AND itf.tax_type_code >= P_EXC_TAX_TYPE_FROM
6937: AND itf.tax_type_code <= P_EXC_TAX_TYPE_TO;

Line 6977: FROM zx_rep_trx_detail_t itf

6973: IF l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i)) = 'IS_NULL' THEN
6974:
6975: SELECT sum(nvl(itf.tax_amt,0))
6976: INTO l_excise_amt_tbl(p_trx_id_tbl(i))
6977: FROM zx_rep_trx_detail_t itf
6978: WHERE itf.request_id = p_request_id
6979: AND itf.trx_id = p_trx_id_tbl(i)
6980: --AND itf.tax_regime_code = p_exc_tax_regime
6981: AND itf.tax = p_exc_tax_regime

Line 7102: FROM ZX_REP_TRX_DETAIL_T DET

7098: -- X_CL_TOTAL_EXEMPT_TBL(i),
7099: -- X_CL_TOTAL_EFFECTIVE_TBL(i),
7100: X_CL_TOTAL_VAT_TAX_TBL(i),
7101: X_CL_TOTAL_OTHER_TAX_TBL(i)
7102: FROM ZX_REP_TRX_DETAIL_T DET
7103: WHERE DET.REQUEST_ID = P_REQUEST_ID
7104: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7105: GROUP BY DET.DOCUMENT_SUB_TYPE;
7106: EXCEPTION

Line 7124: FROM ZX_REP_TRX_DETAIL_T DET

7120: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
7121: INTO
7122: X_CL_TOTAL_EXEMPT_TBL(i),
7123: X_CL_TOTAL_EFFECTIVE_TBL(i)
7124: FROM ZX_REP_TRX_DETAIL_T DET
7125: WHERE DET.REQUEST_ID = P_REQUEST_ID
7126: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7127: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7128: WHERE det1.trx_id = det.trx_id

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

7123: X_CL_TOTAL_EFFECTIVE_TBL(i)
7124: FROM ZX_REP_TRX_DETAIL_T DET
7125: WHERE DET.REQUEST_ID = P_REQUEST_ID
7126: AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
7127: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7128: WHERE det1.trx_id = det.trx_id
7129: AND det1.request_id = P_REQUEST_ID
7130: AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
7131: GROUP BY DET.DOCUMENT_SUB_TYPE;

Line 7167: FROM ZX_REP_TRX_DETAIL_T DET

7163: coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0)))
7164: INTO X_CL_NUM_OF_DOC_TBL(i),
7165: X_CL_TOTAL_VAT_TAX_TBL(i),
7166: X_CL_TOTAL_OTHER_TAX_TBL(i)
7167: FROM ZX_REP_TRX_DETAIL_T DET
7168: WHERE DET.REQUEST_ID = P_REQUEST_ID
7169: AND DET.doc_seq_name = P_DOCUMENT_SUB_TYPE_TBL(i)
7170: GROUP BY DET.DOCUMENT_SUB_TYPE;
7171: EXCEPTION

Line 7185: FROM ZX_REP_TRX_DETAIL_T DET

7181: 0,
7182: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
7183: INTO
7184: X_CL_TOTAL_EFFECTIVE_TBL(i)
7185: FROM ZX_REP_TRX_DETAIL_T DET
7186: WHERE DET.REQUEST_ID = P_REQUEST_ID
7187: AND DET.DOC_SEQ_NAME = P_DOCUMENT_SUB_TYPE_TBL(i)
7188: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7189: WHERE det1.trx_id = det.trx_id

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

7184: X_CL_TOTAL_EFFECTIVE_TBL(i)
7185: FROM ZX_REP_TRX_DETAIL_T DET
7186: WHERE DET.REQUEST_ID = P_REQUEST_ID
7187: AND DET.DOC_SEQ_NAME = P_DOCUMENT_SUB_TYPE_TBL(i)
7188: AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
7189: WHERE det1.trx_id = det.trx_id
7190: AND det1.request_id = P_REQUEST_ID
7191: AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
7192: GROUP BY DET.DOCUMENT_SUB_TYPE;

Line 7326: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

7322: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
7323: | |
7324: | Parameters : |
7325: | IN : p_vat_tax IN |
7326: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
7327: | |
7328: | MODIFICATION HISTORY |
7329: | 07-Nov-03 Hidetaka Kojima created |
7330: | |

Line 7337: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

7333:
7334:
7335: FUNCTION GET_TAX_AUTHORITY_CODE
7336: (
7337: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
7338: P_ORG_ID IN NUMBER
7339: )
7340: return ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE IS
7341:

Line 7525: FROM zx_rep_trx_detail_t

7521: BEGIN
7522:
7523: SELECT count(distinct trx_id)
7524: INTO l_counted_trx_number
7525: FROM zx_rep_trx_detail_t
7526: WHERE request_id = p_request_id;
7527:
7528: EXCEPTION
7529:

Line 7701: FROM zx_rep_trx_detail_t

7697:
7698: BEGIN
7699: SELECT count(distinct trx_id)
7700: INTO l_counted_trx_number
7701: FROM zx_rep_trx_detail_t
7702: WHERE request_id = p_request_id;
7703: EXCEPTION
7704: WHEN OTHERS THEN
7705: IF ( g_level_statement>= g_current_runtime_level ) THEN

Line 7838: FROM zx_rep_trx_detail_t

7834: BEGIN
7835:
7836: SELECT count(distinct trx_id)
7837: INTO l_counted_trx_number
7838: FROM zx_rep_trx_detail_t
7839: WHERE request_id = p_request_id;
7840:
7841: IF (g_level_procedure >= g_current_runtime_level ) THEN
7842: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.GET_VAT_REG_STAT_CODE.BEGIN',

Line 8001: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

7997: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
7998: | |
7999: | Parameters : |
8000: | IN : p_vat_tax IN |
8001: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
8002: | |
8003: | MODIFICATION HISTORY |
8004: | 07-Nov-03 Hidetaka Kojima created |
8005: | |

Line 8012: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8008:
8009:
8010: PROCEDURE GET_REC_COUNT
8011: (
8012: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8013: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8014: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8015: P_REQUEST_ID IN NUMBER,
8016: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL

Line 8013: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

8009:
8010: PROCEDURE GET_REC_COUNT
8011: (
8012: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8013: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8014: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8015: P_REQUEST_ID IN NUMBER,
8016: X_REC_COUNT_TBL OUT NOCOPY NUMERIC11_TBL
8017: )

Line 8044: FROM zx_rep_trx_detail_t

8040: BEGIN
8041:
8042: SELECT count(distinct tax_rate)
8043: INTO l_rec_count_tbl(p_trx_id_tbl(i))
8044: FROM zx_rep_trx_detail_t
8045: WHERE request_id = p_request_id
8046: AND trx_id = p_trx_id_tbl(i)
8047: AND tax_regime_code = p_tax_regime
8048: AND tax = p_vat_tax;

Line 8179: UPDATE zx_rep_trx_detail_t dtl

8175: FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.UPDATE_DGI_CURR_CODE.END',
8176: 'UPDATE_DGI_CURR_CODE(+)');
8177: END IF;
8178:
8179: UPDATE zx_rep_trx_detail_t dtl
8180: SET gdf_fnd_currencies_att1= (SELECT global_attribute1
8181: FROM fnd_currencies
8182: WHERE currency_code = dtl.trx_currency_code)
8183: WHERE request_id = p_request_id;

Line 8206: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |

8202: | Called from ZX_JL_EXTRACT_PKG.POPULATE_JL_AR |
8203: | |
8204: | Parameters : |
8205: | IN : p_vat_tax IN |
8206: | ZX_REP_TRX_DETAIL_T.TAX%TYPE Required |
8207: | |
8208: | MODIFICATION HISTORY |
8209: | 07-Nov-03 Hidetaka Kojima created |
8210: | |

Line 8217: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8213:
8214:
8215: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8216: (
8217: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8218: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8219: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8220: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8221: P_REQUEST_ID IN NUMBER,

Line 8218: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,

8214:
8215: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8216: (
8217: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8218: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8219: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8220: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8221: P_REQUEST_ID IN NUMBER,
8222: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL

Line 8219: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,

8215: PROCEDURE GET_VAT_NONVAT_RATE_COUNT
8216: (
8217: P_VAT_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8218: P_VAT_NON_TAX IN ZX_REP_TRX_DETAIL_T.TAX%TYPE,
8219: P_TAX_REGIME IN ZX_REP_TRX_DETAIL_T.TAX_REGIME_CODE%TYPE,
8220: P_TRX_ID_TBL IN ZX_EXTRACT_PKG.TRX_ID_TBL,
8221: P_REQUEST_ID IN NUMBER,
8222: X_RATE_COUNT_TBL OUT NOCOPY NUMERIC13_TBL
8223: )

Line 8250: FROM zx_rep_trx_detail_t t1

8246: BEGIN
8247:
8248: SELECT count(distinct t1.tax_rate)
8249: INTO l_rate_count_tbl(p_trx_id_tbl(i))
8250: FROM zx_rep_trx_detail_t t1
8251: WHERE t1.request_id = p_request_id
8252: AND t1.trx_id = p_trx_id_tbl(i)
8253: AND NVL(t1.TAX_TYPE_CODE, 'VAT') = 'VAT'
8254: AND tax_regime_code = p_tax_regime

Line 8257: FROM zx_rep_trx_detail_t t2

8253: AND NVL(t1.TAX_TYPE_CODE, 'VAT') = 'VAT'
8254: AND tax_regime_code = p_tax_regime
8255: AND tax in (p_vat_tax, p_vat_non_tax)
8256: AND EXISTS ( SELECT 1
8257: FROM zx_rep_trx_detail_t t2
8258: WHERE t2.request_id = p_request_id
8259: AND t2.trx_id = t1.trx_id
8260: AND t2.tax_rate = 0
8261: AND t2.tax_regime_code = p_tax_regime

Line 9459: FROM ZX_REP_TRX_DETAIL_T DET

9455: SELECT SUM(DECODE(DET.TAX_RATE,0,
9456: 0,
9457: coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
9458: INTO l_total_doc_taxab_amt_tbl(p_trx_id_tbl(i))
9459: FROM ZX_REP_TRX_DETAIL_T DET
9460: WHERE REQUEST_ID = P_REQUEST_ID
9461: AND TRX_ID = p_trx_id_tbl(i);
9462:
9463: X_TOTAL_DOC_TAXAB_AMT_TBL(i) := l_total_doc_taxab_amt_tbl(p_trx_id_tbl(i));