213: --l_default_taxation_country VARCHAR2(2);
214: --l_doc_sub_type VARCHAR2(240);
215:
216: --Bug 8541137
217: --l_tax_invoice_date ZX_LINES_DET_FACTORS.TAX_INVOICE_DATE%TYPE;
218:
219: --Bug 8859101 ends
220:
221: /* Bug 5013598 */
594: 'SYNCHRONIZE','U',
595: 'DELETE','U') ,
596: null)INSERT_UPDATE_FLAG
597: /* The update insert flag is to determine the records that need to be inserted/updated
598: into zx_lines_det_factors depending on the line_level_action for tax event type UPDATE*/
599: FROM ZX_TRANSACTION_LINES_GT trxlines,
600: ZX_TRX_HEADERS_GT header
601: WHERE header.application_id = p_event_class_rec.application_id
602: AND header.entity_code = p_event_class_rec.entity_code
973: 'DELETE','U') INSERT_UPDATE_FLAG,
974: null TAX_PROCESSING_COMPLETED_FLAG, -- Bug 8931030
975: null TAX_CALCULATION_DONE_FLAG -- Bug 8931030
976: /* The update insert flag is to determine the records that need to be inserted/updated
977: into zx_lines_det_factors depending on the line_level_action for tax event type UPDATE*/
978: FROM ZX_TRANSACTION_LINES_GT trxlines,
979: ZX_TRX_HEADERS_GT header
980: WHERE trxlines.application_id = header.application_id
981: AND trxlines.entity_code = header.entity_code
1278: GLOBAL_ATTRIBUTE_CATEGORY,
1279: GLOBAL_ATTRIBUTE1,
1280: TOTAL_INC_TAX_AMT,
1281: USER_UPD_DET_FACTORS_FLAG
1282: FROM ZX_LINES_DET_FACTORS
1283: WHERE event_id = p_event_id;
1284:
1285: /* Cursor for windows (Determining Factors) version of calculate tax*/
1286: CURSOR get_lines_det_factors(p_event_class_rec zx_api_pub.event_class_rec_type) IS
1578: GLOBAL_ATTRIBUTE_CATEGORY,
1579: GLOBAL_ATTRIBUTE1,
1580: TOTAL_INC_TAX_AMT,
1581: USER_UPD_DET_FACTORS_FLAG
1582: FROM ZX_LINES_DET_FACTORS
1583: WHERE application_id = p_event_class_rec.application_id
1584: AND entity_code = p_event_class_rec.entity_code
1585: AND event_class_code = p_event_class_rec.event_class_code
1586: AND trx_id = p_event_class_rec.trx_id
1592: zl.tax_regime_code,
1593: zl.tax_provider_id,
1594: regimes.effective_from,
1595: regimes.effective_to
1596: FROM ZX_LINES zl,
1597: ZX_REGIMES_B regimes
1598: WHERE zl.application_id = p_event_class_rec.application_id
1599: AND zl.entity_code = p_event_class_rec.entity_code
1600: AND zl.event_class_code = p_event_class_rec.event_class_code
1628: zxlines.tax_provider_id,
1629: zxlines.trx_line_id,
1630: regimes.effective_from,
1631: regimes.effective_to
1632: FROM ZX_LINES zxlines,
1633: ZX_REGIMES_B regimes
1634: WHERE zxlines.application_id = p_event_class_rec.application_id
1635: AND zxlines.entity_code = p_event_class_rec.entity_code
1636: AND zxlines.event_class_code = p_event_class_rec.event_class_code
1713: -- PRIVATE PROCEDURE
1714: -- db_insert_line_det_factors
1715: --
1716: -- DESCRIPTION
1717: -- Bulk insert into zx_lines_det_factors
1718: --
1719: -- CALLED BY
1720: -- calculate_tax
1721: -- import_document_with_tax
1723: -- insupd_line_det_factors
1724: -----------------------------------------------------------------------
1725: /*----------------------------------------------------------------------------*
1726: |p_insert needed in case of UPDATE tax event type wherein some records passed|
1727: |need to be updated in zx_lines_det_factors while others need to be inserted |
1728: |depending on the line level action. This scenario is applicable only for |
1729: |Calculate_tax API. p_insert stores the index upto which the records from the|
1730: |collection needs to be inserted into zx_lines_det_factors. |
1731: *----------------------------------------------------------------------------*/
1726: |p_insert needed in case of UPDATE tax event type wherein some records passed|
1727: |need to be updated in zx_lines_det_factors while others need to be inserted |
1728: |depending on the line level action. This scenario is applicable only for |
1729: |Calculate_tax API. p_insert stores the index upto which the records from the|
1730: |collection needs to be inserted into zx_lines_det_factors. |
1731: *----------------------------------------------------------------------------*/
1732: PROCEDURE db_insert_line_det_factors
1733: (p_trx_line_dist_tbl IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE,
1734: p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1757: -- Set the return status to Success
1758: x_return_status := FND_API.G_RET_STS_SUCCESS;
1759: l_stmt_num := 0;
1760:
1761: -- Insert records in ZX_LINES_DET_FACTORS
1762: --Start Bug 8269616
1763: IF ZX_API_PUB.G_PUB_SRVC IN ('INSERT_LINE_DET_FACTORS','COPY_INSERT_LINE_DET_FACTORS',
1764: 'REVERSE_DOCUMENT_DISTRIBUTION')
1765: --End Bug 8269616
1765: --End Bug 8269616
1766: THEN
1767: l_stmt_num := 1;
1768: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id SAVE EXCEPTIONS
1769: INSERT INTO ZX_LINES_DET_FACTORS(
1770: EVENT_ID,
1771: INTERNAL_ORGANIZATION_ID,
1772: APPLICATION_ID,
1773: ENTITY_CODE,
2362: AND err_gt.trx_id = p_trx_line_dist_tbl.trx_id(i));
2363: ELSE
2364: l_stmt_num := 2;
2365: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id SAVE EXCEPTIONS
2366: INSERT INTO ZX_LINES_DET_FACTORS(
2367: EVENT_ID,
2368: INTERNAL_ORGANIZATION_ID,
2369: APPLICATION_ID,
2370: ENTITY_CODE,
2984: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2985: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2986: FND_LOG.STRING(G_LEVEL_STATEMENT,
2987: 'ZX.PLSQL.ZX_SRVC_TYP_PKG.db_insert_line_det_factors',
2988: TO_CHAR(l_err_count)||' Error(s) occured while inserting record into ZX_LINES_DET_FACTORS.');
2989: END IF;
2990:
2991: l_err_trx_id := -99;
2992: FOR i IN 1 .. l_err_count LOOP
3039: -- PRIVATE PROCEDURE
3040: -- db_update_line_det_factors
3041: --
3042: -- DESCRIPTION
3043: -- Bulk update into zx_lines_det_factors
3044: --
3045: -- CALLED BY
3046: -- calculate_tax
3047: -- insupd_line_det_factors
3047: -- insupd_line_det_factors
3048: -----------------------------------------------------------------------
3049: /*----------------------------------------------------------------------------*
3050: |p_update needed in case of UPDATE tax event type wherein some records passed|
3051: |need to be updated in zx_lines_det_factors while others need to be inserted |
3052: |depending on the line level action. This scenario is applicable only for |
3053: |Calculate_tax API. p_update stores the index from which the lines from |
3054: |the collection needs to be updated to zx_lines_det_factors |
3055: *----------------------------------------------------------------------------*/
3050: |p_update needed in case of UPDATE tax event type wherein some records passed|
3051: |need to be updated in zx_lines_det_factors while others need to be inserted |
3052: |depending on the line level action. This scenario is applicable only for |
3053: |Calculate_tax API. p_update stores the index from which the lines from |
3054: |the collection needs to be updated to zx_lines_det_factors |
3055: *----------------------------------------------------------------------------*/
3056:
3057: PROCEDURE db_update_line_det_factors
3058: (p_trx_line_dist_tbl IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE,
3109: --FORALL i IN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) .. NVL(p_update_index,-99)
3110: -- Bug 8873180
3111: l_stmt_num := 1;
3112: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id BETWEEN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) AND NVL(p_update_index,-99)
3113: UPDATE ZX_LINES_DET_FACTORS SET
3114: EVENT_ID = p_event_class_rec.event_id,
3115: INTERNAL_ORGANIZATION_ID = p_trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i),
3116: EVENT_TYPE_CODE = p_trx_line_dist_tbl.EVENT_TYPE_CODE(i),
3117: DOC_EVENT_STATUS = p_event_class_rec.DOC_STATUS_CODE,
3412: --FORALL i IN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) .. NVL(p_update_index,-99)
3413: -- Bug 8873180
3414: l_stmt_num := 2;
3415: FORALL i IN INDICES OF p_trx_line_dist_tbl.application_id BETWEEN NVL(p_trx_line_dist_tbl.application_id.FIRST,0) AND NVL(p_update_index,-99)
3416: UPDATE ZX_LINES_DET_FACTORS SET
3417: EVENT_ID = p_event_class_rec.event_id,
3418: INTERNAL_ORGANIZATION_ID = p_trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i),
3419: EVENT_TYPE_CODE = p_trx_line_dist_tbl.EVENT_TYPE_CODE(i),
3420: DOC_EVENT_STATUS = p_event_class_rec.DOC_STATUS_CODE,
3752: -----------------------------------------------------------------------
3753:
3754: PROCEDURE insert_detail_tax_lines_gt
3755: (p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
3756: p_sync_with_prvdr_flag IN ZX_LINES.sync_with_prvdr_flag%type,
3757: p_ptnr_tax_lines_insert OUT NOCOPY NUMBER, -- Bug 5332192
3758: x_return_status OUT NOCOPY VARCHAR2
3759: )IS
3760: l_api_name CONSTANT VARCHAR2(30):= 'INSERT_DETAIL_TAX_LINES_GT';
4361: L.interface_tax_line_id,
4362: L.taxing_juris_geography_id,
4363: L.adjusted_doc_tax_line_id,
4364: 1
4365: FROM ZX_LINES L
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
4393: -- PRIVATE PROCEDURE
4394: -- call_lte
4395: --
4396: -- DESCRIPTION
4397: -- Wrapper to fetch all lines from zx_lines_det_factors before calling LTE
4398: -- to calculate_tax
4399: --
4400: -- CALLED BY
4401: -- calculate_tax
4707: GLOBAL_ATTRIBUTE_CATEGORY,
4708: GLOBAL_ATTRIBUTE1,
4709: TOTAL_INC_TAX_AMT,
4710: USER_UPD_DET_FACTORS_FLAG
4711: FROM ZX_LINES_DET_FACTORS
4712: WHERE application_id = p_event_class_rec.application_id
4713: AND entity_code = p_event_class_rec.entity_code
4714: AND event_class_code = p_event_class_rec.event_class_code
4715: AND trx_id = p_event_class_rec.trx_id
5083: x_return_status OUT NOCOPY VARCHAR2
5084: ) IS
5085: l_api_name CONSTANT VARCHAR2(30):= 'LOCK_LINE_DET_FACTS';
5086: l_return_status VARCHAR2(30);
5087: l_lines_det_rec zx_lines_det_factors%rowtype;
5088: l_context_info_rec ZX_API_PUB.context_info_rec_type;
5089:
5090: BEGIN
5091: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5097: IF p_event_class_rec.TAX_EVENT_TYPE_CODE = 'UPDATE' THEN
5098: /*Lock trx line det factors*/
5099: SELECT *
5100: INTO l_lines_det_rec
5101: FROM ZX_LINES_DET_FACTORS
5102: WHERE application_id = p_event_class_rec.application_id
5103: AND entity_code = p_event_class_rec.entity_code
5104: AND event_class_code = p_event_class_rec.event_class_code
5105: AND trx_id = p_event_class_rec.trx_id
5852: -- populate_parties
5853: --
5854: -- DESCRIPTION
5855: -- Retrieve the following from hz_parties given the party_tax_profile ids
5856: -- from zx_lines_det_factors and zx_party_tax_profile
5857: -- SHIP_TO_PARTY_NUMBER
5858: -- SHIP_TO_PARTY_NAME
5859: -- SHIP_FROM_PARTY_NUMBER
5860: -- SHIP_FROM_PARTY_NAME
5880: l_ship_to_ptp_id NUMBER;
5881: l_ship_from_ptp_id NUMBER;
5882: l_bill_to_ptp_id NUMBER;
5883: l_bill_from_ptp_id NUMBER;
5884: l_ship_third_pty_acct_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_ID%type;
5885: l_bill_third_pty_acct_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%type;
5886: l_party_index VARCHAR2(100);
5887: BEGIN
5888: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5881: l_ship_from_ptp_id NUMBER;
5882: l_bill_to_ptp_id NUMBER;
5883: l_bill_from_ptp_id NUMBER;
5884: l_ship_third_pty_acct_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_ID%type;
5885: l_bill_third_pty_acct_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%type;
5886: l_party_index VARCHAR2(100);
5887: BEGIN
5888: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5889: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
5890: END IF;
5891:
5892: x_return_status := FND_API.G_RET_STS_SUCCESS;
5893:
5894: --Get the ptp ids from zx_lines_det_factors
5895: SELECT ship_to_party_tax_prof_id,
5896: bill_to_party_tax_prof_id,
5897: ship_from_party_tax_prof_id,
5898: bill_from_party_tax_prof_id,
5903: l_ship_from_ptp_id,
5904: l_bill_from_ptp_id,
5905: l_ship_third_pty_acct_id, -- Bug 4939819
5906: l_bill_third_pty_acct_id -- Bug 4939819
5907: FROM ZX_LINES_DET_FACTORS
5908: WHERE event_class_mapping_id = p_event_class_rec.event_class_mapping_id -- Bug 4939819
5909: AND trx_id = p_trx_id
5910: AND trx_line_id = p_trx_line_id
5911: AND trx_level_type = p_trx_level_type;
7395: tax.tax_regime_id,
7396: tax.tax_provider_id,
7397: regimes.effective_from,
7398: regimes.effective_to
7399: FROM ZX_LINES_DET_FACTORS lines,
7400: ZX_LINES tax,
7401: ZX_REGIMES_B regimes
7402: WHERE lines.application_id = p_event_class_rec.application_id
7403: AND lines.entity_code = p_event_class_rec.entity_code
7396: tax.tax_provider_id,
7397: regimes.effective_from,
7398: regimes.effective_to
7399: FROM ZX_LINES_DET_FACTORS lines,
7400: ZX_LINES tax,
7401: ZX_REGIMES_B regimes
7402: WHERE lines.application_id = p_event_class_rec.application_id
7403: AND lines.entity_code = p_event_class_rec.entity_code
7404: AND lines.event_class_code = p_event_class_rec.event_class_code
7521: l_tax_provider_id NUMBER;
7522: l_context_info_rec ZX_API_PUB.context_info_rec_type;
7523: l_incl_override VARCHAR2(1);
7524:
7525: --Fetch the transaction lines from ZX_LINES_DET_FACTORS for which inclusive tax line, calculated by Oracle E-Business Tax,
7526: --has been overridden and partner has been involved in the calculation of taxes of at least one regime and the transaction line
7527: --amount includes the inclusive tax amount.The transaction line containing inclusive taxes can be identified by the flag
7528: --INCLUSIVE_TAX_OVERRIDE_FLAG = 'Y' in ZX_LINES_DET_FACTORS.
7529:
7524:
7525: --Fetch the transaction lines from ZX_LINES_DET_FACTORS for which inclusive tax line, calculated by Oracle E-Business Tax,
7526: --has been overridden and partner has been involved in the calculation of taxes of at least one regime and the transaction line
7527: --amount includes the inclusive tax amount.The transaction line containing inclusive taxes can be identified by the flag
7528: --INCLUSIVE_TAX_OVERRIDE_FLAG = 'Y' in ZX_LINES_DET_FACTORS.
7529:
7530: CURSOR ptnr_incl_lines_csr(p_event_class_rec ZX_API_PUB.event_class_rec_type) IS
7531: SELECT distinct
7532: tax.tax_regime_code,
7533: tax.tax_regime_id,
7534: tax.tax_provider_id,
7535: regimes.effective_from,
7536: regimes.effective_to
7537: FROM ZX_LINES_DET_FACTORS lines,
7538: ZX_LINES tax,
7539: ZX_REGIMES_B regimes
7540: WHERE lines.application_id = p_event_class_rec.application_id
7541: AND lines.entity_code = p_event_class_rec.entity_code
7534: tax.tax_provider_id,
7535: regimes.effective_from,
7536: regimes.effective_to
7537: FROM ZX_LINES_DET_FACTORS lines,
7538: ZX_LINES tax,
7539: ZX_REGIMES_B regimes
7540: WHERE lines.application_id = p_event_class_rec.application_id
7541: AND lines.entity_code = p_event_class_rec.entity_code
7542: AND lines.event_class_code = p_event_class_rec.event_class_code
7969: | We need to skip the logic to get ptps if they already exist in trx_line_dist_tbl.|
7970: | This is the case when import is called after call to validate_and_default routine|
7971: | which derives and defaults the values back to GT and also in the case when |
7972: | calculate tax is called by products uptaking the determining factors UI where the|
7973: | transaction line information is alredy present in zx_lines_det_factors and |
7974: | fetched into trx_line_dist_tbl |
7975: +---------------------------------------------------------------------------------*/
7976: /* Skip if the ship_to_party_tax_prof_id is already populated*/
7977: IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_to_party_tax_prof_id(p_trx_line_index) is NULL THEN
9104: SELECT record_type_code,
9105: partner_migrated_flag
9106: INTO l_record_type,
9107: l_partner_migrated_flag
9108: FROM ZX_LINES_DET_FACTORS
9109: WHERE application_id = p_event_class_rec.application_id
9110: AND entity_code = p_event_class_rec.entity_code
9111: AND event_class_code = p_event_class_rec.event_class_code
9112: AND trx_id = p_event_class_rec.trx_id
9113: AND rownum = 1;
9114: EXCEPTION WHEN OTHERS THEN --Bug fix 5094078
9115: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
9116: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
9117: ' No row found in zx_lines_det_factors for UPDATE event type' );
9118: END IF;
9119: END;
9120:
9121: IF l_partner_migrated_flag = 'Y' THEN
9209: END IF;
9210: BEGIN
9211: SELECT 'Y'
9212: INTO l_deleted_line_exists
9213: FROM ZX_LINES_DET_FACTORS
9214: WHERE application_id = p_event_class_rec.application_id
9215: AND entity_code = p_event_class_rec.entity_code
9216: AND event_class_code = p_event_class_rec.event_class_code
9217: AND trx_id = p_event_class_rec.trx_id
9318: END IF;
9319:
9320:
9321: /*Check if partner_processing_flag = 'N', then
9322: Delete Provider Tax Lines from the ZX_LINES and references to the provider in the ZX_TRX_LINE_APP_REGIMES for that regime.
9323: If partner recording is allowed for this event class, then
9324: Call partner's service Modify Doc Status with action DELETE*/
9325:
9326: IF ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(l_regime_index) AND
9343: RETURN;
9344: END IF;
9345: END IF; --record_for_partners_flag
9346:
9347: /*Delete Provider Tax Lines from the ZX_LINES*/
9348: --Call zx_lines_table_handler for deleting the non-applicable regimes
9349:
9350: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
9351: p_event => 'DELETE',
9344: END IF;
9345: END IF; --record_for_partners_flag
9346:
9347: /*Delete Provider Tax Lines from the ZX_LINES*/
9348: --Call zx_lines_table_handler for deleting the non-applicable regimes
9349:
9350: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
9351: p_event => 'DELETE',
9352: p_tax_regime_code => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_regime_code,
9346:
9347: /*Delete Provider Tax Lines from the ZX_LINES*/
9348: --Call zx_lines_table_handler for deleting the non-applicable regimes
9349:
9350: zx_lines_table_handler (p_event_class_rec => p_event_class_rec,
9351: p_event => 'DELETE',
9352: p_tax_regime_code => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_regime_code,
9353: p_provider_id => ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_provider_id,
9354: x_return_status => l_return_status
9563: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
9564: END IF;
9565: x_return_status := FND_API.G_RET_STS_SUCCESS;
9566:
9567: delete from zx_lines_det_factors
9568: WHERE application_id = p_event_class_rec.application_id
9569: AND entity_code = p_event_class_rec.entity_code
9570: AND event_class_code = p_event_class_rec.event_class_code
9571: AND trx_id = p_event_class_rec.trx_id;
9722: *-----------------------------------------------------------------------*/
9723:
9724: /*------------------------------------------------------------------------*
9725: | Bug 3955422 - Remove the logic below since for LTE , decision is to |
9726: | retreive data from zx_lines_det_factors always |
9727: *-----------------------------------------------------------------------*/
9728: /*IF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'EBTAX' THEN
9729: l_cursor_rows := C_LINES_PER_FETCH ;
9730: ELSIF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'LTE' THEN
9793: END IF;
9794: RETURN;
9795: END IF;
9796:
9797: -- SDSU Bug 6605681, When AP is calling invoice validation in bulk process, it errors out with U1 violation in zx_lines_det_factors.
9798: -- 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.
9799: -- 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
9800: -- Start Bug 6605681
9801: IF c_lines%ISOPEN THEN CLOSE c_lines;
10900: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
10901: BEGIN
10902: SELECT count(*)
10903: INTO l_count
10904: FROM ZX_LINES_DET_FACTORS
10905: WHERE application_id = p_event_class_rec.application_id
10906: AND entity_code = p_event_class_rec.entity_code
10907: AND event_class_code = p_event_class_rec.event_class_code
10908: AND trx_id = p_event_class_rec.trx_id
11225: -- partner processing.
11226: IF get_lines_det_factors%ROWCOUNT = 0 THEN
11227: SELECT count(*)
11228: INTO l_count
11229: FROM ZX_LINES_DET_FACTORS
11230: WHERE application_id = p_event_class_rec.application_id
11231: AND entity_code = p_event_class_rec.entity_code
11232: AND event_class_code = p_event_class_rec.event_class_code
11233: AND trx_id = p_event_class_rec.trx_id
11353: END IF;
11354:
11355: /*Merge ruled out by performance due to record of table structure*/
11356: --Do not perform updates in case of lines called from Determining Factors window since
11357: --line is already updated in zx_lines_det_factors due to call to update table handler
11358: -- by products
11359:
11360: --Tax calculation has been successfully performed on this document
11361: p_event_class_rec.TAX_CALCULATION_DONE_FLAG := 'Y';
11439: END IF;
11440: RETURN;
11441: END IF;
11442: --Do not perform inserts in case of lines called from Determining Factors window since
11443: --line is already existing in zx_lines_det_factors
11444: ELSIF ZX_API_PUB.G_DATA_TRANSFER_MODE = 'WIN' THEN
11445: db_update_line_det_factors (p_trx_line_dist_tbl => ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl,
11446: p_event_class_rec => p_event_class_rec,
11447: p_update_index => ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id.LAST,
11605: --Call LTE package if tax method is LTE
11606: IF nvl(p_event_class_rec.TAX_METHOD_CODE,'EBTAX') = 'LTE' THEN
11607: -- bug#5665057-
11608: IF ZX_API_PUB.G_DATA_TRANSFER_MODE = 'WIN' THEN
11609: update zx_lines_det_factors
11610: set event_id = p_event_class_rec.event_id
11611: where application_id = p_event_class_rec.application_id
11612: AND entity_code = p_event_class_rec.entity_code
11613: AND event_class_code = p_event_class_rec.event_class_code
11684: EXCEPTION
11685: WHEN FND_API.G_EXC_ERROR THEN
11686: ROLLBACK TO Calculate_Tax_LTE;
11687: IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
11688: FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'Deleting Records from Zx_Lines_Det_Factors for Trx_id : '||l_event_class_rec.trx_id );
11689: END IF;
11690:
11691: DELETE FROM zx_lines_det_factors
11692: WHERE application_id = l_event_class_rec.application_id
11687: IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
11688: FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'Deleting Records from Zx_Lines_Det_Factors for Trx_id : '||l_event_class_rec.trx_id );
11689: END IF;
11690:
11691: DELETE FROM zx_lines_det_factors
11692: WHERE application_id = l_event_class_rec.application_id
11693: AND entity_code = l_event_class_rec.entity_code
11694: AND event_class_code = l_event_class_rec.event_class_code
11695: AND trx_id = l_event_class_rec.trx_id;
13307: EXCEPTION
13308: WHEN FND_API.G_EXC_ERROR THEN
13309: ROLLBACK TO Import_Tax_LTE;
13310: IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
13311: FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'Deleting Records from Zx_Lines_Det_Factors for Trx_id : '||l_event_class_rec.trx_id );
13312: END IF;
13313:
13314: DELETE FROM zx_lines_det_factors
13315: WHERE application_id = l_event_class_rec.application_id
13310: IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
13311: FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'Deleting Records from Zx_Lines_Det_Factors for Trx_id : '||l_event_class_rec.trx_id );
13312: END IF;
13313:
13314: DELETE FROM zx_lines_det_factors
13315: WHERE application_id = l_event_class_rec.application_id
13316: AND entity_code = l_event_class_rec.entity_code
13317: AND event_class_code = l_event_class_rec.event_class_code
13318: AND trx_id = l_event_class_rec.trx_id;
13718: /*------------------------------------------------------------------------+
13719: | Override Summary Tax Lines |
13720: + ------------------------------------------------------------------------*/
13721:
13722: --Pass the tax event type code derived into event class rec instead of that fetched from zx_lines_det_factors
13723: zx_global_structures_pkg.trx_line_dist_tbl.TAX_EVENT_TYPE_CODE(l_trx_line_index):=p_event_class_rec.tax_event_type_code;
13724:
13725: ZX_TDS_CALC_SERVICES_PUB_PKG.override_summary_tax_lines(l_trx_line_index
13726: ,p_event_class_rec
14087: /* -----------------------------------------------------------------+
14088: | Call TDS routine to override the detail tax lines |
14089: | |
14090: + -----------------------------------------------------------------*/
14091: --Pass the tax event type code derived into event class rec instead of that fetched from zx_lines_det_factors
14092: zx_global_structures_pkg.trx_line_dist_tbl.TAX_EVENT_TYPE_CODE(l_trx_line_index):=p_event_class_rec.tax_event_type_code;
14093:
14094: ZX_TDS_CALC_SERVICES_PUB_PKG.override_detail_tax_lines(l_trx_line_index
14095: ,p_event_class_rec
14210: p_event_class_rec.record_for_partners_flag = 'Y' THEN
14211:
14212: /*Commented due to GSCC error - need to provide all column names for insert
14213: INSERT INTO ZX_DETAIL_TAX_LINES_GT
14214: SELECT * from ZX_LINES
14215: WHERE application_id = p_event_class_rec.application_id
14216: AND entity_code = p_event_class_rec.entity_code
14217: AND event_class_code = p_event_class_rec.event_class_code
14218: AND trx_id = p_event_class_rec.trx_id
14328: END IF;--tax_provider_id is not null
14329: END LOOP;
14330: END IF; --recording flag
14331:
14332: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
14333: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
14334: p_event => 'UPDATE',
14335: p_tax_regime_code => null,
14336: p_provider_id => null,
14329: END LOOP;
14330: END IF; --recording flag
14331:
14332: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
14333: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
14334: p_event => 'UPDATE',
14335: p_tax_regime_code => null,
14336: p_provider_id => null,
14337: x_return_status => l_return_status
14350: for tax lines synchronization view needed for partner service.
14351: But, eBTax service ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines
14352: creates tax lines for the records remaining in the
14353: ZX_DETAIL_TAX_LINES_GT. This results in tax line not getting
14354: deleted from zx_lines when partner created tax line is deleted.
14355: Hence, following delete is being issued. */
14356:
14357: BEGIN
14358: DELETE FROM ZX_DETAIL_TAX_LINES_GT
14566: -- PUBLIC PROCEDURE
14567: -- reverse_document
14568: --
14569: -- DESCRIPTION
14570: -- Negate the line amount and line quantity and store into zx_lines_det_factors
14571: --
14572: -- CALLED BY
14573: -- ZX_API_PUB.reverse_document
14574: -----------------------------------------------------------------------
14886: zl.START_EXPENSE_DATE ,
14887: zl.TRX_BATCH_ID ,
14888: zl.APPLIED_TO_TRX_NUMBER ,
14889: zl.PROVNL_TAX_DETERMINATION_DATE
14890: FROM ZX_LINES_DET_FACTORS zl,
14891: ZX_REVERSE_TRX_LINES_GT zrtlgt,
14892: ZX_REV_TRX_HEADERS_GT zrthgt
14893: WHERE zl.trx_id =zrtlgt.reversed_trx_id
14894: AND zl.application_id = zrtlgt.reversed_appln_id
15283: END LOOP;
15284: END IF;
15285:
15286: /* -------------------------------------------------------+
15287: | Insert into zx_lines_det_factors |
15288: + -------------------------------------------------------*/
15289: db_insert_line_det_factors (p_trx_line_dist_tbl => zx_global_structures_pkg.trx_line_dist_tbl,
15290: p_event_class_rec => p_event_class_rec,
15291: p_insert_index => zx_global_structures_pkg.trx_line_dist_tbl.APPLICATION_ID.FIRST,
15854: END IF;
15855: END LOOP;
15856: END IF;
15857:
15858: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
15859: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
15860: p_event => 'UPDATE',
15861: p_tax_regime_code => null,
15862: p_provider_id => null,
15855: END LOOP;
15856: END IF;
15857:
15858: /* RE-INITIALISE SYNCHRONIZATION FLAG in ZX_LINES TO 'N' */
15859: zx_lines_table_handler(p_event_class_rec => p_event_class_rec,
15860: p_event => 'UPDATE',
15861: p_tax_regime_code => null,
15862: p_provider_id => null,
15863: x_return_status => l_return_status
15923: p_event_class_rec.record_for_partners_flag = 'Y' THEN
15924: BEGIN
15925: SELECT zxdet.partner_migrated_flag
15926: INTO l_partner_migrated_flag
15927: FROM zx_lines_det_factors zxdet
15928: WHERE zxdet.application_id = p_event_class_rec.application_id
15929: AND zxdet.entity_code = p_event_class_rec.entity_code
15930: AND zxdet.event_class_code = p_event_class_rec.event_class_code
15931: AND zxdet.trx_id = p_event_class_rec.trx_id
15932: AND rownum = 1;
15933: EXCEPTION WHEN OTHERS THEN
15934: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
15935: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
15936: 'No rows in zx_lines_det_factors for the transaction' );
15937: END IF;
15938: RETURN;
15939: END;
15940:
16108: -- PUBLIC PROCEDURE
16109: -- insupd_line_det_factors
16110: --
16111: -- DESCRIPTION
16112: -- Called to insert/update to zx_lines_det_factors for products integrating
16113: -- with the Line det factors UI
16114: --
16115: -- CALLED BY
16116: -- ZX_API_PUB.insert_line_det_factors
16306: ELSIF ZX_API_PUB.G_PUB_SRVC = 'UPDATE_LINE_DET_FACTORS' THEN
16307:
16308: SELECT tax_calculation_done_flag
16309: INTO p_event_class_rec.tax_calculation_done_flag
16310: FROM ZX_LINES_DET_FACTORS
16311: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
16312: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
16313: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
16314: AND TRX_ID = p_event_class_rec.TRX_ID
16348: END insupd_line_det_factors;
16349:
16350: -----------------------------------------------------------------------
16351: -- PUBLIC PROCEDURE
16352: -- zx_lines_table_handler
16353: --
16354: -- DESCRIPTION
16355: -- Handles inserts/updates/deletes to zx_lines for partner integration
16356: --
16351: -- PUBLIC PROCEDURE
16352: -- zx_lines_table_handler
16353: --
16354: -- DESCRIPTION
16355: -- Handles inserts/updates/deletes to zx_lines for partner integration
16356: --
16357: -- CALLED BY
16358: -- ZX_API_PUB.ptnr_sync_calc_tax
16359: -- override_tax
16357: -- CALLED BY
16358: -- ZX_API_PUB.ptnr_sync_calc_tax
16359: -- override_tax
16360: ----------------------------------------------------------------------
16361: PROCEDURE zx_lines_table_handler
16362: (
16363: p_event_class_rec IN ZX_API_PUB.event_class_rec_type ,
16364: p_event IN VARCHAR2,
16365: p_tax_regime_code IN VARCHAR2,
16366: p_provider_id IN NUMBER,
16367: x_return_status OUT NOCOPY VARCHAR2
16368: )IS
16369:
16370: l_api_name CONSTANT VARCHAR2(30):= 'ZX_LINES_TABLE_HANDLER';
16371: l_context_info_rec ZX_API_PUB.context_info_rec_type;
16372: l_return_status VARCHAR2(1);
16373:
16374: BEGIN
16378:
16379: x_return_status := FND_API.G_RET_STS_SUCCESS ;
16380:
16381: IF p_event ='UPDATE' THEN
16382: UPDATE ZX_LINES
16383: SET sync_with_prvdr_flag ='N'
16384: WHERE application_id = p_event_class_rec.application_id
16385: AND entity_code = p_event_class_rec.entity_code
16386: AND event_class_code = p_event_class_rec.event_class_code
16386: AND event_class_code = p_event_class_rec.event_class_code
16387: AND trx_id = p_event_class_rec.trx_id
16388: AND tax_provider_id is not null;
16389: ELSIF p_event = 'DELETE' THEN
16390: DELETE from zx_lines
16391: WHERE application_id = p_event_class_rec.application_id
16392: AND entity_code = p_event_class_rec.entity_code
16393: AND event_class_code = p_event_class_rec.event_class_code
16394: AND trx_id = p_event_class_rec.trx_id
16405: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
16406: IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
16407: FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
16408: END IF;
16409: END zx_lines_table_handler;
16410:
16411: -----------------------------------------------------------------------
16412: -- PUBLIC PROCEDURE
16413: -- Get_Tax_Profile_Ids
16547: -- Bug 8859101 Starts
16548:
16549: l_default_taxation_country VARCHAR2(2);
16550: l_doc_sub_type VARCHAR2(240);
16551: l_tax_invoice_date ZX_LINES_DET_FACTORS.TAX_INVOICE_DATE%TYPE;
16552:
16553: -- Bug 8859101 ends
16554:
16555:
16570: l_event_class_rec.prod_family_grp_code := p_event_class_rec.prod_family_grp_code;
16571: l_event_class_rec.tax_method_code := p_event_class_rec.tax_method_code;
16572:
16573: /*If the adjusted document information is passed with the transaction line, then derive the values
16574: tax determining factors from ZX_LINES_DET_FACTORS for the adjusted document*/
16575: IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_APPLICATION_ID(p_trx_line_index) is not null THEN
16576: BEGIN
16577: SELECT default_taxation_country,
16578: document_sub_type,
16594: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16595: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16596: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16597: l_tax_classification_code
16598: FROM ZX_LINES_DET_FACTORS
16599: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
16600: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
16601: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
16602: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
16639: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16640: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16641: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16642: l_tax_classification_code
16643: FROM ZX_LINES_DET_FACTORS
16644: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
16645: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
16646: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
16647: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
16649: AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index);
16650: END;
16651: --upgrade of adjusted doc complete
16652: /*If the applied from document information is passed with the transaction line, then derive the values
16653: tax determining factors from ZX_LINES_DET_FACTORS for the applied from document*/
16654: ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index) is not null THEN
16655: BEGIN
16656: SELECT default_taxation_country,
16657: document_sub_type,
16673: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16674: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16675: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16676: l_tax_classification_code
16677: FROM ZX_LINES_DET_FACTORS
16678: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
16679: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
16680: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
16681: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
16718: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16719: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16720: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16721: l_tax_classification_code
16722: FROM ZX_LINES_DET_FACTORS
16723: WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
16724: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
16725: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
16726: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
16728: AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
16729: END;
16730: --upgrade of applied from doc complete
16731: /*If the source document information is passed with the transaction line, then derive the values
16732: tax determining factors from ZX_LINES_DET_FACTORS for the source document*/
16733: /*ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_TRX_ID(p_trx_line_index) is not null THEN
16734: BEGIN
16735: --To support intercompany transactions, fetch the intercompany information
16736: --from zx_evnt_cls_mappings
16741: AP's event classes don't match one-to-one with AR. This has resulted
16742: into an issue when Credit memo is imported during Intercompany xfer.
16743: 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'
16744: with corresponding intrcmp_src_evnt_cls_code 'INVOICE'.
16745: 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.
16746: */
16747:
16748: /*SELECT intrcmp_src_appln_id,
16749: intrcmp_src_entity_code,
16777: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16778: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16779: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16780: l_tax_classification_code
16781: FROM ZX_LINES_DET_FACTORS
16782: WHERE application_id = nvl(l_intrcmp_src_appln_id,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(p_trx_line_index))
16783: AND entity_code = nvl(l_intrcmp_src_entity_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(p_trx_line_index))
16784: -- 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))
16785: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
16828: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_trx_line_index),
16829: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_trx_line_index),
16830: ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_trx_line_index),
16831: l_tax_classification_code
16832: FROM ZX_LINES_DET_FACTORS
16833: WHERE application_id = nvl(l_intrcmp_src_appln_id,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(p_trx_line_index))
16834: AND entity_code = nvl(l_intrcmp_src_entity_code,ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(p_trx_line_index))
16835: 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))
16836: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
16852: tax_invoice_date --Bug8541137
16853: INTO l_default_taxation_country,
16854: l_doc_sub_type,
16855: l_tax_invoice_date --Bug8541137
16856: FROM ZX_LINES_DET_FACTORS
16857: WHERE application_id = p_event_class_rec.application_id
16858: AND entity_code = p_event_class_rec.entity_code
16859: AND event_class_code = p_event_class_rec.event_class_code
16860: AND trx_id = p_event_class_rec.trx_id
16923: -- For records in trx_line_dist_tbl
16924: -- Loop
16925: -- If all of defaulting attributes null (not including tax classification) then
16926: -- If line_level_action is UPDATE
16927: -- Fetch all attributes from zx_lines_det_factors
16928: -- WHEN NO_DATA_FOUND
16929: -- IF event_class_code in ('PO_PA','RELEASE')
16930: -- call on-the-fly upgrade
16931: -- ELSIF user_upd_det_factors_flag = 'N'
16934: -- if item/item_org_id/country has changed
16935: -- call RE-defaulting API for intended_use, product_fiscal_classification, product type
16936: -- if item/item_org_id/assessable value has changed
16937: -- call redefaulting API for assessable value
16938: -- if nothing changed copy from determining attrs from zx_lines_det_factors
16939: -- Always call the tax classification defaulting API
16940: -- ELSIF line level action is CREATE
16941: -- IF historical_tax_code_id passed
16942: -- redefault the tax attributes for PO (on the fly migration)
17916: -- PUBLIC PROCEDURE
17917: -- call_redefaulting_APIs
17918: --
17919: -- DESCRIPTION
17920: -- 1) Fetch and populate all determining attributes from zx_lines_det_factors
17921: -- 2) If product id or product org id changes call the redefaulting APIs for product
17922: -- fiscal classification and intended use
17923: -- 3) If product id, product org id, line amount changes, call the redefaulting APIs
17924: -- for assessable value
17965: --Bug#7045194: validate input tax classification code
17966:
17967: --CURSOR validate_input_tcc_csr
17968: --(c_input_tcc ZX_RATES_B.tax_rate_code%TYPE,
17969: --c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
17970: --c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
17971: --IS
17972: --SELECT COUNT(1)
17973: -- FROM ZX_INPUT_CLASSIFICATIONS_V
17966:
17967: --CURSOR validate_input_tcc_csr
17968: --(c_input_tcc ZX_RATES_B.tax_rate_code%TYPE,
17969: --c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
17970: --c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
17971: --IS
17972: --SELECT COUNT(1)
17973: -- FROM ZX_INPUT_CLASSIFICATIONS_V
17974: -- WHERE lookup_code = c_input_tcc
17981: --Bug#7045194: validate output tax classification code
17982:
17983: -- CURSOR validate_output_tcc_csr
17984: -- (c_output_tcc ZX_RATES_B.tax_rate_code%TYPE,
17985: -- c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
17986: -- c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
17987: -- IS
17988: -- SELECT COUNT(1)
17989: -- FROM ZX_OUTPUT_CLASSIFICATIONS_V
17982:
17983: -- CURSOR validate_output_tcc_csr
17984: -- (c_output_tcc ZX_RATES_B.tax_rate_code%TYPE,
17985: -- c_trx_date ZX_LINES_DET_FACTORS.trx_date%TYPE,
17986: -- c_internal_organization_id ZX_LINES.internal_organization_id%TYPE)
17987: -- IS
17988: -- SELECT COUNT(1)
17989: -- FROM ZX_OUTPUT_CLASSIFICATIONS_V
17990: -- WHERE lookup_code = c_output_tcc
18041: l_line_amt,
18042: l_input_tax_class_code,
18043: l_output_tax_class_code,
18044: l_trx_line_id
18045: FROM ZX_LINES_DET_FACTORS
18046: WHERE application_id = p_event_class_rec.application_id
18047: AND entity_code = p_event_class_rec.entity_code
18048: AND event_class_code = p_event_class_rec.event_class_code
18049: AND trx_id = p_event_class_rec.trx_id
18102: l_input_tax_class_code,
18103: l_output_tax_class_code,
18104: l_trx_line_id
18105: FROM
18106: ZX_LINES_DET_FACTORS
18107: WHERE
18108: application_id = p_event_class_rec.application_id
18109: AND entity_code = p_event_class_rec.entity_code
18110: AND event_class_code = p_event_class_rec.event_class_code
18683: l_legal_entity_id NUMBER;
18684: l_establishment_id NUMBER;
18685: l_tbl_index BINARY_INTEGER;
18686: l_trx_date DATE;
18687: l_zx_lines_cnt NUMBER;
18688:
18689: BEGIN
18690: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18691: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',
18763: 'Getting Count of tax lines with NULL establishment_id');
18764: END IF;
18765:
18766: SELECT COUNT(*)
18767: INTO l_zx_lines_cnt
18768: FROM zx_lines
18769: WHERE establishment_id IS NULL
18770: AND application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_index)
18771: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(p_index)
18764: END IF;
18765:
18766: SELECT COUNT(*)
18767: INTO l_zx_lines_cnt
18768: FROM zx_lines
18769: WHERE establishment_id IS NULL
18770: AND application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_index)
18771: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(p_index)
18772: AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.entity_code(p_index)
18773: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(p_index);
18774:
18775: IF ( g_level_statement >= g_current_runtime_level) THEN
18776: FND_LOG.STRING(g_level_statement,g_module_name||l_api_name,
18777: 'No. of tax lines with NULL establishment_id : '||l_zx_lines_cnt);
18778: END IF;
18779:
18780: IF l_zx_lines_cnt > 0 THEN
18781: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18776: FND_LOG.STRING(g_level_statement,g_module_name||l_api_name,
18777: 'No. of tax lines with NULL establishment_id : '||l_zx_lines_cnt);
18778: END IF;
18779:
18780: IF l_zx_lines_cnt > 0 THEN
18781: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18782: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18783: 'Updating ZX Tables for Establishment_id');
18784: END IF;
18782: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18783: 'Updating ZX Tables for Establishment_id');
18784: END IF;
18785:
18786: UPDATE zx_lines
18787: SET establishment_id = l_establishment_id,
18788: trx_date = l_trx_date
18789: WHERE establishment_id IS NULL
18790: AND application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_index)
18793: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(p_index);
18794:
18795: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18796: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18797: 'Updated ZX_Lines Table rows : '||sql%rowcount);
18798: END IF;
18799:
18800: UPDATE zx_lines_summary
18801: SET establishment_id = l_establishment_id
18796: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18797: 'Updated ZX_Lines Table rows : '||sql%rowcount);
18798: END IF;
18799:
18800: UPDATE zx_lines_summary
18801: SET establishment_id = l_establishment_id
18802: WHERE establishment_id IS NULL
18803: AND application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_index)
18804: AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(p_index)
18806: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(p_index);
18807:
18808: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18809: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18810: 'Updated ZX_Lines_summary Table rows : '||sql%rowcount);
18811: END IF;
18812:
18813: UPDATE zx_lines_det_factors
18814: SET establishment_id = l_establishment_id,
18809: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18810: 'Updated ZX_Lines_summary Table rows : '||sql%rowcount);
18811: END IF;
18812:
18813: UPDATE zx_lines_det_factors
18814: SET establishment_id = l_establishment_id,
18815: trx_date = l_trx_date
18816: WHERE establishment_id IS NULL
18817: AND application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_index)
18820: AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(p_index);
18821:
18822: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
18823: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
18824: 'Updated ZX_Lines_det_factors Table rows : '||sql%rowcount);
18825: END IF;
18826:
18827: END IF;
18828: END IF;
18847: -- print_unique_columns
18848: --
18849: -- DESCRIPTION
18850: -- This is a private procedure to print the unique columns in case of
18851: -- ZX_LINES_DET_FACTORS_U1 error for debug purpose
18852: --
18853: -- HISTORY
18854: -- Added by Binapani Beura on 23-Nov-2010
18855: ------------------------------------------------------------------------------
18863: p_trx_line_id IN NUMBER,
18864: p_trx_level_type IN VARCHAR2)
18865: IS
18866:
18867: -- Cursor to fetch unique columns from ZX_LINES_DET_FACTORS --
18868: CURSOR get_unique_columns IS
18869: SELECT application_id,
18870: entity_code,
18871: event_class_code,
18871: event_class_code,
18872: trx_id,
18873: trx_line_id,
18874: trx_level_type
18875: FROM zx_lines_det_factors
18876: WHERE application_id = p_application_id
18877: AND event_class_code = p_event_class_code
18878: AND entity_code = p_entity_code
18879: AND trx_id = p_trx_id