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

APPS.ZX_PTP_IMPORT dependencies on ZX_REGISTRATIONS_INT

Line 217: G_INTF_REG_TABLE_NAME := 'ZX_REGISTRATIONS_INT';

213: --
214: -- interface table names
215: --
216: G_INTF_PTP_TABLE_NAME := 'ZX_PARTY_TAX_PROFILE_INT';
217: G_INTF_REG_TABLE_NAME := 'ZX_REGISTRATIONS_INT';
218: G_INTF_EXE_TABLE_NAME := 'ZX_EXEMPTIONS_INT';
219: G_INTF_RCA_TABLE_NAME := 'ZX_REPORT_CODES_ASSOC_INT';
220: G_INTF_CUS_TABLE_NAME := 'RA_CUSTOMERS_INTERFACE_ALL';
221: G_INTF_SUP_TABLE_NAME := 'AP_SUPPLIERS_INT';

Line 457: DELETE zx_registrations_int

453: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||G_PROC_NAME,G_DEBUG_STATEMENT);
454: END IF;
455:
456:
457: DELETE zx_registrations_int
458: WHERE request_id = G_REQUEST_ID
459: AND record_status = G_RS_VALID;
460:
461: G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_REG_TABLE_NAME;

Line 545: UPDATE zx_registrations_int reg_int

541: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
542: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'ResetRecStatusForChildTables',G_DEBUG_STATEMENT);
543: END IF;
544:
545: UPDATE zx_registrations_int reg_int
546: SET record_status = NULL
547: WHERE request_id = G_REQUEST_ID
548: AND record_status = G_RS_VALID
549: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 766: FROM ZX_REGISTRATIONS_INT

762:
763: BEGIN
764: SELECT G_INTF_REG_TABLE_NAME
765: INTO l_temp_char_30
766: FROM ZX_REGISTRATIONS_INT
767: WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
768: AND NVL(batch_id,G_MISS_NUM) =
769: NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
770: AND ROWNUM = 1;

Line 2630: FROM zx_registrations_int

2626: PROCEDURE ProcessRegistrationRecord IS
2627:
2628: CURSOR get_country_codes IS
2629: SELECT DISTINCT prog_int_char1
2630: FROM zx_registrations_int
2631: WHERE record_status = G_RS_IN_PROCESS
2632: AND request_id = G_REQUEST_ID
2633: AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0
2634: AND prog_int_char1 IS NOT NULL

Line 2656: UPDATE zx_registrations_int

2652:
2653: -------------------------
2654: -- Populate default values
2655: -------------------------
2656: UPDATE zx_registrations_int
2657: SET request_id = G_REQUEST_ID
2658: ,record_status = G_RS_IN_PROCESS
2659: ,dml_type = UPPER(dml_type)
2660: ,prog_int_char1 = NULL

Line 2676: UPDATE zx_registrations_int

2672: -------------------------
2673: -- Check Mandatory Paramters
2674: -------------------------
2675:
2676: UPDATE zx_registrations_int
2677: SET error_number = G_ES_REG_MAND_PARAM_MISSING
2678: WHERE request_id = G_REQUEST_ID
2679: AND record_status = G_RS_IN_PROCESS
2680: AND NVL(tax_regime_code,tax_regime_name) IS NULL;

Line 2691: UPDATE zx_registrations_int orig

2687: -------------------------
2688: -- Duplicate records checking
2689: -------------------------
2690:
2691: UPDATE zx_registrations_int orig
2692: SET error_number = error_number + G_ES_REG_DUP_INTF_RECORD,
2693: record_status = G_RS_ERROR
2694: WHERE request_id = G_REQUEST_ID
2695: AND record_status = G_RS_IN_PROCESS

Line 2699: FROM zx_registrations_int dup

