[Home] [Help]
9717: | PROCEDURE |
9718: | create_rules |
9719: | |
9720: | DESCRIPTION |
9721: | This routine inserts data into ZX_RULES_B,_TL,ZX_PROCESS_RESULTS |
9722: | Based on AP recovery rules and associated rates. |
9723: | |
9724: | SCOPE - PUBLIC |
9725: | |
9746:
9747: IF L_MULTI_ORG_FLAG = 'Y'
9748: THEN
9749:
9750: INSERT INTO ZX_RULES_B_TMP
9751: (
9752: TAX_RULE_CODE ,
9753: TAX ,
9754: TAX_REGIME_CODE ,
9791: 'Y' ,--ENABLED_FLAG
9792: 'MIGRATED' ,--RECORD_TYPE_CODE
9793: 'EX Acct String Range-Party FC' ,--DET_FACTOR_TEMPL_CODE
9794: CONTENT_OWNER_ID ,
9795: zx_rules_b_s.nextval ,--TAX_RULE_ID
9796: fnd_global.user_id ,
9797: SYSDATE ,
9798: fnd_global.user_id ,
9799: SYSDATE ,
9814: group by name having count(*)>1)
9815: THEN
9816: --check if ap_tax_recvry_rules_all.NAME || rates.TAX is more than 30 characters in length
9817: DECODE(SIGN(LENGTHB(RULES.NAME||RATES.TAX)-30),
9818: 1, SUBSTRB(RULES.NAME,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_RULES_B_S'),
9819: RULES.NAME||RATES.TAX)
9820: ELSE -- else for GROUP BY CHECKING
9821: RULES.NAME
9822: END TAX_RULE_CODE,
9837: AND CODES.ORG_ID = PTP.PARTY_ID
9838: AND PTP.PARTY_TYPE_CODE ='OU'
9839: --Added following conditions for Sync process
9840: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
9841: AND not exists (select 1 from zx_rules_b
9842: where substrb(tax_rule_code,1,24)
9843: = ( --check if there exists multiple rows in ap_tax_recvry_rules_all with the same name.
9844: CASE WHEN EXISTS (select NULL
9845: from ap_tax_recvry_rules_all
9857: )
9858: );
9859: ELSE
9860:
9861: INSERT INTO ZX_RULES_B_TMP
9862: (
9863: TAX_RULE_CODE ,
9864: TAX ,
9865: TAX_REGIME_CODE ,
9902: 'Y' ,--ENABLED_FLAG
9903: 'MIGRATED' ,--RECORD_TYPE_CODE
9904: 'EX Acct String Range-Party FC' ,--DET_FACTOR_TEMPL_CODE
9905: CONTENT_OWNER_ID ,
9906: zx_rules_b_s.nextval ,--TAX_RULE_ID
9907: fnd_global.user_id ,
9908: SYSDATE ,
9909: fnd_global.user_id ,
9910: SYSDATE ,
9926: )
9927: THEN
9928: --check if ap_tax_recvry_rules_all.NAME || rates.TAX is more than 30 characters in length
9929: DECODE(SIGN(LENGTHB(RULES.NAME||RATES.TAX)-30),
9930: 1, SUBSTRB(RULES.NAME,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_RULES_B_S'),
9931: RULES.NAME||RATES.TAX
9932: )
9933: ELSE -- else for GROUP BY CHECKING
9934: RULES.NAME
9951: AND CODES.ORG_ID = PTP.PARTY_ID
9952: AND PTP.PARTY_TYPE_CODE ='OU'
9953: --Added following conditions for Sync process
9954: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
9955: AND not exists (select 1 from zx_rules_b
9956: where substrb(tax_rule_code,1,24)
9957: = ( --check if there exists multiple rows in ap_tax_recvry_rules_all with the same name.
9958: CASE WHEN EXISTS (select NULL
9959: from ap_tax_recvry_rules_all
10008: ,l.LANGUAGE_CODE
10009: FROM ap_tax_codes_all codes,
10010: ap_tax_recvry_rules_all Rules,
10011: zx_rates_b rates,
10012: zx_rules_b b,
10013: fnd_languages L
10014: WHERE l.installed_flag in ('I', 'B')
10015: AND codes.TAX_RECOVERY_RULE_ID = RULES.RULE_ID
10016: AND codes.tax_id = rates.source_id
10121: AP_TAX_CODES_ALL CODES,
10122: ap_tax_recvry_rules_all AP_RULES,
10123: ap_tax_recvry_rates_all AP_RATES,
10124: ZX_RATES_B ZX_RATES,
10125: ZX_RULES_B ZX_RULES,
10126: ZX_CONDITION_GROUPS_B GROUPS,
10127: zx_party_tax_profile ptp
10128: WHERE
10129: CODES.TAX_ID = NVL(ZX_RATES.SOURCE_ID, ZX_RATES.TAX_RATE_ID)
10277: AP_TAX_CODES_ALL CODES,
10278: ap_tax_recvry_rules_all AP_RULES,
10279: ap_tax_recvry_rates_all AP_RATES,
10280: ZX_RATES_B ZX_RATES,
10281: ZX_RULES_B ZX_RULES,
10282: ZX_CONDITION_GROUPS_B GROUPS,
10283: zx_party_tax_profile ptp
10284: WHERE
10285: CODES.TAX_ID = NVL(ZX_RATES.SOURCE_ID, ZX_RATES.TAX_RATE_ID)