[Home] [Help]
186:
187: IF ( g_level_statement >= g_current_runtime_level) THEN
188:
189: SELECT count(1) INTO l_count_header FROM zx_trx_headers_gt;
190: SELECT count(1) INTO l_count_lines FROM zx_transaction_lines_gt;
191:
192: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
193: 'Count of ZX_TRX_HEADERS_GT : '||to_char(l_count_header));
194: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
191:
192: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
193: 'Count of ZX_TRX_HEADERS_GT : '||to_char(l_count_header));
194: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
195: 'Count of ZX_TRANSACTION_LINES_GT : '||to_char(l_count_lines));
196: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
197: 'Count of ZX_IMPORT_TAX_LINES_GT - Total Tax Lines : '||to_char(l_count_tax_lines));
198: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
199: 'Count of ZX_IMPORT_TAX_LINES_GT - Tax Lines with Regime and Tax : '||to_char(l_count_reg_null));
501: l_org_id ZX_TRX_HEADERS_GT.internal_organization_id%Type;
502: l_effective_date Date;
503: l_return_status varchar2(30);
504:
505: --Bug 5018766 : Added for Defaulting the Tax Dates on the Newly added columns in zx_transaction_lines_gt
506: c_lines_per_commit CONSTANT NUMBER := ZX_TDS_CALC_SERVICES_PUB_PKG.G_LINES_PER_COMMIT;
507: l_application_id_tbl APPLICATION_ID_TBL;
508: l_entity_code_tbl ENTITY_CODE_TBL;
509: l_event_class_code_tbl EVENT_CLASS_CODE_TBL;
530: header.provnl_tax_determination_date,
531: Lines.adjusted_doc_date,
532: header.trx_date) subscription_date
533: FROM zx_trx_headers_gt header,
534: zx_transaction_lines_gt lines
535: WHERE header.application_id = lines.application_id
536: AND header.entity_code = lines.entity_code
537: AND header.event_class_code = lines.event_class_code
538: AND header.trx_id = lines.trx_id ;
550: --Bug 5018766 : Default the tax_date , tax_determine_date and tax_point_date for all the trx_lines
551: IF (g_level_statement >= g_current_runtime_level ) THEN
552: FND_LOG.STRING(g_level_statement,
553: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.DEFAULT_TAX_ATTR',
554: 'Stamping the Dates onto ZX_TRANSACTION_LINES_GT');
555: END IF;
556:
557: OPEN c_lines;
558: LOOP
571:
572: -- Stamp the tax_date , tax_determine_date and tax_point_date to the same value
573: IF ( l_count > 0 ) THEN
574: FORALL i IN 1 .. l_count
575: UPDATE zx_transaction_lines_gt
576: SET tax_date = l_trx_date_tbl(i),
577: tax_determine_date = l_trx_date_tbl(i),
578: tax_point_date = l_trx_date_tbl(i),
579: subscription_date = l_subscription_date_tbl(i)
627: TaxLines_gt.trx_id trx_id,
628: TaxLines_gt.summary_tax_line_number summary_tax_line_number
629: FROM ZX_IMPORT_TAX_LINES_GT TaxLines_gt,
630: ZX_TRX_HEADERS_GT Header,
631: ZX_TRANSACTION_LINES_GT Lines
632: WHERE TaxLines_gt.application_id = Header.application_id
633: AND TaxLines_gt.entity_code = Header.entity_code
634: AND TaxLines_gt.event_class_code = Header.event_class_code
635: AND TaxLines_gt.trx_id = Header.trx_id
679: TaxLines_gt.trx_id trx_id,
680: TaxLines_gt.summary_tax_line_number summary_tax_line_number
681: FROM ZX_IMPORT_TAX_LINES_GT TaxLines_gt,
682: ZX_TRX_HEADERS_GT Header,
683: ZX_TRANSACTION_LINES_GT Lines
684: WHERE TaxLines_gt.application_id = Header.application_id
685: AND TaxLines_gt.entity_code = Header.entity_code
686: AND TaxLines_gt.event_class_code = Header.event_class_code
687: AND TaxLines_gt.trx_id = Header.trx_id
746: TaxLines_gt.trx_id trx_id,
747: TaxLines_gt.summary_tax_line_number summary_tax_line_number
748: FROM ZX_IMPORT_TAX_LINES_GT TaxLines_gt,
749: ZX_TRX_HEADERS_GT Header,
750: ZX_TRANSACTION_LINES_GT Lines
751: WHERE TaxLines_gt.application_id = Header.application_id
752: AND TaxLines_gt.entity_code = Header.entity_code
753: AND TaxLines_gt.event_class_code = Header.event_class_code
754: AND TaxLines_gt.trx_id = Header.trx_id
763: AND lines.trx_line_id =
764: (
765: SELECT
766: MIN(trx_line_id)
767: FROM zx_transaction_lines_gt trans_line_gt
768: WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
769: AND trans_line_gt.application_id = taxlines_gt.application_id
770: AND trans_line_gt.entity_code = taxlines_gt.entity_code
771: AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
839: TaxLines_gt.summary_tax_line_number summary_tax_line_number
840: FROM ZX_FC_COUNTRY_DEFAULTS ContDef,
841: ZX_IMPORT_TAX_LINES_GT TaxLines_gt,
842: ZX_TRX_HEADERS_GT Header,
843: --ZX_TRANSACTION_LINES_GT Lines,
844: ZX_RATES_B rates,
845: ZX_SUBSCRIPTION_DETAILS sd_rates
846: WHERE TaxLines_gt.tax_rate_code = rates.tax_rate_code(+)
847: AND (Taxlines_gt.tax_rate_code IS NOT NULL OR Taxlines_gt.tax_rate_id IS NOT NULL)
887: --AND lines.trx_line_id =
888: --(
889: --SELECT
890: --MIN(trx_line_id)
891: --FROM zx_transaction_lines_gt trans_line_gt
892: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
893: --AND trans_line_gt.application_id = taxlines_gt.application_id
894: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
895: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
970: TaxLines_gt.summary_tax_line_number summary_tax_line_number
971: FROM ZX_FC_COUNTRY_DEFAULTS ContDef,
972: ZX_IMPORT_TAX_LINES_GT TaxLines_gt,
973: ZX_TRX_HEADERS_GT Header,
974: --ZX_TRANSACTION_LINES_GT Lines,
975: ZX_RATES_B rates,
976: ZX_SUBSCRIPTION_DETAILS sd_rates
977: WHERE TaxLines_gt.tax_rate_id = rates.tax_rate_id(+)
978: AND (Taxlines_gt.tax_rate_code IS NOT NULL OR Taxlines_gt.tax_rate_id IS NOT NULL)
1019: --AND lines.trx_line_id =
1020: --(
1021: --SELECT
1022: --MIN(trx_line_id)
1023: --FROM zx_transaction_lines_gt trans_line_gt
1024: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
1025: --AND trans_line_gt.application_id = taxlines_gt.application_id
1026: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
1027: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
1096: SET tax_status_code =
1097: (SELECT Status.tax_status_code
1098: FROM ZX_STATUS_B Status,
1099: ZX_TRX_HEADERS_GT Header,
1100: --ZX_TRANSACTION_LINES_GT Lines,
1101: ZX_SUBSCRIPTION_DETAILS sd_status,
1102: ZX_RATES_B rates,
1103: ZX_SUBSCRIPTION_DETAILS sd_rates
1104: WHERE Status.tax_regime_code = TaxLines.tax_regime_code
1149: --AND lines.trx_line_id =
1150: --(
1151: --SELECT
1152: --MIN(trx_line_id)
1153: --FROM zx_transaction_lines_gt trans_line_gt
1154: --WHERE trans_line_gt.trx_id = TaxLines.trx_id
1155: --AND trans_line_gt.application_id = TaxLines.application_id
1156: --AND trans_line_gt.entity_code = TaxLines.entity_code
1157: --AND trans_line_gt.event_class_code = TaxLines.event_class_code
1242: (SELECT /*+ leading (mapp) */ srvc.srvc_provider_id
1243: FROM ZX_SRVC_SUBSCRIPTIONS srvc,
1244: ZX_REGIMES_USAGES reg,
1245: ZX_TRX_HEADERS_GT Header,
1246: --ZX_TRANSACTION_LINES_GT Lines,
1247: ZX_EVNT_CLS_MAPPINGS mapp
1248: WHERE reg.tax_regime_code = TaxLines.tax_regime_code
1249: AND srvc.regime_usage_id = reg.regime_usage_id
1250: AND srvc.enabled_flag = 'Y'
1290: --AND lines.trx_line_id =
1291: --(
1292: --SELECT
1293: --MIN(trx_line_id)
1294: --FROM zx_transaction_lines_gt trans_line_gt
1295: --WHERE trans_line_gt.trx_id = TaxLines.trx_id
1296: --AND trans_line_gt.application_id = TaxLines.application_id
1297: --AND trans_line_gt.entity_code = TaxLines.entity_code
1298: --AND trans_line_gt.event_class_code = TaxLines.event_class_code
1342: SET tax_jurisdiction_code =
1343: (SELECT jur.TAX_JURISDICTION_CODE
1344: FROM ZX_JURISDICTIONS_B Jur,
1345: ZX_TRX_HEADERS_GT Header,
1346: --ZX_TRANSACTION_LINES_GT Lines,
1347: ZX_RATES_B rates,
1348: ZX_SUBSCRIPTION_DETAILS sd_rates
1349: WHERE Jur.tax = TaxLines.Tax
1350: AND Jur.tax_regime_code = TaxLines.Tax_Regime_Code
1398: --AND lines.trx_line_id =
1399: --(
1400: --SELECT
1401: --MIN(trx_line_id)
1402: --FROM zx_transaction_lines_gt trans_line_gt
1403: --WHERE trans_line_gt.trx_id = TaxLines.trx_id
1404: --AND trans_line_gt.application_id = TaxLines.application_id
1405: --AND trans_line_gt.entity_code = TaxLines.entity_code
1406: --AND trans_line_gt.event_class_code = TaxLines.event_class_code
1479: TaxLines_gt.trx_id trx_id,
1480: TaxLines_gt.summary_tax_line_number summary_tax_line_number
1481: FROM zx_rates_b rates,
1482: zx_trx_headers_gt header,
1483: --zx_transaction_lines_gt lines,
1484: zx_subscription_details sd_rates,
1485: zx_import_tax_lines_gt taxlines_gt
1486: WHERE TaxLines_gt.tax_regime_Code = rates.tax_regime_code(+)
1487: -- AND (Taxlines.tax_rate_code IS NOT NULL OR Taxlines.tax_rate_id IS NOT NULL)
1570: --( TaxLines.trx_line_id IS NULL
1571: --AND TaxLines.tax_line_allocation_flag = 'N'
1572: --AND lines.trx_line_id =
1573: --(SELECT MIN(trx_line_id)
1574: --FROM zx_transaction_lines_gt trans_line_gt
1575: --WHERE trans_line_gt.trx_id = TaxLines.trx_id
1576: --AND trans_line_gt.application_id = TaxLines.application_id
1577: --AND trans_line_gt.entity_code = TaxLines.entity_code
1578: --AND trans_line_gt.event_class_code = TaxLines.event_class_code
1621: TaxLines_gt.trx_id trx_id,
1622: TaxLines_gt.summary_tax_line_number summary_tax_line_number
1623: FROM zx_rates_b rates,
1624: zx_trx_headers_gt header,
1625: --zx_transaction_lines_gt lines,
1626: zx_subscription_details sd_rates,
1627: zx_import_tax_lines_gt taxlines_gt
1628: WHERE TaxLines_gt.tax_regime_Code = rates.tax_regime_code(+)
1629: -- AND (Taxlines.tax_rate_code IS NOT NULL OR Taxlines.tax_rate_id IS NOT NULL)
1753: TaxLines.trx_line_id
1754: FROM ZX_TRX_TAX_LINK_GT TaxLink,
1755: ZX_IMPORT_TAX_LINES_GT TaxLines,
1756: ZX_TRX_HEADERS_GT Header,
1757: ZX_TRANSACTION_LINES_GT Lines,
1758: ZX_TAXES_B tax,
1759: zx_rates_b rate,
1760: zx_subscription_details sd_tax,
1761: zx_subscription_details sd_rates
1805: AND lines.trx_line_id =
1806: (
1807: SELECT
1808: MIN(trx_line_id)
1809: FROM zx_transaction_lines_gt trans_line_gt
1810: WHERE trans_line_gt.trx_id = TaxLines.trx_id
1811: AND trans_line_gt.application_id = TaxLines.application_id
1812: AND trans_line_gt.entity_code = TaxLines.entity_code
1813: AND trans_line_gt.event_class_code = TaxLines.event_class_code
1966: lines_gt.trx_line_id,
1967: lines_gt.trx_level_type
1968: FROM ZX_TRX_HEADERS_GT header,
1969: ZX_EVNT_CLS_MAPPINGS evntmap,
1970: ZX_TRANSACTION_LINES_GT lines_gt,
1971: ZX_FC_PRODUCT_CATEGORIES_V fc_prodcat,
1972: ZX_FC_CODES_B fc_user,
1973: ZX_FC_DOCUMENT_FISCAL_V fc_doc,
1974: ZX_FC_BUSINESS_CATEGORIES_V fc_trx,
2017: -- ZX_RATES_B rate ,
2018: -- zx_rates_b off_rate,
2019: -- zx_import_tax_lines_gt temp_gt,
2020: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
2021: zx_transaction_lines_gt lines_gt,
2022: ZX_JURISDICTIONS_B jur,
2023: zx_subscription_details sd_reg,
2024: zx_subscription_details sd_tax,
2025: zx_subscription_details sd_status
2079: AND lines_gt.trx_line_id =
2080: (
2081: SELECT
2082: MIN(trx_line_id)
2083: FROM zx_transaction_lines_gt trans_line_gt
2084: WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
2085: AND trans_line_gt.application_id = taxlines_gt.application_id
2086: AND trans_line_gt.entity_code = taxlines_gt.entity_code
2087: AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
2221: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR.BEGIN',
2222: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR(+)');
2223: END IF;
2224:
2225: --Validations for ZX_TRX_HEADERS_GT, ZX_TRANSACTION_LINES_GT
2226: IF (g_level_statement >= g_current_runtime_level) THEN
2227: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
2228: 'Validations for ZX_TRX_HEADERS_GT and ZX_TRANSACTION_LINES_GT');
2229: END IF;
2224:
2225: --Validations for ZX_TRX_HEADERS_GT, ZX_TRANSACTION_LINES_GT
2226: IF (g_level_statement >= g_current_runtime_level) THEN
2227: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
2228: 'Validations for ZX_TRX_HEADERS_GT and ZX_TRANSACTION_LINES_GT');
2229: END IF;
2230:
2231: -- Select the key columns and write into fnd log for debug purpose
2232: IF ( g_level_statement >= g_current_runtime_level) THEN
2544: -- Check for existence of Control tax amount
2545: nvl2(header.ctrl_total_hdr_tx_amt,
2546: CASE WHEN ( NOT EXISTS
2547: (SELECT 1
2548: FROM ZX_TRANSACTION_LINES_GT lines1
2549: WHERE lines1.ctrl_hdr_tx_appl_flag = 'Y'
2550: AND lines1.trx_id = header.trx_id
2551: AND lines1.application_id = header.application_id
2552: AND lines1.entity_code = header.entity_code
2631: --Bug 4703541
2632: CASE WHEN (header.TRX_CURRENCY_CODE is NULL
2633: AND header.precision is NULL )
2634: AND EXISTS
2635: ( SELECT 1 FROM zx_transaction_lines_gt
2636: WHERE application_id = header.application_id
2637: AND entity_code = header.entity_code
2638: AND event_class_code = header.event_class_code
2639: AND trx_id = header.trx_id
2674: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
2675: 'No. of Rows inserted : '|| to_char(sql%ROWCOUNT) );
2676: END IF;
2677:
2678: /** 2. Validating for the Line Related Validations : zx_transaction_lines_gt **/
2679: g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2680: IF ( g_level_statement >= g_current_runtime_level) THEN
2681: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
2682: 'Running Trx Line Related Validations : ZX_TRANSACTION_LINES_GT');
2678: /** 2. Validating for the Line Related Validations : zx_transaction_lines_gt **/
2679: g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2680: IF ( g_level_statement >= g_current_runtime_level) THEN
2681: FND_LOG.STRING(g_level_statement,'ZX.PLSQL.ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
2682: 'Running Trx Line Related Validations : ZX_TRANSACTION_LINES_GT');
2683: END IF;
2684:
2685: INSERT ALL
2686: WHEN (ZX_LOCATION_MISSING = 'Y') THEN
4053: NOT BETWEEN fc_doc.effective_from
4054: AND nvl(fc_doc.effective_to, lines_gt.tax_date)
4055: AND (NOT EXISTS
4056: (SELECT 1
4057: FROM ZX_TRANSACTION_LINES_GT
4058: WHERE application_id = lines_gt.application_id
4059: AND entity_code = lines_gt.entity_code
4060: AND event_class_code = lines_gt.event_class_code
4061: AND trx_id = lines_gt.trx_id
4654: END TAX_RATE_CODE_NOT_ACTIVE
4655: FROM
4656: ZX_TRX_HEADERS_GT header,
4657: ZX_EVNT_CLS_MAPPINGS evntmap,
4658: ZX_TRANSACTION_LINES_GT lines_gt,
4659: --ZX_FC_PRODUCT_CATEGORIES_V fc_prodcat,
4660: --ZX_FC_CODES_B fc_user,
4661: ZX_FC_DOCUMENT_FISCAL_V fc_doc,
4662: --ZX_FC_BUSINESS_CATEGORIES_V fc_trx,
5426: -- ZX_RATES_B rate ,
5427: -- zx_rates_b off_rate,
5428: -- zx_import_tax_lines_gt temp_gt,
5429: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
5430: --zx_transaction_lines_gt lines_gt,
5431: ZX_JURISDICTIONS_B jur,
5432: zx_subscription_details sd_reg,
5433: zx_subscription_details sd_tax,
5434: zx_subscription_details sd_status
5486: --taxlines_gt.trx_line_id IS NULL
5487: --AND taxlines_gt.tax_line_allocation_flag = 'N'
5488: --AND lines_gt.trx_line_id =
5489: --(
5490: --SELECT /*+ index(trans_line_gt ZX_TRANSACTION_LINES_GT_U1) */
5491: --MIN(trx_line_id)
5492: --FROM zx_transaction_lines_gt trans_line_gt
5493: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
5494: --AND trans_line_gt.application_id = taxlines_gt.application_id
5488: --AND lines_gt.trx_line_id =
5489: --(
5490: --SELECT /*+ index(trans_line_gt ZX_TRANSACTION_LINES_GT_U1) */
5491: --MIN(trx_line_id)
5492: --FROM zx_transaction_lines_gt trans_line_gt
5493: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
5494: --AND trans_line_gt.application_id = taxlines_gt.application_id
5495: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
5496: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
6063: AND --All Alloc
6064: (tax_gt.trx_line_id IS NULL AND
6065: tax_gt.tax_line_allocation_flag = 'N' AND
6066: EXISTS (SELECT 1
6067: FROM zx_transaction_lines_gt trans_line_gt
6068: WHERE trans_line_gt.trx_id = tax_gt.trx_id
6069: AND trans_line_gt.application_id = tax_gt.application_id
6070: AND trans_line_gt.entity_code = tax_gt.entity_code
6071: AND trans_line_gt.event_class_code = tax_gt.event_class_code
6155: AND --All Alloc
6156: (tax_gt.trx_line_id IS NULL AND
6157: tax_gt.tax_line_allocation_flag = 'N' AND
6158: EXISTS (SELECT 1
6159: FROM zx_transaction_lines_gt trans_line_gt
6160: WHERE trans_line_gt.trx_id = tax_gt.trx_id
6161: AND trans_line_gt.application_id = tax_gt.application_id
6162: AND trans_line_gt.entity_code = tax_gt.entity_code
6163: AND trans_line_gt.event_class_code = tax_gt.event_class_code
6171: 'N'
6172: END ZX_TAX_MISSING_IN_ADJUSTED_TO
6173: FROM ZX_TRX_HEADERS_GT header,
6174: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
6175: zx_transaction_lines_gt lines_gt
6176: WHERE taxlines_gt.trx_id = header.trx_id
6177: AND taxlines_gt.application_id = Header.application_id
6178: AND taxlines_gt.entity_code = Header.entity_code
6179: AND taxlines_gt.event_class_code = Header.event_class_code
6602: AND --All Alloc
6603: (tax_gt.trx_line_id IS NULL AND
6604: tax_gt.tax_line_allocation_flag = 'N' AND
6605: EXISTS (SELECT 1
6606: FROM zx_transaction_lines_gt trans_line_gt
6607: WHERE trans_line_gt.trx_id = tax_gt.trx_id
6608: AND trans_line_gt.application_id = tax_gt.application_id
6609: AND trans_line_gt.entity_code = tax_gt.entity_code
6610: AND trans_line_gt.event_class_code = tax_gt.event_class_code
6694: AND --All Alloc
6695: (tax_gt.trx_line_id IS NULL AND
6696: tax_gt.tax_line_allocation_flag = 'N' AND
6697: EXISTS (SELECT 1
6698: FROM zx_transaction_lines_gt trans_line_gt
6699: WHERE trans_line_gt.trx_id = tax_gt.trx_id
6700: AND trans_line_gt.application_id = tax_gt.application_id
6701: AND trans_line_gt.entity_code = tax_gt.entity_code
6702: AND trans_line_gt.event_class_code = tax_gt.event_class_code
6710: 'N'
6711: END ZX_TAX_MISSING_IN_ADJUSTED_TO
6712: FROM ZX_TRX_HEADERS_GT header,
6713: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
6714: zx_transaction_lines_gt lines_gt
6715: WHERE taxlines_gt.trx_id = header.trx_id
6716: AND taxlines_gt.application_id = Header.application_id
6717: AND taxlines_gt.entity_code = Header.entity_code
6718: AND taxlines_gt.event_class_code = Header.event_class_code
7157: AND --All Alloc
7158: (tax_gt.trx_line_id IS NULL AND
7159: tax_gt.tax_line_allocation_flag = 'N' AND
7160: EXISTS (SELECT 1
7161: FROM zx_transaction_lines_gt trans_line_gt
7162: WHERE trans_line_gt.trx_id = tax_gt.trx_id
7163: AND trans_line_gt.application_id = tax_gt.application_id
7164: AND trans_line_gt.entity_code = tax_gt.entity_code
7165: AND trans_line_gt.event_class_code = tax_gt.event_class_code
7249: AND --All Alloc
7250: (tax_gt.trx_line_id IS NULL AND
7251: tax_gt.tax_line_allocation_flag = 'N' AND
7252: EXISTS (SELECT 1
7253: FROM zx_transaction_lines_gt trans_line_gt
7254: WHERE trans_line_gt.trx_id = tax_gt.trx_id
7255: AND trans_line_gt.application_id = tax_gt.application_id
7256: AND trans_line_gt.entity_code = tax_gt.entity_code
7257: AND trans_line_gt.event_class_code = tax_gt.event_class_code
7265: 'N'
7266: END ZX_TAX_MISSING_IN_ADJUSTED_TO
7267: FROM ZX_TRX_HEADERS_GT header,
7268: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
7269: zx_transaction_lines_gt lines_gt
7270: WHERE taxlines_gt.trx_id = header.trx_id
7271: AND taxlines_gt.application_id = Header.application_id
7272: AND taxlines_gt.entity_code = Header.entity_code
7273: AND taxlines_gt.event_class_code = Header.event_class_code
7281: taxlines_gt.trx_line_id IS NULL
7282: AND taxlines_gt.tax_line_allocation_flag = 'N'
7283: AND lines_gt.trx_line_id =
7284: (
7285: SELECT /*+ index(trans_line_gt ZX_TRANSACTION_LINES_GT_U1) */
7286: MIN(trx_line_id)
7287: FROM zx_transaction_lines_gt trans_line_gt
7288: WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
7289: AND trans_line_gt.application_id = taxlines_gt.application_id
7283: AND lines_gt.trx_line_id =
7284: (
7285: SELECT /*+ index(trans_line_gt ZX_TRANSACTION_LINES_GT_U1) */
7286: MIN(trx_line_id)
7287: FROM zx_transaction_lines_gt trans_line_gt
7288: WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
7289: AND trans_line_gt.application_id = taxlines_gt.application_id
7290: AND trans_line_gt.entity_code = taxlines_gt.entity_code
7291: AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
7488: ELSE 'N'
7489: END STATUS_SUBSCR_NOT_EFFECTIVE
7490: from
7491: zx_trx_headers_gt header,
7492: --zx_transaction_lines_gt lines_gt,
7493: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
7494: ZX_REGIMES_B regime ,
7495: ZX_TAXES_B tax ,
7496: ZX_STATUS_B status ,
7535: --AND lines_gt.trx_line_id =
7536: --(
7537: --SELECT
7538: --MIN(trx_line_id)
7539: --FROM zx_transaction_lines_gt trans_line_gt
7540: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
7541: --AND trans_line_gt.application_id = taxlines_gt.application_id
7542: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
7543: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
7891: ZX_RATES_B rate ,
7892: zx_rates_b off_rate,
7893: zx_import_tax_lines_gt temp_gt,
7894: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
7895: --zx_transaction_lines_gt lines_gt,
7896: zx_subscription_details sd_rates
7897: WHERE taxlines_gt.trx_id = header.trx_id
7898: AND taxlines_gt.application_id = Header.application_id
7899: AND taxlines_gt.entity_code = Header.entity_code
7934: --AND lines_gt.trx_line_id =
7935: --(
7936: --SELECT
7937: --MIN(trx_line_id)
7938: --FROM zx_transaction_lines_gt trans_line_gt
7939: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
7940: --AND trans_line_gt.application_id = taxlines_gt.application_id
7941: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
7942: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
8168: ZX_RATES_B rate ,
8169: zx_rates_b off_rate,
8170: zx_import_tax_lines_gt temp_gt,
8171: ZX_IMPORT_TAX_LINES_GT taxlines_gt,
8172: --zx_transaction_lines_gt lines_gt,
8173: zx_subscription_details sd_rates
8174: WHERE taxlines_gt.trx_id = header.trx_id
8175: AND taxlines_gt.application_id = Header.application_id
8176: AND taxlines_gt.entity_code = Header.entity_code
8211: --AND lines_gt.trx_line_id =
8212: --(
8213: --SELECT
8214: --MIN(trx_line_id)
8215: --FROM zx_transaction_lines_gt trans_line_gt
8216: --WHERE trans_line_gt.trx_id = taxlines_gt.trx_id
8217: --AND trans_line_gt.application_id = taxlines_gt.application_id
8218: --AND trans_line_gt.entity_code = taxlines_gt.entity_code
8219: --AND trans_line_gt.event_class_code = taxlines_gt.event_class_code
8402: summary_tax_line_number,
8403: trx_level_type,
8404: interface_tax_entity_code,
8405: interface_tax_line_id,
8406: -- Check if the Trx Lines present in Link GTT are also present in ZX_TRANSACTION_LINES_GT or not
8407: CASE WHEN NOT EXISTS (SELECT 1
8408: FROM zx_transaction_lines_gt
8409: WHERE application_id = link_gt.application_id
8410: AND entity_code = link_gt.entity_code
8404: interface_tax_entity_code,
8405: interface_tax_line_id,
8406: -- Check if the Trx Lines present in Link GTT are also present in ZX_TRANSACTION_LINES_GT or not
8407: CASE WHEN NOT EXISTS (SELECT 1
8408: FROM zx_transaction_lines_gt
8409: WHERE application_id = link_gt.application_id
8410: AND entity_code = link_gt.entity_code
8411: AND event_class_code = link_gt.event_class_code
8412: AND trx_id = link_gt.trx_id
8503: header.trx_id,
8504: lines_gt.trx_line_id,
8505: lines_gt.trx_level_type
8506: FROM ZX_TRX_HEADERS_GT header,
8507: ZX_TRANSACTION_LINES_GT lines_gt
8508: WHERE lines_gt.application_id = header.application_id
8509: AND lines_gt.entity_code = header.entity_code
8510: AND lines_gt.event_class_code = header.event_class_code
8511: AND lines_gt.trx_id = header.trx_id;
8813: AND entity_code = header.related_doc_entity_code
8814: AND event_class_code = header.related_doc_event_class_code
8815: AND trx_id = header.related_doc_trx_id))
8816: AND ( NOT EXISTS
8817: (SELECT 1 FROM ZX_TRANSACTION_LINES_GT
8818: WHERE application_id = lines_gt.application_id
8819: AND entity_code = lines_gt.entity_code
8820: AND event_class_code = lines_gt.event_class_code
8821: AND trx_id = lines_gt.trx_id
8848: AND trx_level_type = lines_gt.ref_doc_trx_level_type)
8849: )
8850: AND ( NOT EXISTS
8851: (SELECT 1
8852: FROM ZX_TRANSACTION_LINES_GT
8853: WHERE application_id = lines_gt.ref_doc_application_id
8854: AND entity_code = lines_gt.ref_doc_entity_code
8855: AND event_class_code = lines_gt.ref_doc_event_class_code
8856: AND trx_id = lines_gt.ref_doc_trx_id
8873: AND trx_id = lines_gt.applied_from_trx_id
8874: AND trx_line_id = lines_gt.applied_from_line_id
8875: AND trx_level_type = lines_gt.applied_from_trx_level_type))
8876: AND ( NOT EXISTS
8877: (SELECT 1 FROM ZX_TRANSACTION_LINES_GT
8878: WHERE application_id = lines_gt.applied_from_application_id
8879: AND entity_code = lines_gt.applied_from_entity_code
8880: AND event_class_code = lines_gt.applied_from_event_class_code
8881: AND trx_id = lines_gt.applied_from_trx_id
8898: AND trx_id = lines_gt.adjusted_doc_trx_id
8899: AND trx_line_id = lines_gt.adjusted_doc_line_id
8900: AND trx_level_type = lines_gt.adjusted_doc_trx_level_type))
8901: AND ( NOT EXISTS
8902: (SELECT 1 FROM ZX_TRANSACTION_LINES_GT
8903: WHERE application_id = lines_gt.adjusted_doc_application_id
8904: AND entity_code = lines_gt.adjusted_doc_entity_code
8905: AND event_class_code = lines_gt.adjusted_doc_event_class_code
8906: AND trx_id = lines_gt.adjusted_doc_trx_id
8927: AND trx_id = lines_gt.applied_to_trx_id
8928: AND trx_line_id = lines_gt.applied_to_trx_line_id
8929: AND trx_level_type = lines_gt.applied_to_trx_level_type))
8930: AND ( NOT EXISTS
8931: (SELECT 1 FROM ZX_TRANSACTION_LINES_GT
8932: WHERE application_id = lines_gt.applied_to_application_id
8933: AND entity_code = lines_gt.applied_to_entity_code
8934: AND event_class_code = lines_gt.applied_to_event_class_code
8935: AND trx_id = lines_gt.applied_to_trx_id
8966: AND line.trx_line_id = lines_gt.source_line_id
8967: AND line.trx_level_type = lines_gt.source_trx_level_type)
8968: )
8969: AND ( NOT EXISTS
8970: (SELECT 1 FROM ZX_TRANSACTION_LINES_GT line,
8971: zx_evnt_cls_mappings map
8972: WHERE lines_gt.application_id = map.application_id
8973: AND lines_gt.entity_code = map.entity_code
8974: AND lines_gt.event_class_code = map.event_class_code
8994: ELSE NULL
8995: END,
8996: NULL) ZX_SOURCE_DOC_MISSING
8997: FROM ZX_TRX_HEADERS_GT header,
8998: ZX_TRANSACTION_LINES_GT lines_gt
8999: WHERE lines_gt.application_id = header.application_id
9000: AND lines_gt.entity_code = header.entity_code
9001: AND lines_gt.event_class_code = header.event_class_code
9002: AND lines_gt.trx_id = header.trx_id;
9161: L.defaulting_attribute10
9162: -- L.input_tax_classification_code, --Bug 4919842
9163: -- L.output_tax_classification_code --Bug 4919842
9164: FROM ZX_TRX_HEADERS_GT H,
9165: ZX_TRANSACTION_LINES_GT L
9166: WHERE L.application_id = H.application_id
9167: AND L.entity_code = H.entity_code
9168: AND L.event_class_code = H.event_class_code
9169: AND L.trx_id = H.trx_id
9259: l_definforec := l_definforec_null;
9260:
9261: --
9262: -- populate fields in the record with values
9263: -- from zx_trx_headers_gt and zx_transaction_lines_gt
9264: --
9265: -- Bug#4310278- replace line_location_id with
9266: -- ref_doc columns
9267:
9341: END IF;
9342:
9343: --
9344: -- store returned tax classification code for update to
9345: -- zx_transaction_lines_gt later
9346: --
9347: l_input_tax_classif_code_tbl(i) := l_definforec.input_tax_classification_code;
9348: l_output_tax_classif_code_tbl(i) := l_definforec.output_tax_classification_code;
9349: -- END IF ; -- end if for the conditional call
9353: EXIT;
9354: END IF;
9355:
9356: --
9357: -- update zx_transaction_lines_gt with the default
9358: -- tax classification code found
9359: --
9360: IF (g_level_statement >= g_current_runtime_level ) THEN
9361: FND_LOG.STRING(g_level_statement,
9359: --
9360: IF (g_level_statement >= g_current_runtime_level ) THEN
9361: FND_LOG.STRING(g_level_statement,
9362: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.POP_DEF_TAX_CLASSIF_CODE',
9363: 'update zx_transaction_lines_gt with the default tax classification code found ');
9364: END IF;
9365:
9366: FORALL i IN 1 .. l_count
9367: UPDATE ZX_TRANSACTION_LINES_GT
9363: 'update zx_transaction_lines_gt with the default tax classification code found ');
9364: END IF;
9365:
9366: FORALL i IN 1 .. l_count
9367: UPDATE ZX_TRANSACTION_LINES_GT
9368: SET input_tax_classification_code = l_input_tax_classif_code_tbl(i),
9369: output_tax_classification_code = l_output_tax_classif_code_tbl(i)
9370: WHERE application_id = l_application_id_tbl(i)
9371: AND entity_code = l_entity_code_tbl(i)
9835: INTERFACE_LINE_ID ,
9836: HISTORICAL_TAX_CODE_ID ,
9837: USER_UPD_DET_FACTORS_FLAG -- Bug 4703541
9838: FROM
9839: ZX_TRANSACTION_LINES_GT Lines,
9840: ZX_TRX_HEADERS_GT Header
9841:
9842: WHERE
9843: Lines.application_id = Header.application_id
10359: RETURN;
10360: END IF;
10361: END LOOP;
10362:
10363: -- update zx_transaction_lines_gt with the default
10364: -- tax attributes found
10365: IF (g_level_statement >= g_current_runtime_level ) THEN
10366: FND_LOG.STRING(g_level_statement,
10367: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.def_additional_tax_attribs',
10364: -- tax attributes found
10365: IF (g_level_statement >= g_current_runtime_level ) THEN
10366: FND_LOG.STRING(g_level_statement,
10367: 'ZX.PLSQL.ZX_VALIDATE_API_PKG.def_additional_tax_attribs',
10368: 'updating zx_transaction_lines_gt with tax attributes found');
10369: END IF;
10370:
10371: FORALL i IN 1..nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID.last,0)
10372:
10369: END IF;
10370:
10371: FORALL i IN 1..nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID.last,0)
10372:
10373: UPDATE /*+ INDEX (z,ZX_TRANSACTION_LINES_GT_U1) */ ZX_TRANSACTION_LINES_GT z
10374: SET trx_business_category =
10375: DECODE(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.SOURCE_APPLICATION_ID(I),
10376: NULL , DECODE(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.REF_DOC_APPLICATION_ID(i),
10377: NULL, ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.TRX_BUSINESS_CATEGORY(i),