2695: AND record_status = G_RS_IN_PROCESS
2696: AND error_number <> G_ES_REG_MAND_PARAM_MISSING
2697: AND EXISTS (
2698: SELECT /*+ FIRST_ROWS(1) */ 1
2699: FROM zx_registrations_int dup
2700: WHERE dup.rowid <> orig.rowid
2701: AND dup.request_id = G_REQUEST_ID
2702: AND dup.record_status = G_RS_IN_PROCESS
2703: AND NVL(dup.party_tax_profile_id,G_MISS_NUM)

Line 2742: UPDATE zx_registrations_int orig

2738: -- Record level validations
2739: -------------------------
2740: -- Registrations with overlapping date ranges.
2741:
2742: UPDATE zx_registrations_int orig
2743: SET error_number = error_number + G_ES_REG_DATE_OVERLAP,
2744: record_status = G_RS_ERROR
2745: WHERE request_id = G_REQUEST_ID
2746: AND record_status = G_RS_IN_PROCESS

Line 2750: FROM zx_registrations_int dup

2746: AND record_status = G_RS_IN_PROCESS
2747: AND BITAND(error_number,G_ES_REG_DUP_INTF_RECORD) = 0
2748: AND EXISTS (
2749: SELECT /*+ FIRST_ROWS(1) */ 1
2750: FROM zx_registrations_int dup
2751: WHERE dup.rowid <> orig.rowid
2752: AND dup.request_id = G_REQUEST_ID
2753: AND dup.record_status = G_RS_IN_PROCESS
2754: AND dup.intf_ptp_reference = orig.intf_ptp_reference

Line 2782: UPDATE ZX_REGISTRATIONS_INT REG

2778: -- TRN Validation
2779: -----------------
2780:
2781: --Initialise prog_int_char1 with Country Code
2782: UPDATE ZX_REGISTRATIONS_INT REG
2783: SET PROG_INT_CHAR1 = (SELECT COUNTRY_CODE
2784: FROM ZX_REGIMES_B TR
2785: WHERE TR.TAX_REGIME_CODE = REG.TAX_REGIME_CODE)
2786: WHERE request_id = G_REQUEST_ID

Line 2793: UPDATE zx_registrations_int reg_int

2789: AND record_status = G_RS_IN_PROCESS
2790: AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0;
2791:
2792: --Stamp Valid Status for Null registration_number OR Country Code
2793: UPDATE zx_registrations_int reg_int
2794: SET PROG_INT_NUM1 = G_PROG_INT_VAL
2795: WHERE request_id = G_REQUEST_ID
2796: AND prog_int_char1 IS NULL
2797: OR registration_number IS NULL

Line 2808: UPDATE zx_registrations_int reg

2804: );
2805: END LOOP;
2806:
2807: -- Final Updation of the Record Status and error Codes
2808: UPDATE zx_registrations_int reg
2809: SET error_number = error_number + G_ES_REG_REG_NUM_INVALID
2810: WHERE prog_int_char1 IS NOT NULL
2811: AND prog_int_char1 IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
2812: 'LV','LU','LT','IT','IE','HU','GR','GB','FR',

Line 2832: UPDATE zx_registrations_int orig

2828: -- Record level validations
2829: -------------------------
2830:
2831: -- Validation to check multiple default_registration_flag defined for a ptp
2832: UPDATE zx_registrations_int orig
2833: SET error_number = error_number + G_ES_REG_MULT_DEF_FLAG
2834: WHERE orig.record_status = G_RS_IN_PROCESS
2835: AND orig.request_id = G_REQUEST_ID
2836: AND BITAND(orig.error_number,G_ES_REG_DEFREG_FLG_INVALID) = 0

Line 2840: FROM zx_registrations_int dup

2836: AND BITAND(orig.error_number,G_ES_REG_DEFREG_FLG_INVALID) = 0
2837: AND NVL(orig.default_registration_flag,'N') = 'Y'
2838: AND EXISTS (
2839: SELECT /*+ FIRST_ROWS(1) */ 1
2840: FROM zx_registrations_int dup
2841: WHERE dup.rowid <> orig.rowid
2842: AND dup.request_id = G_REQUEST_ID
2843: AND dup.record_status = G_RS_IN_PROCESS
2844: AND NVL(dup.default_registration_flag,'N') = 'Y'

Line 2857: UPDATE zx_registrations_int

2853: END IF;
2854:
2855: -- Validation to check when REGISTRATION_STATUS_CODE = 'REGISTERED'
2856: -- and REGISTRATION_SOURCE_CODE = 'EXPLICIT', tax authority is required
2857: UPDATE zx_registrations_int
2858: SET error_number = error_number + G_ES_REG_TAX_AUT_REQ
2859: WHERE record_status = G_RS_IN_PROCESS
2860: AND request_id = G_REQUEST_ID
2861: AND BITAND(error_number,G_ES_REG_TAX_AUT_INVALID) = 0

Line 2876: UPDATE zx_registrations_int reg_int

2872: -------------------------
2873: -- No Other Validations
2874: -------------------------
2875:
2876: UPDATE zx_registrations_int reg_int
2877: SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
2878: WHERE request_id = G_REQUEST_ID
2879: AND record_status = G_RS_IN_PROCESS;
2880:

Line 2886: FROM zx_registrations_int reg_int

2882: SET record_status = NULL
2883: WHERE request_id = G_REQUEST_ID
2884: AND record_status = G_RS_VALID
2885: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
2886: FROM zx_registrations_int reg_int
2887: WHERE request_id = G_REQUEST_ID
2888: AND record_status = G_RS_ERROR
2889: AND reg_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
2890:

Line 2948: UPDATE zx_registrations_int

2944: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'RegValueToIdConversion','RegValueToIdConversion(+)');
2945: END IF;
2946:
2947: -- Records with invalid dml_type
2948: UPDATE zx_registrations_int
2949: SET error_number = error_number + G_ES_REG_TRANS_TYPE_INVALID
2950: WHERE record_status = G_RS_IN_PROCESS
2951: AND request_id = G_REQUEST_ID
2952: AND dml_type <> 'CREATE';

Line 2960: UPDATE zx_registrations_int

2956: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
2957: END IF;
2958:
2959: -- Validate the fields effective_to and effective_from
2960: UPDATE zx_registrations_int
2961: SET error_number = error_number + G_ES_REG_DATE_RANGE_INVALID
2962: WHERE record_status = G_RS_IN_PROCESS
2963: AND request_id = G_REQUEST_ID
2964: AND effective_to IS NOT NULL

Line 2973: UPDATE zx_registrations_int reg_int

2969: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
2970: END IF;
2971:
2972: -- validating the fields registration_type_code and registration_type_name
2973: UPDATE zx_registrations_int reg_int
2974: SET error_number = error_number + G_ES_REG_REG_TYPE_INVALID
2975: WHERE (( REGISTRATION_TYPE_CODE IS NOT NULL
2976: AND NOT EXISTS
2977: (

Line 3010: UPDATE zx_registrations_int reg_int

3006: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3007: END IF;
3008:
3009: -- value to id conversion for registration_type_code
3010: UPDATE zx_registrations_int reg_int
3011: SET registration_type_code = (
3012: SELECT fndlookup.lookup_code
3013: FROM fnd_lookups fndlookup
3014: WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'

Line 3032: UPDATE zx_registrations_int reg_int

3028: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3029: END IF;
3030:
3031: -- validating the fields registration_status_code and registration_status_name
3032: UPDATE zx_registrations_int reg_int
3033: SET error_number = error_number + G_ES_REG_REG_STAT_INVALID
3034: WHERE (( registration_status_code IS NOT NULL
3035: AND NOT EXISTS
3036: (

Line 3069: UPDATE zx_registrations_int reg_int

3065: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3066: END IF;
3067:
3068: -- value to id conversion for registration_status_code
3069: UPDATE zx_registrations_int reg_int
3070: SET registration_status_code = (
3071: SELECT fndlookup.lookup_code
3072: FROM fnd_lookups fndlookup
3073: WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_STATUS'

Line 3091: UPDATE zx_registrations_int reg_int

3087: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3088: END IF;
3089:
3090: -- validating the fields registration_source_code and registration_source_name
3091: UPDATE zx_registrations_int reg_int
3092: SET error_number = error_number + G_ES_REG_REG_SRC_INVALID
3093: WHERE (( registration_source_code IS NOT NULL
3094: AND NOT EXISTS
3095: (

Line 3128: UPDATE zx_registrations_int reg_int

3124: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3125: END IF;
3126:
3127: -- value to id conversion for registration_source_code
3128: UPDATE zx_registrations_int reg_int
3129: SET registration_source_code = (
3130: SELECT fndlookup.lookup_code
3131: FROM fnd_lookups fndlookup
3132: WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_SOURCE'

Line 3150: UPDATE zx_registrations_int reg_int

3146: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3147: END IF;
3148:
3149: -- validating the fields registration_reason_code and registration_reason_name
3150: UPDATE zx_registrations_int reg_int
3151: SET error_number = error_number + G_ES_REG_REG_RSN_INVALID
3152: WHERE (( registration_reason_code IS NOT NULL
3153: AND NOT EXISTS
3154: (

Line 3187: UPDATE zx_registrations_int reg_int

3183: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3184: END IF;
3185:
3186: -- value to id conversion for registration_reason_code
3187: UPDATE zx_registrations_int reg_int
3188: SET registration_reason_code = (
3189: SELECT fndlookup.lookup_code
3190: FROM fnd_lookups fndlookup
3191: WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_REASON'

Line 3209: UPDATE zx_registrations_int reg_int

3205: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3206: END IF;
3207:
3208: -- validating the fields tax_authority_id and tax_authority_name
3209: UPDATE zx_registrations_int reg_int
3210: SET error_number = error_number + G_ES_REG_TAX_AUT_INVALID
3211: WHERE (( tax_authority_id IS NOT NULL
3212: AND NOT EXISTS
3213: (

Line 3245: UPDATE zx_registrations_int reg_int

3241: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3242: END IF;
3243:
3244: -- value to id conversion for tax_authority_id
3245: UPDATE zx_registrations_int reg_int
3246: SET tax_authority_id = (
3247: SELECT PTP.party_tax_profile_id
3248: FROM zx_party_tax_profile ptp, hz_parties pty
3249: WHERE ptp.party_type_code ='TAX_AUTHORITY'

Line 3266: UPDATE zx_registrations_int

3262: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3263: END IF;
3264:
3265: -- validating the field inclusive_tax_flag
3266: UPDATE zx_registrations_int
3267: SET error_number = error_number + G_ES_REG_INC_FLG_INVALID
3268: WHERE record_status = G_RS_IN_PROCESS
3269: AND request_id = G_REQUEST_ID
3270: AND inclusive_tax_flag NOT IN ('Y','N',NULL);

Line 3278: UPDATE zx_registrations_int reg_int

3274: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3275: END IF;
3276:
3277: -- validating the fields rounding_rule_code and rounding_rule_name
3278: UPDATE zx_registrations_int reg_int
3279: SET error_number = error_number + G_ES_REG_ROUND_RULE_INVALID
3280: WHERE (( rounding_rule_code IS NOT NULL
3281: AND NOT EXISTS
3282: (

Line 3315: UPDATE zx_registrations_int reg_int

3311: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3312: END IF;
3313:
3314: -- value to id conversion for rounding_rule_code
3315: UPDATE zx_registrations_int reg_int
3316: SET rounding_rule_code = (
3317: SELECT lookup_code
3318: FROM fnd_lookups
3319: WHERE lookup_type = 'ZX_ROUNDING_RULE'

Line 3337: UPDATE zx_registrations_int reg_int

3333: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3334: END IF;
3335:
3336: -- validating the fields legal_location_id and other asccociated columns
3337: UPDATE zx_registrations_int reg_int
3338: SET error_number = error_number + G_ES_REG_LGL_LOC_INVALID
3339: WHERE (( legal_location_id IS NOT NULL
3340: AND NOT EXISTS
3341: (

Line 3378: UPDATE zx_registrations_int reg_int

3374: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3375: END IF;
3376:
3377: -- value to id conversion for legal_location_id
3378: UPDATE zx_registrations_int reg_int
3379: SET legal_location_id = (
3380: SELECT legal_location_id
3381: FROM hr_locations
3382: WHERE legal_address_flag = 'Y'

Line 3408: UPDATE zx_registrations_int

3404: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3405: END IF;
3406:
3407: -- validating the field default_registration_flag
3408: UPDATE zx_registrations_int
3409: SET error_number = error_number + G_ES_REG_DEFREG_FLG_INVALID
3410: WHERE record_status = G_RS_IN_PROCESS
3411: AND request_id = G_REQUEST_ID
3412: AND default_registration_flag NOT IN ('Y','N');

Line 3420: UPDATE zx_registrations_int reg_int

3416: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3417: END IF;
3418:
3419: -- validating the fields tax_regime_name snd tax_regime_code
3420: UPDATE zx_registrations_int reg_int
3421: SET error_number = error_number + G_ES_REG_TAX_REG_INVALID
3422: WHERE (( tax_regime_code IS NOT NULL
3423: AND NOT EXISTS
3424: (

Line 3452: UPDATE zx_registrations_int reg_int

3448: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3449: END IF;
3450:
3451: -- value to id conversion for tax_regime_code
3452: UPDATE zx_registrations_int reg_int
3453: SET tax_regime_code = (
3454: SELECT tax_regime_name
3455: FROM zx_regimes_v
3456: WHERE has_sub_regime_flag <> 'Y'

Line 3472: UPDATE zx_registrations_int reg_int

3468: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3469: END IF;
3470:
3471: -- validating the fields tax and tax_name
3472: UPDATE zx_registrations_int reg_int
3473: SET error_number = error_number + G_ES_REG_TAX_PARAM_INVALID
3474: WHERE (( tax_code IS NOT NULL
3475: AND NOT EXISTS
3476: (

Line 3505: UPDATE zx_registrations_int reg_int

3501: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3502: END IF;
3503:
3504: -- value to id conversion for tax
3505: UPDATE zx_registrations_int reg_int
3506: SET tax_code = (
3507: SELECT tax
3508: FROM zx_mco_lv_taxes_v
3509: WHERE tax_regime_code = reg_int.tax_regime_code

Line 3526: UPDATE zx_registrations_int reg_int

3522: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3523: END IF;
3524:
3525: -- validating the fields tax_jurisdiction_code and tax_jurisdiction_name
3526: UPDATE zx_registrations_int reg_int
3527: SET error_number = error_number + G_ES_REG_TAX_JUR_INVALID
3528: WHERE ( tax_jurisdiction_code IS NOT NULL
3529: OR tax_jurisdiction_name IS NOT NULL
3530: )

Line 3543: UPDATE zx_registrations_int reg_int

3539: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3540: END IF;
3541:
3542: -- validating the fields TAX_JURISDICTION_CODE and tax_jurisdiction_name
3543: UPDATE zx_registrations_int reg_int
3544: SET error_number = error_number + G_ES_REG_TAX_JUR_INVALID
3545: WHERE (( tax_jurisdiction_code IS NOT NULL
3546: AND NOT EXISTS
3547: (

Line 3583: UPDATE zx_registrations_int reg_int

3579: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'RegValueToIdConversion',G_DEBUG_STATEMENT);
3580: END IF;
3581:
3582: -- value to id conversion for tax_jurisdiction_code
3583: UPDATE zx_registrations_int reg_int
3584: SET tax_jurisdiction_code = (
3585: SELECT tax_jurisdiction_code
3586: FROM zx_jurisdictions_vl
3587: WHERE tax_regime_code = reg_int.tax_regime_code

Line 3632: UPDATE zx_registrations_int reg_int

3628: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'DupTRNValidation','DupTRNValidation(+)');
3629: END IF;
3630:
3631: -- Store Party Info in prog_int_char1
3632: UPDATE zx_registrations_int reg_int
3633: SET prog_int_char1 =
3634: (SELECT /*+ FIRST_ROWS(1) */ intf_party_reference
3635: FROM zx_party_tax_profile_int ptp_int
3636: WHERE ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference

Line 3647: UPDATE zx_registrations_int reg_int

3643: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3644: END IF;
3645:
3646: -- Ignore Records having allow_dup_regn_num_flag enables at Tax Level.
3647: UPDATE zx_registrations_int reg_int
3648: SET prog_int_num1 = G_PROG_INT_VAL
3649: WHERE request_id = G_REQUEST_ID
3650: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3651: AND BITAND(reg_int.error_number,G_ES_REG_TAX_PARAM_INVALID) = 0

Line 3683: from zx_registrations_int reg_dup

3679: AND ptp_dup.intf_party_reference <> ptp_int.intf_party_reference
3680: AND ROWNUM = 1
3681: UNION ALL
3682: select /*+ FIRST_ROWS(1) */ 2
3683: from zx_registrations_int reg_dup
3684: where reg_dup.request_id = ptp_int.request_id
3685: AND reg_dup.record_status = ptp_int.record_status
3686: AND reg_dup.registration_number = ptp_int.registration_number
3687: AND reg_dup.intf_ptp_reference <> ptp_int.intf_ptp_reference

Line 3717: from zx_registrations_int reg_dup

3713: AND ptp_dup.ROWID <> ptp_int.ROWID
3714: AND (ptp_dup.registration_number = ptp_int.registration_number
3715: OR EXISTS (
3716: select /*+ FIRST_ROWS(1) */ 2
3717: from zx_registrations_int reg_dup
3718: where reg_dup.request_id = ptp_dup.request_id
3719: AND reg_dup.record_status = ptp_dup.record_status
3720: and reg_dup.registration_number =ptp_int.registration_number
3721: and reg_dup.intf_ptp_reference =ptp_dup.intf_ptp_reference

Line 3735: FROM zx_registrations_int reg_prnt

3731: AND (
3732: ptp_prnt.registration_number=ptp_dup.registration_number
3733: OR EXISTS (
3734: SELECT /*+ FIRST_ROWS(1) */ 1
3735: FROM zx_registrations_int reg_prnt
3736: WHERE reg_prnt.request_id = ptp_prnt.request_id
3737: AND reg_prnt.record_status = ptp_prnt.record_status
3738: AND reg_prnt.intf_ptp_reference =ptp_prnt.intf_ptp_reference
3739: AND reg_prnt.registration_number =

Line 3787: UPDATE zx_registrations_int reg_int

3783: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3784: END IF;
3785:
3786: -- TRN duplicate for Third Party and Third Party Site against Diff Party/Sites
3787: UPDATE zx_registrations_int reg_int
3788: SET error_number = error_number + G_ES_REG_REG_NUM_DUP
3789: WHERE request_id = G_REQUEST_ID
3790: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3791: AND prog_int_num1 <> G_PROG_INT_VAL

Line 3803: from zx_registrations_int reg_dup

3799: AND ptp_dup.intf_party_reference <> reg_int.prog_int_char1
3800: AND ROWNUM = 1
3801: UNION ALL
3802: select /*+ FIRST_ROWS(1) */ 2
3803: from zx_registrations_int reg_dup
3804: where reg_dup.request_id = reg_int.request_id
3805: AND reg_dup.record_status = reg_int.record_status
3806: and reg_dup.registration_number = reg_int.registration_number
3807: AND reg_dup.ROWID <> reg_int.ROWID

Line 3818: UPDATE zx_registrations_int reg_int

3814: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3815: END IF;
3816:
3817: -- TRN duplicate for Third Party Sites Siblings
3818: UPDATE zx_registrations_int reg_int
3819: SET error_number = error_number + G_ES_REG_REG_NUM_DUP
3820: WHERE request_id = G_REQUEST_ID
3821: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3822: AND prog_int_num1 <> G_PROG_INT_VAL

Line 3844: from zx_registrations_int reg_dup

3840: AND ptp_dup.intf_ptp_reference <> reg_int.intf_ptp_reference
3841: AND (ptp_dup.registration_number = reg_int.registration_number
3842: OR EXISTS (
3843: select /*+ FIRST_ROWS(1) */ 2
3844: from zx_registrations_int reg_dup
3845: where reg_dup.request_id = ptp_dup.request_id
3846: AND reg_dup.record_status = ptp_dup.record_status
3847: and reg_dup.registration_number =reg_int.registration_number
3848: and reg_dup.intf_ptp_reference =ptp_dup.intf_ptp_reference

Line 3864: FROM zx_registrations_int reg_prnt

3860: AND (
3861: ptp_prnt.registration_number=ptp_dup.registration_number
3862: OR EXISTS (
3863: SELECT /*+ FIRST_ROWS(1) */ 1
3864: FROM zx_registrations_int reg_prnt
3865: WHERE reg_prnt.request_id = ptp_prnt.request_id
3866: AND reg_prnt.record_status = ptp_prnt.record_status
3867: AND reg_prnt.intf_ptp_reference =ptp_prnt.intf_ptp_reference
3868: AND reg_prnt.registration_number =

Line 3882: UPDATE zx_registrations_int reg_int

3878: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3879: END IF;
3880:
3881: -- TRN duplicate against Prod Tables
3882: UPDATE zx_registrations_int reg_int
3883: SET error_number = error_number + G_ES_REG_REG_NUM_DUP
3884: WHERE request_id = G_REQUEST_ID
3885: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3886: AND prog_int_num1 <> G_PROG_INT_VAL

Line 3901: UPDATE zx_registrations_int reg_int

3897: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3898: END IF;
3899:
3900: -- TRN duplicate against Prod Tables
3901: UPDATE zx_registrations_int reg_int
3902: SET error_number = error_number + G_ES_REG_REG_NUM_DUP
3903: WHERE request_id = G_REQUEST_ID
3904: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3905: AND prog_int_num1 <> G_PROG_INT_VAL

Line 3931: UPDATE zx_registrations_int reg_int

3927: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
3928: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3929: END IF;
3930:
3931: UPDATE zx_registrations_int reg_int
3932: SET record_status = G_RS_ERROR
3933: WHERE request_id = G_REQUEST_ID
3934: AND record_status = G_RS_VALID
3935: AND error_number > 0;

Line 3947: FROM zx_registrations_int reg_int

3943: SET record_status = NULL
3944: WHERE request_id = G_REQUEST_ID
3945: AND record_status = G_RS_VALID
3946: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
3947: FROM zx_registrations_int reg_int
3948: WHERE request_id = G_REQUEST_ID
3949: AND record_status = G_RS_ERROR
3950: AND reg_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
3951:

Line 3986: FROM zx_registrations_int reg

3982:
3983: UPDATE zx_party_tax_profile_int ptp_int
3984: SET registration_number = (
3985: SELECT registration_number
3986: FROM zx_registrations_int reg
3987: WHERE reg.request_id = G_REQUEST_ID
3988: AND NVL(reg.record_status,G_RS_ERROR) = G_RS_VALID
3989: AND reg.intf_ptp_reference = nvl(ptp_int.intf_ptp_reference,G_MISS_CHAR)
3990: AND NVL(reg.default_registration_flag,'N') = 'Y'

Line 3997: FROM zx_registrations_int reg

3993: WHERE request_id = G_REQUEST_ID
3994: AND record_status = G_RS_IN_PROCESS
3995: AND EXISTS (
3996: SELECT 1
3997: FROM zx_registrations_int reg
3998: WHERE reg.request_id = G_REQUEST_ID
3999: AND NVL(reg.record_status,G_RS_ERROR) = G_RS_VALID
4000: AND reg.intf_ptp_reference = nvl(ptp_int.intf_ptp_reference,G_MISS_CHAR)
4001: AND NVL(reg.default_registration_flag,'N') = 'Y'

Line 5977: UPDATE zx_registrations_int reg_int

5973: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
5974: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PopulateProductionTables',G_DEBUG_STATEMENT);
5975: END IF;
5976:
5977: UPDATE zx_registrations_int reg_int
5978: SET party_tax_profile_id =
5979: (
5980: SELECT party_tax_profile_id
5981: FROM zx_party_tax_profile_int ptp_int

Line 6292: FROM zx_registrations_int

6288: 1, --OBJECT_VERSION_NUMBER
6289: NULL, --ROUNDING_LEVEL_CODE
6290: NULL, --ACCOUNT_TYPE_CODE
6291: NULL --MERGED_TO_REGISTRATION_ID
6292: FROM zx_registrations_int
6293: WHERE record_status = G_RS_VALID
6294: AND request_id = G_REQUEST_ID
6295: AND error_number = 0);
6296:

Line 6500: FROM zx_registrations_int reg_int, zx_party_tax_profile_int ptp_int

6496: AND record_status = G_RS_ERROR;
6497:
6498: CURSOR c_reg_errors IS
6499: SELECT ptp_int.party_type_code, ptp_int.party_id, reg_int.*
6500: FROM zx_registrations_int reg_int, zx_party_tax_profile_int ptp_int
6501: WHERE reg_int.request_id = G_REQUEST_ID
6502: AND reg_int.record_status = G_RS_ERROR
6503: AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
6504: AND ptp_int.request_id = reg_int.request_id;

Line 8187: FROM zx_registrations_int

8183: );
8184:
8185: SELECT NVL(SUM(DECODE(record_status,G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
8186: INTO l_count_exception_records, l_count_success_records
8187: FROM zx_registrations_int
8188: WHERE request_id = G_REQUEST_ID;
8189: l_message_line1 := RPAD(l_tot_records_msg||' '||l_count_success_records
8190: ,l_msg_header_unit_length,' ');
8191: l_message_line2 := RPAD(l_exe_records_msg||' '||l_count_exception_records