[Home] [Help]
579: 'SYNCHRONIZE','U',
580: 'DELETE','U') ,
581: null)INSERT_UPDATE_FLAG
582: /* The update insert flag is to determine the records that need to be inserted/updated
583: into zx_lines_det_factors depending on the line_level_action for tax event type UPDATE*/
584: FROM ZX_TRANSACTION_LINES_GT trxlines,
585: ZX_TRX_HEADERS_GT header
586: WHERE header.application_id = p_event_class_rec.application_id
587: AND header.entity_code = p_event_class_rec.entity_code
954: 'CANCEL','U',
955: 'SYNCHRONIZE','U',
956: 'DELETE','U') INSERT_UPDATE_FLAG
957: /* The update insert flag is to determine the records that need to be inserted/updated
958: into zx_lines_det_factors depending on the line_level_action for tax event type UPDATE*/
959: FROM ZX_TRANSACTION_LINES_GT trxlines,
960: ZX_TRX_HEADERS_GT header
961: WHERE trxlines.application_id = header.application_id
962: AND trxlines.entity_code = header.entity_code
1259: GLOBAL_ATTRIBUTE_CATEGORY,
1260: GLOBAL_ATTRIBUTE1,
1261: TOTAL_INC_TAX_AMT,
1262: USER_UPD_DET_FACTORS_FLAG
1263: FROM ZX_LINES_DET_FACTORS
1264: WHERE event_id = p_event_id;
1265:
1266: /* Cursor for windows (Determining Factors) version of calculate tax*/
1267: CURSOR get_lines_det_factors(p_event_class_rec zx_api_pub.event_class_rec_type) IS
1559: GLOBAL_ATTRIBUTE_CATEGORY,
1560: GLOBAL_ATTRIBUTE1,
1561: TOTAL_INC_TAX_AMT,
1562: USER_UPD_DET_FACTORS_FLAG
1563: FROM ZX_LINES_DET_FACTORS
1564: WHERE application_id = p_event_class_rec.application_id
1565: AND entity_code = p_event_class_rec.entity_code
1566: AND event_class_code = p_event_class_rec.event_class_code
1567: AND trx_id = p_event_class_rec.trx_id
1573: zl.tax_regime_code,
1574: zl.tax_provider_id,
1575: regimes.effective_from,
1576: regimes.effective_to
1577: FROM ZX_LINES zl,
1578: ZX_REGIMES_B regimes
1579: WHERE zl.application_id = p_event_class_rec.application_id
1580: AND zl.entity_code = p_event_class_rec.entity_code
1581: AND zl.event_class_code = p_event_class_rec.event_class_code
1609: zxlines.tax_provider_id,
1610: zxlines.trx_line_id,
1611: regimes.effective_from,
1612: regimes.effective_to
1613: FROM ZX_LINES zxlines,
1614: ZX_REGIMES_B regimes
1615: WHERE zxlines.application_id = p_event_class_rec.application_id
1616: AND zxlines.entity_code = p_event_class_rec.entity_code
1617: AND zxlines.event_class_code = p_event_class_rec.event_class_code
1680: -- PRIVATE PROCEDURE
1681: -- db_insert_line_det_factors
1682: --
1683: -- DESCRIPTION
1684: -- Bulk insert into zx_lines_det_factors
1685: --
1686: -- CALLED BY
1687: -- calculate_tax
1688: -- import_document_with_tax
1690: -- insupd_line_det_factors
1691: -----------------------------------------------------------------------
1692: /*----------------------------------------------------------------------------*
1693: |p_insert needed in case of UPDATE tax event type wherein some records passed|
1694: |need to be updated in zx_lines_det_factors while others need to be inserted |
1695: |depending on the line level action. This scenario is applicable only for |
1696: |Calculate_tax API. p_insert stores the index upto which the records from the|
1697: |collection needs to be inserted into zx_lines_det_factors. |
1698: *----------------------------------------------------------------------------*/
1693: |p_insert needed in case of UPDATE tax event type wherein some records passed|
1694: |need to be updated in zx_lines_det_factors while others need to be inserted |
1695: |depending on the line level action. This scenario is applicable only for |
1696: |Calculate_tax API. p_insert stores the index upto which the records from the|
1697: |collection needs to be inserted into zx_lines_det_factors. |
1698: *----------------------------------------------------------------------------*/
1699: PROCEDURE db_insert_line_det_factors
1700: (p_trx_line_dist_tbl IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE,
1701: p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1710:
1711: -- Set the return status to Success
1712: x_return_status := FND_API.G_RET_STS_SUCCESS;
1713:
1714: -- Insert records in ZX_LINES_DET_FACTORS
1715: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id
1716: INSERT INTO ZX_LINES_DET_FACTORS(
1717: EVENT_ID,
1718: INTERNAL_ORGANIZATION_ID,
1712: x_return_status := FND_API.G_RET_STS_SUCCESS;
1713:
1714: -- Insert records in ZX_LINES_DET_FACTORS
1715: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id
1716: INSERT INTO ZX_LINES_DET_FACTORS(
1717: EVENT_ID,
1718: INTERNAL_ORGANIZATION_ID,
1719: APPLICATION_ID,
1720: ENTITY_CODE,
2336: -- PRIVATE PROCEDURE
2337: -- db_update_line_det_factors
2338: --
2339: -- DESCRIPTION
2340: -- Bulk update into zx_lines_det_factors
2341: --
2342: -- CALLED BY
2343: -- calculate_tax
2344: -- insupd_line_det_factors
2344: -- insupd_line_det_factors
2345: -----------------------------------------------------------------------
2346: /*----------------------------------------------------------------------------*
2347: |p_update needed in case of UPDATE tax event type wherein some records passed|
2348: |need to be updated in zx_lines_det_factors while others need to be inserted |
2349: |depending on the line level action. This scenario is applicable only for |
2350: |Calculate_tax API. p_update stores the index from which the lines from |
2351: |the collection needs to be updated to zx_lines_det_factors |
2352: *----------------------------------------------------------------------------*/
2347: |p_update needed in case of UPDATE tax event type wherein some records passed|
2348: |need to be updated in zx_lines_det_factors while others need to be inserted |
2349: |depending on the line level action. This scenario is applicable only for |
2350: |Calculate_tax API. p_update stores the index from which the lines from |
2351: |the collection needs to be updated to zx_lines_det_factors |
2352: *----------------------------------------------------------------------------*/
2353:
2354: PROCEDURE db_update_line_det_factors
2355: (p_trx_line_dist_tbl IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE,
2401: p_event_class_rec.quote_flag ='N' AND
2402: p_event_class_rec.tax_event_type_code IN('UPDATE','CREATE'))
2403: THEN
2404: FORALL i IN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) .. NVL(p_update_index,-99)
2405: UPDATE ZX_LINES_DET_FACTORS SET
2406: EVENT_ID = p_event_class_rec.event_id,
2407: INTERNAL_ORGANIZATION_ID = p_trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i),
2408: EVENT_TYPE_CODE = p_trx_line_dist_tbl.EVENT_TYPE_CODE(i),
2409: DOC_EVENT_STATUS = p_event_class_rec.DOC_STATUS_CODE,
2694: AND err_gt.event_class_code = p_trx_line_dist_tbl.event_class_code(i)
2695: AND err_gt.trx_id = p_trx_line_dist_tbl.trx_id(i));
2696: ELSE
2697: FORALL i IN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) .. NVL(p_update_index,-99)
2698: UPDATE ZX_LINES_DET_FACTORS SET
2699: EVENT_ID = p_event_class_rec.event_id,
2700: INTERNAL_ORGANIZATION_ID = p_trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i),
2701: EVENT_TYPE_CODE = p_trx_line_dist_tbl.EVENT_TYPE_CODE(i),
2702: DOC_EVENT_STATUS = p_event_class_rec.DOC_STATUS_CODE,
3027: -----------------------------------------------------------------------
3028:
3029: PROCEDURE insert_detail_tax_lines_gt
3030: (p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
3031: p_sync_with_prvdr_flag IN ZX_LINES.sync_with_prvdr_flag%type,
3032: p_ptnr_tax_lines_insert OUT NOCOPY NUMBER, -- Bug 5332192
3033: x_return_status OUT NOCOPY VARCHAR2
3034: )IS
3035: l_api_name CONSTANT VARCHAR2(30):= 'INSERT_DETAIL_TAX_LINES_GT';
3636: L.interface_tax_line_id,
3637: L.taxing_juris_geography_id,
3638: L.adjusted_doc_tax_line_id,
3639: 1
3640: FROM ZX_LINES L
3641: WHERE application_id = p_event_class_rec.application_id
3642: AND entity_code = p_event_class_rec.entity_code
3643: AND event_class_code = p_event_class_rec.event_class_code
3644: AND trx_id = p_event_class_rec.trx_id
3668: -- PRIVATE PROCEDURE
3669: -- call_lte
3670: --
3671: -- DESCRIPTION
3672: -- Wrapper to fetch all lines from zx_lines_det_factors before calling LTE
3673: -- to calculate_tax
3674: --
3675: -- CALLED BY
3676: -- calculate_tax
3981: GLOBAL_ATTRIBUTE_CATEGORY,
3982: GLOBAL_ATTRIBUTE1,
3983: TOTAL_INC_TAX_AMT,
3984: USER_UPD_DET_FACTORS_FLAG
3985: FROM ZX_LINES_DET_FACTORS
3986: WHERE application_id = p_event_class_rec.application_id
3987: AND entity_code = p_event_class_rec.entity_code
3988: AND event_class_code = p_event_class_rec.event_class_code
3989: AND trx_id = p_event_class_rec.trx_id
4347: x_return_status OUT NOCOPY VARCHAR2
4348: ) IS
4349: l_api_name CONSTANT VARCHAR2(30):= 'LOCK_LINE_DET_FACTS';
4350: l_return_status VARCHAR2(30);
4351: l_lines_det_rec zx_lines_det_factors%rowtype;
4352: l_context_info_rec ZX_API_PUB.context_info_rec_type;
4353:
4354: BEGIN
4355: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4361: IF p_event_class_rec.TAX_EVENT_TYPE_CODE = 'UPDATE' THEN
4362: /*Lock trx line det factors*/
4363: SELECT *
4364: INTO l_lines_det_rec
4365: FROM ZX_LINES_DET_FACTORS
4366: WHERE application_id = p_event_class_rec.application_id
4367: AND entity_code = p_event_class_rec.entity_code
4368: AND event_class_code = p_event_class_rec.event_class_code
4369: AND trx_id = p_event_class_rec.trx_id
5025: -- populate_parties
5026: --
5027: -- DESCRIPTION
5028: -- Retrieve the following from hz_parties given the party_tax_profile ids
5029: -- from zx_lines_det_factors and zx_party_tax_profile
5030: -- SHIP_TO_PARTY_NUMBER
5031: -- SHIP_TO_PARTY_NAME
5032: -- SHIP_FROM_PARTY_NUMBER
5033: -- SHIP_FROM_PARTY_NAME
5053: l_ship_to_ptp_id NUMBER;
5054: l_ship_from_ptp_id NUMBER;
5055: l_bill_to_ptp_id NUMBER;
5056: l_bill_from_ptp_id NUMBER;
5057: l_ship_third_pty_acct_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_ID%type;
5058: l_bill_third_pty_acct_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%type;
5059: l_party_index VARCHAR2(100);
5060: BEGIN
5061: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5054: l_ship_from_ptp_id NUMBER;
5055: l_bill_to_ptp_id NUMBER;
5056: l_bill_from_ptp_id NUMBER;
5057: l_ship_third_pty_acct_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_ID%type;
5058: l_bill_third_pty_acct_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%type;
5059: l_party_index VARCHAR2(100);
5060: BEGIN
5061: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5062: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
5063: END IF;
5064:
5065: x_return_status := FND_API.G_RET_STS_SUCCESS;
5066:
5067: --Get the ptp ids from zx_lines_det_factors
5068: SELECT ship_to_party_tax_prof_id,
5069: bill_to_party_tax_prof_id,
5070: ship_from_party_tax_prof_id,
5071: bill_from_party_tax_prof_id,
5076: l_ship_from_ptp_id,
5077: l_bill_from_ptp_id,
5078: l_ship_third_pty_acct_id, -- Bug 4939819
5079: l_bill_third_pty_acct_id -- Bug 4939819
5080: FROM ZX_LINES_DET_FACTORS
5081: WHERE event_class_mapping_id = p_event_class_rec.event_class_mapping_id -- Bug 4939819
5082: AND trx_id = p_trx_id
5083: AND trx_line_id = p_trx_line_id
5084: AND trx_level_type = p_trx_level_type;
6557: tax.tax_regime_id,
6558: tax.tax_provider_id,
6559: regimes.effective_from,
6560: regimes.effective_to
6561: FROM ZX_LINES_DET_FACTORS lines,
6562: ZX_LINES tax,
6563: ZX_REGIMES_B regimes
6564: WHERE lines.application_id = p_event_class_rec.application_id
6565: AND lines.entity_code = p_event_class_rec.entity_code
6558: tax.tax_provider_id,
6559: regimes.effective_from,
6560: regimes.effective_to
6561: FROM ZX_LINES_DET_FACTORS lines,
6562: ZX_LINES tax,
6563: ZX_REGIMES_B regimes
6564: WHERE lines.application_id = p_event_class_rec.application_id
6565: AND lines.entity_code = p_event_class_rec.entity_code
6566: AND lines.event_class_code = p_event_class_rec.event_class_code
6683: l_tax_provider_id NUMBER;
6684: l_context_info_rec ZX_API_PUB.context_info_rec_type;
6685: l_incl_override VARCHAR2(1);
6686:
6687: --Fetch the transaction lines from ZX_LINES_DET_FACTORS for which inclusive tax line, calculated by Oracle E-Business Tax,
6688: --has been overridden and partner has been involved in the calculation of taxes of at least one regime and the transaction line
6689: --amount includes the inclusive tax amount.The transaction line containing inclusive taxes can be identified by the flag
6690: --INCLUSIVE_TAX_OVERRIDE_FLAG = 'Y' in ZX_LINES_DET_FACTORS.
6691:
6686:
6687: --Fetch the transaction lines from ZX_LINES_DET_FACTORS for which inclusive tax line, calculated by Oracle E-Business Tax,
6688: --has been overridden and partner has been involved in the calculation of taxes of at least one regime and the transaction line
6689: --amount includes the inclusive tax amount.The transaction line containing inclusive taxes can be identified by the flag
6690: --INCLUSIVE_TAX_OVERRIDE_FLAG = 'Y' in ZX_LINES_DET_FACTORS.
6691:
6692: CURSOR ptnr_incl_lines_csr(p_event_class_rec ZX_API_PUB.event_class_rec_type) IS
6693: SELECT distinct
6694: tax.tax_regime_code,
6695: tax.tax_regime_id,
6696: tax.tax_provider_id,
6697: regimes.effective_from,
6698: regimes.effective_to
6699: FROM ZX_LINES_DET_FACTORS lines,
6700: ZX_LINES tax,
6701: ZX_REGIMES_B regimes
6702: WHERE lines.application_id = p_event_class_rec.application_id
6703: AND lines.entity_code = p_event_class_rec.entity_code
6696: tax.tax_provider_id,
6697: regimes.effective_from,
6698: regimes.effective_to
6699: FROM ZX_LINES_DET_FACTORS lines,
6700: ZX_LINES tax,
6701: ZX_REGIMES_B regimes
6702: WHERE lines.application_id = p_event_class_rec.application_id
6703: AND lines.entity_code = p_event_class_rec.entity_code
6704: AND lines.event_class_code = p_event_class_rec.event_class_code
7129: | We need to skip the logic to get ptps if they already exist in trx_line_dist_tbl.|
7130: | This is the case when import is called after call to validate_and_default routine|
7131: | which derives and defaults the values back to GT and also in the case when |
7132: | calculate tax is called by products uptaking the determining factors UI where the|
7133: | transaction line information is alredy present in zx_lines_det_factors and |
7134: | fetched into trx_line_dist_tbl |
7135: +---------------------------------------------------------------------------------*/
7136: /* Skip if the ship_to_party_tax_prof_id is already populated*/
7137: IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_to_party_tax_prof_id(p_trx_line_index) is NULL THEN
8232: SELECT record_type_code,
8233: partner_migrated_flag
8234: INTO l_record_type,
8235: l_partner_migrated_flag
8236: FROM ZX_LINES_DET_FACTORS
8237: WHERE application_id = p_event_class_rec.application_id
8238: AND entity_code = p_event_class_rec.entity_code
8239: AND event_class_code = p_event_class_rec.event_class_code
8240: AND trx_id = p_event_class_rec.trx_id
8241: AND rownum = 1;
8242: EXCEPTION WHEN OTHERS THEN --Bug fix 5094078
8243: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
8244: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
8245: ' No row found in zx_lines_det_factors for UPDATE event type' );
8246: END IF;
8247: END;
8248:
8249: IF l_partner_migrated_flag = 'Y' THEN
8337: END IF;
8338: BEGIN
8339: SELECT 'Y'
8340: INTO l_deleted_line_exists
8341: FROM ZX_LINES_DET_FACTORS
8342: WHERE application_id = p_event_class_rec.application_id
8343: AND entity_code = p_event_class_rec.entity_code
8344: AND event_class_code = p_event_class_rec.event_class_code
8345: AND trx_id = p_event_class_rec.trx_id
8446: END IF;
8447:
8448:
8449: /*Check if partner_processing_flag = 'N', then
8450: Delete Provider Tax Lines from the ZX_LINES and references to the provider in the ZX_TRX_LINE_APP_REGIMES for that regime.
8451: If partner recording is allowed for this event class, then
8452: Call partner's service Modify Doc Status with action DELETE*/
8453:
8454: IF ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(l_regime_index) AND
8471: RETURN;
8472: END IF;
8473: END IF; --record_for_partners_flag
8474:
8475: /*Delete Provider Tax Lines from the ZX_LINES*/
8476: --Call zx_lines_table_handler for deleting the non-applicable regimes
8477:
8478: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
8479: p_event => 'DELETE',
8472: END IF;
8473: END IF; --record_for_partners_flag
8474:
8475: /*Delete Provider Tax Lines from the ZX_LINES*/
8476: --Call zx_lines_table_handler for deleting the non-applicable regimes
8477:
8478: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
8479: p_event => 'DELETE',
8480: p_tax_regime_code => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_regime_code,
8474:
8475: /*Delete Provider Tax Lines from the ZX_LINES*/
8476: --Call zx_lines_table_handler for deleting the non-applicable regimes
8477:
8478: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
8479: p_event => 'DELETE',
8480: p_tax_regime_code => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_regime_code,
8481: p_provider_id => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_provider_id,
8482: x_return_status => l_return_status
8666: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
8667: END IF;
8668: x_return_status := FND_API.G_RET_STS_SUCCESS;
8669:
8670: delete from zx_lines_det_factors
8671: WHERE application_id = p_event_class_rec.application_id
8672: AND entity_code = p_event_class_rec.entity_code
8673: AND event_class_code = p_event_class_rec.event_class_code
8674: AND trx_id = p_event_class_rec.trx_id;
8812: *-----------------------------------------------------------------------*/
8813:
8814: /*------------------------------------------------------------------------*
8815: | Bug 3955422 - Remove the logic below since for LTE , decision is to |
8816: | retreive data from zx_lines_det_factors always |
8817: *-----------------------------------------------------------------------*/
8818: /*IF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'EBTAX' THEN
8819: l_cursor_rows := C_LINES_PER_FETCH ;
8820: ELSIF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'LTE' THEN
8881: END IF;
8882: RETURN;
8883: END IF;
8884:
8885: -- SDSU Bug 6605681, When AP is calling invoice validation in bulk process, it errors out with U1 violation in zx_lines_det_factors.
8886: -- The invoice validation flow is then changed to processing individual invoices. At this point AP receives cursor already open error. To avoid this, adding this code to close and open the cursor for each call.
8887: -- This is a temporary fix and tested. We need to identify where exactly they are receiving the cursor error and identify if cursor should be open or close. This fix will now go only in a one-off checkin, includable by AP
8888: -- Start Bug 6605681
8889: IF c_lines%ISOPEN THEN CLOSE c_lines;
9833: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
9834: BEGIN
9835: SELECT count(*)
9836: INTO l_count
9837: FROM ZX_LINES_DET_FACTORS
9838: WHERE application_id = p_event_class_rec.application_id
9839: AND entity_code = p_event_class_rec.entity_code
9840: AND event_class_code = p_event_class_rec.event_class_code
9841: AND trx_id = p_event_class_rec.trx_id
10205: END IF;
10206:
10207: /*Merge ruled out by performance due to record of table structure*/
10208: --Do not perform updates in case of lines called from Determining Factors window since
10209: --line is already updated in zx_lines_det_factors due to call to update table handler
10210: -- by products
10211:
10212: --Tax calculation has been successfully performed on this document
10213: p_event_class_rec.TAX_CALCULATION_DONE_FLAG := 'Y';
10289: END IF;
10290: RETURN;
10291: END IF;
10292: --Do not perform inserts in case of lines called from Determining Factors window since
10293: --line is already existing in zx_lines_det_factors
10294: ELSIF ZX_API_PUB.G_DATA_TRANSFER_MODE = 'WIN' THEN
10295: db_update_line_det_factors (p_trx_line_dist_tbl => ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl,
10296: p_event_class_rec => p_event_class_rec,
10297: p_update_index => ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id.LAST,
10418: IF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'LTE' THEN
10419:
10420: -- bug#5665057-
10421: IF ZX_API_PUB.G_DATA_TRANSFER_MODE = 'WIN' THEN
10422: update zx_lines_det_factors
10423: set event_id = p_event_class_rec.event_id
10424: where application_id = p_event_class_rec.application_id
10425: AND entity_code = p_event_class_rec.entity_code
10426: AND event_class_code = p_event_class_rec.event_class_code
12688: /*------------------------------------------------------------------------+
12689: | Override Summary Tax Lines |
12690: + ------------------------------------------------------------------------*/
12691:
12692: --Pass the tax event type code derived into event class rec instead of that fetched from zx_lines_det_factors
12693: zx_global_structures_pkg.trx_line_dist_tbl.TAX_EVENT_TYPE_CODE(l_trx_line_index):=p_event_class_rec.tax_event_type_code;
12694:
12695: ZX_TDS_CALC_SERVICES_PUB_PKG.override_summary_tax_lines(l_trx_line_index
12696: ,p_event_class_rec
13057: /* -----------------------------------------------------------------+
13058: | Call TDS routine to override the detail tax lines |
13059: | |
13060: + -----------------------------------------------------------------*/
13061: --Pass the tax event type code derived into event class rec instead of that fetched from zx_lines_det_factors
13062: zx_global_structures_pkg.trx_line_dist_tbl.TAX_EVENT_TYPE_CODE(l_trx_line_index):=p_event_class_rec.tax_event_type_code;
13063:
13064: ZX_TDS_CALC_SERVICES_PUB_PKG.override_detail_tax_lines(l_trx_line_index
13065: ,p_event_class_rec
13180: p_event_class_rec.record_for_partners_flag = 'Y' THEN
13181:
13182: /*Commented due to GSCC error - need to provide all column names for insert
13183: INSERT INTO ZX_DETAIL_TAX_LINES_GT
13184: SELECT * from ZX_LINES
13185: WHERE application_id = p_event_class_rec.application_id
13186: AND entity_code = p_event_class_rec.entity_code
13187: AND event_class_code = p_event_class_rec.event_class_code
13188: AND trx_id = p_event_class_rec.trx_id
13298: END IF;--tax_provider_id is not null
13299: END LOOP;
13300: END IF; --recording flag
13301:
13302: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
13303: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
13304: p_event => 'UPDATE',
13305: p_tax_regime_code => null,
13306: p_provider_id => null,
13299: END LOOP;
13300: END IF; --recording flag
13301:
13302: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
13303: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
13304: p_event => 'UPDATE',
13305: p_tax_regime_code => null,
13306: p_provider_id => null,
13307: x_return_status => l_return_status
13320: for tax lines synchronization view needed for partner service.
13321: But, eBTax service ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines
13322: creates tax lines for the records remaining in the
13323: ZX_DETAIL_TAX_LINES_GT. This results in tax line not getting
13324: deleted from zx_lines when partner created tax line is deleted.
13325: Hence, following delete is being issued. */
13326:
13327: BEGIN
13328: DELETE FROM ZX_DETAIL_TAX_LINES_GT
13533: -- PUBLIC PROCEDURE
13534: -- reverse_document
13535: --
13536: -- DESCRIPTION
13537: -- Negate the line amount and line quantity and store into zx_lines_det_factors
13538: --
13539: -- CALLED BY
13540: -- ZX_API_PUB.reverse_document
13541: -----------------------------------------------------------------------
13851: zl.START_EXPENSE_DATE ,
13852: zl.TRX_BATCH_ID ,
13853: zl.APPLIED_TO_TRX_NUMBER ,
13854: zl.PROVNL_TAX_DETERMINATION_DATE
13855: FROM ZX_LINES_DET_FACTORS zl,
13856: ZX_REVERSE_TRX_LINES_GT zrtlgt,
13857: ZX_REV_TRX_HEADERS_GT zrthgt
13858: WHERE zl.trx_id =zrtlgt.reversed_trx_id
13859: AND zl.application_id = zrtlgt.reversed_appln_id
14195: zx_global_structures_pkg.trx_line_dist_tbl.PROVNL_TAX_DETERMINATION_DATE;
14196: CLOSE get_rev_info_csr;
14197:
14198: /* -------------------------------------------------------+
14199: | Insert into zx_lines_det_factors |
14200: + -------------------------------------------------------*/
14201: db_insert_line_det_factors (p_trx_line_dist_tbl => zx_global_structures_pkg.trx_line_dist_tbl,
14202: p_event_class_rec => p_event_class_rec,
14203: p_insert_index => zx_global_structures_pkg.trx_line_dist_tbl.APPLICATION_ID.FIRST,
14765: END IF;
14766: END LOOP;
14767: END IF;
14768:
14769: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
14770: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
14771: p_event => 'UPDATE',
14772: p_tax_regime_code => null,
14773: p_provider_id => null,
14766: END LOOP;
14767: END IF;
14768:
14769: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
14770: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
14771: p_event => 'UPDATE',
14772: p_tax_regime_code => null,
14773: p_provider_id => null,
14774: x_return_status => l_return_status
14833: IF p_event_class_rec.record_flag ='Y' AND
14834: p_event_class_rec.record_for_partners_flag = 'Y' THEN
14835: SELECT zxdet.partner_migrated_flag
14836: INTO l_partner_migrated_flag
14837: FROM zx_lines_det_factors zxdet
14838: WHERE zxdet.application_id = p_event_class_rec.application_id
14839: AND zxdet.entity_code = p_event_class_rec.entity_code
14840: AND zxdet.event_class_code = p_event_class_rec.event_class_code
14841: AND zxdet.trx_id = p_event_class_rec.trx_id
15010: -- PUBLIC PROCEDURE
15011: -- insupd_line_det_factors
15012: --
15013: -- DESCRIPTION
15014: -- Called to insert/update to zx_lines_det_factors for products integrating
15015: -- with the Line det factors UI
15016: --
15017: -- CALLED BY
15018: -- ZX_API_PUB.insert_line_det_factors
15208: ELSIF ZX_API_PUB.G_PUB_SRVC = 'UPDATE_LINE_DET_FACTORS' THEN
15209:
15210: SELECT tax_calculation_done_flag
15211: INTO p_event_class_rec.tax_calculation_done_flag
15212: FROM ZX_LINES_DET_FACTORS
15213: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
15214: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
15215: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
15216: AND TRX_ID = p_event_class_rec.TRX_ID
15250: END insupd_line_det_factors;
15251:
15252: -----------------------------------------------------------------------
15253: -- PUBLIC PROCEDURE
15254: -- zx_lines_table_handler
15255: --
15256: -- DESCRIPTION
15257: -- Handles inserts/updates/deletes to zx_lines for partner integration
15258: --
15253: -- PUBLIC PROCEDURE
15254: -- zx_lines_table_handler
15255: --
15256: -- DESCRIPTION
15257: -- Handles inserts/updates/deletes to zx_lines for partner integration
15258: --
15259: -- CALLED BY
15260: -- ZX_API_PUB.ptnr_sync_calc_tax
15261: -- override_tax
15259: -- CALLED BY
15260: -- ZX_API_PUB.ptnr_sync_calc_tax
15261: -- override_tax
15262: ----------------------------------------------------------------------
15263: PROCEDURE zx_lines_table_handler
15264: (
15265: p_event_class_rec IN ZX_API_PUB.event_class_rec_type ,
15266: p_event IN VARCHAR2,
15267: p_tax_regime_code IN VARCHAR2,
15268: p_provider_id IN NUMBER,
15269: x_return_status OUT NOCOPY VARCHAR2
15270: )IS
15271:
15272: l_api_name CONSTANT VARCHAR2(30):= 'ZX_LINES_TABLE_HANDLER';
15273: l_context_info_rec ZX_API_PUB.context_info_rec_type;
15274: l_return_status VARCHAR2(1);
15275:
15276: BEGIN
15280:
15281: x_return_status := FND_API.G_RET_STS_SUCCESS ;
15282:
15283: IF p_event ='UPDATE' THEN
15284: UPDATE ZX_LINES
15285: SET sync_with_prvdr_flag ='N'
15286: WHERE application_id = p_event_class_rec.application_id
15287: AND entity_code = p_event_class_rec.entity_code
15288: AND event_class_code = p_event_class_rec.event_class_code
15288: AND event_class_code = p_event_class_rec.event_class_code
15289: AND trx_id = p_event_class_rec.trx_id
15290: AND tax_provider_id is not null;
15291: ELSIF p_event = 'DELETE' THEN
15292: DELETE from zx_lines
15293: WHERE application_id = p_event_class_rec.application_id
15294: AND entity_code = p_event_class_rec.entity_code
15295: AND event_class_code = p_event_class_rec.event_class_code
15296: AND trx_id = p_event_class_rec.trx_id
15307: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
15308: IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
15309: FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
15310: END IF;
15311: END zx_lines_table_handler;
15312:
15313: -----------------------------------------------------------------------
15314: -- PUBLIC PROCEDURE
15315: -- Get_Tax_Profile_Ids
15462: l_event_class_rec.prod_family_grp_code := p_event_class_rec.prod_family_grp_code;
15463: l_event_class_rec.tax_method_code := p_event_class_rec.tax_method_code;
15464:
15465: /*If the adjusted document information is passed with the transaction line, then derive the values
15466: tax determining factors from ZX_LINES_DET_FACTORS for the adjusted document*/
15467: IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_APPLICATION_ID(p_trx_line_index) is not null THEN
15468: BEGIN
15469: SELECT default_taxation_country,
15470: document_sub_type,
15486: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15487: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15488: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15489: l_tax_classification_code
15490: FROM ZX_LINES_DET_FACTORS
15491: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
15492: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
15493: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
15494: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
15531: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15532: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15533: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15534: l_tax_classification_code
15535: FROM ZX_LINES_DET_FACTORS
15536: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
15537: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
15538: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
15539: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
15541: AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index);
15542: END;
15543: --upgrade of adjusted doc complete
15544: /*If the applied from document information is passed with the transaction line, then derive the values
15545: tax determining factors from ZX_LINES_DET_FACTORS for the applied from document*/
15546: ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index) is not null THEN
15547: BEGIN
15548: SELECT default_taxation_country,
15549: document_sub_type,
15565: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15566: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15567: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15568: l_tax_classification_code
15569: FROM ZX_LINES_DET_FACTORS
15570: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
15571: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
15572: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
15573: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
15610: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15611: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15612: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15613: l_tax_classification_code
15614: FROM ZX_LINES_DET_FACTORS
15615: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
15616: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
15617: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
15618: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
15620: AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
15621: END;
15622: --upgrade of applied from doc complete
15623: /*If the source document information is passed with the transaction line, then derive the values
15624: tax determining factors from ZX_LINES_DET_FACTORS for the source document*/
15625: ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_TRX_ID(p_trx_line_index) is not null THEN
15626: BEGIN
15627: --To support intercompany transactions, fetch the intercompany information
15628: --from zx_evnt_cls_mappings
15633: AP's event classes don't match one-to-one with AR. This has resulted
15634: into an issue when Credit memo is imported during Intercompany xfer.
15635: We are not able to determine the correct intrcmp_src_evnt_cls_code ('CREDIT_MEMO') from the query below as AP has only one event class 'STANDARD INVOICES'
15636: with corresponding intrcmp_src_evnt_cls_code 'INVOICE'.
15637: Since, AR contains invoices, credit memos, debit memos in one entity, assumption here is trx_id will be unique. Hence, omitting event_class_code criteria from query on zx_lines_det_factors.
15638: */
15639:
15640: SELECT intrcmp_src_appln_id,
15641: intrcmp_src_entity_code,
15669: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15670: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15671: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15672: l_tax_classification_code
15673: FROM ZX_LINES_DET_FACTORS
15674: WHERE application_id = nvl(l_intrcmp_src_appln_id,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(p_trx_line_index))
15675: AND entity_code = nvl(l_intrcmp_src_entity_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(p_trx_line_index))
15676: -- Bug 5666175 AND event_class_code = nvl(l_intrcmp_src_event_class_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(p_trx_line_index))
15677: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
15720: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
15721: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
15722: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
15723: l_tax_classification_code
15724: FROM ZX_LINES_DET_FACTORS
15725: WHERE application_id = nvl(l_intrcmp_src_appln_id,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(p_trx_line_index))
15726: AND entity_code = nvl(l_intrcmp_src_entity_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(p_trx_line_index))
15727: AND event_class_code = nvl(l_intrcmp_src_event_class_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(p_trx_line_index))
15728: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
15742: SELECT default_taxation_country ,
15743: document_sub_type
15744: INTO l_default_taxation_country,
15745: l_doc_sub_type
15746: FROM ZX_LINES_DET_FACTORS
15747: WHERE application_id = p_event_class_rec.application_id
15748: AND entity_code = p_event_class_rec.entity_code
15749: AND event_class_code = p_event_class_rec.event_class_code
15750: AND trx_id = p_event_class_rec.trx_id
15812: -- For records in trx_line_dist_tbl
15813: -- Loop
15814: -- If all of defaulting attributes null (not including tax classification) then
15815: -- If line_level_action is UPDATE
15816: -- Fetch all attributes from zx_lines_det_factors
15817: -- WHEN NO_DATA_FOUND
15818: -- IF event_class_code in ('PO_PA','RELEASE')
15819: -- call on-the-fly upgrade
15820: -- ELSIF user_upd_det_factors_flag = 'N'
15823: -- if item/item_org_id/country has changed
15824: -- call RE-defaulting API for intended_use, product_fiscal_classification, product type
15825: -- if item/item_org_id/assessable value has changed
15826: -- call redefaulting API for assessable value
15827: -- if nothing changed copy from determining attrs from zx_lines_det_factors
15828: -- Always call the tax classification defaulting API
15829: -- ELSIF line level action is CREATE
15830: -- IF historical_tax_code_id passed
15831: -- redefault the tax attributes for PO (on the fly migration)
16805: -- PUBLIC PROCEDURE
16806: -- call_redefaulting_APIs
16807: --
16808: -- DESCRIPTION
16809: -- 1) Fetch and populate all determining attributes from zx_lines_det_factors
16810: -- 2) If product id or product org id changes call the redefaulting APIs for product
16811: -- fiscal classification and intended use
16812: -- 3) If product id, product org id, line amount changes, call the redefaulting APIs
16813: -- for assessable value
16852: --Bug#7045194: validate input tax classification code
16853:
16854: CURSOR validate_input_tcc_csr
16855: (c_input_tcc ZX_RATES_B.tax_rate_code%TYPE,
16856: c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
16857: c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
16858: IS
16859: SELECT COUNT(1)
16860: FROM ZX_INPUT_CLASSIFICATIONS_V
16853:
16854: CURSOR validate_input_tcc_csr
16855: (c_input_tcc ZX_RATES_B.tax_rate_code%TYPE,
16856: c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
16857: c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
16858: IS
16859: SELECT COUNT(1)
16860: FROM ZX_INPUT_CLASSIFICATIONS_V
16861: WHERE lookup_code = c_input_tcc
16868: --Bug#7045194: validate output tax classification code
16869:
16870: CURSOR validate_output_tcc_csr
16871: (c_output_tcc ZX_RATES_B.tax_rate_code%TYPE,
16872: c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
16873: c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
16874: IS
16875: SELECT COUNT(1)
16876: FROM ZX_OUTPUT_CLASSIFICATIONS_V
16869:
16870: CURSOR validate_output_tcc_csr
16871: (c_output_tcc ZX_RATES_B.tax_rate_code%TYPE,
16872: c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
16873: c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
16874: IS
16875: SELECT COUNT(1)
16876: FROM ZX_OUTPUT_CLASSIFICATIONS_V
16877: WHERE lookup_code = c_output_tcc
16926: l_line_amt,
16927: l_input_tax_class_code,
16928: l_output_tax_class_code,
16929: l_trx_line_id
16930: FROM ZX_LINES_DET_FACTORS
16931: WHERE application_id = p_event_class_rec.application_id
16932: AND entity_code = p_event_class_rec.entity_code
16933: AND event_class_code = p_event_class_rec.event_class_code
16934: AND trx_id = p_event_class_rec.trx_id