[Home] [Help]
9: ------------The procedures declared below were created as part of bug fix 3722296------
10:
11: PROCEDURE ZX_CREATE_REG(
12: p_reg_info varchar2,
13: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type
14: );
15:
16: PROCEDURE ZX_CREATE_REGISTRATIONS(
17: p_hr_org_reg_info varchar2,
17: p_hr_org_reg_info varchar2,
18: p_hr_loc_reg_info varchar2,
19: p_ar_tax_reg_info varchar2,
20: p_fin_vat_reg_info varchar2,
21: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
22: p_level number
23: );
24:
25:
29: PROCEDURE ZX_CREATE_REP_TYPE_ASSOC
30: (
31: p_reg_rec register_num_tab,
32: p_level NUMBER,
33: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
34: p_hr_rep_type_info_lat varchar2,
35: p_hr_rep_type_info_kor varchar2,
36: p_hr_rep_type_info_eur_grc varchar2,
37: p_ar_tax_reg_info varchar2,
38: p_fin_vat_reg_info varchar2
39: );
40:
41: --PROCEDURE REG_REP_DRIVER_PROC
42: --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
43:
44: --PROCEDURE REG_REP_DRIVER_PROC_OU
45: --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
46:
41: --PROCEDURE REG_REP_DRIVER_PROC
42: --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
43:
44: --PROCEDURE REG_REP_DRIVER_PROC_OU
45: --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
46:
47: -----------------------------------------------------------------------------------------
48:
49:
74: |=========================================================================*/
75:
76: PROCEDURE ZX_CREATE_REG(
77: p_reg_info varchar2,
78: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type
79: )
80: IS
81: BEGIN
82:
156: FND_GLOBAL.CONC_PROGRAM_ID, --PROGRAM_ID
157: FND_GLOBAL.CONC_LOGIN_ID , --PROGRAM_LOGIN_ID
158: 1
159:
160: FROM ZX_PARTY_TAX_PROFILE
161:
162: WHERE PARTY_TAX_PROFILE_ID=p_ptp_id
163: AND NOT EXISTS
164: (SELECT 1 FROM ZX_REGISTRATIONS WHERE REGISTRATION_NUMBER=p_reg_info
201: p_hr_org_reg_info varchar2,
202: p_hr_loc_reg_info varchar2,
203: p_ar_tax_reg_info varchar2,
204: p_fin_vat_reg_info varchar2,
205: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
206: p_level number
207: )
208: IS
209: BEGIN
268: PROCEDURE ZX_CREATE_REP_TYPE_ASSOC
269: (
270: p_reg_rec register_num_tab,
271: p_level NUMBER,
272: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
273: p_hr_rep_type_info_lat varchar2,
274: p_hr_rep_type_info_kor varchar2,
275: p_hr_rep_type_info_eur_grc varchar2,
276: p_ar_tax_reg_info varchar2,
336: | Bugfix: 3722296 |
337: |=========================================================================*/
338:
339:
340: PROCEDURE REG_REP_DRIVER_PROC(p_party_type_code zx_party_tax_profile.party_type_code%type)
341: IS
342:
343: ---------------------Local variable declarations-----------------
344: l_first_reg_val varchar2(160);
383: ,Hr_Organization_Information HrOuInfo
384: ,Financials_System_Params_All Fso
385: ,Ap_System_Parameters_All ASP
386: ,Ar_System_Parameters_All ARP
387: ,Zx_party_tax_profile ptp
388: WHERE XEP.legal_entity_id = HrOuInfo.ORG_INFORMATION2
389: AND XEP.establishment_id = xa.subject_id
390: and atype.association_type_id = xa.association_type_id
391: and atype.object_type_id = ctype_obj.object_type_id
487: | |
488: |=========================================================================*/
489:
490:
491: PROCEDURE REG_REP_DRIVER_PROC_OU(p_party_type_code zx_party_tax_profile.party_type_code%type)
492: IS
493: ---------------------Local variable declarations-----------------
494: l_first_reg_val varchar2(160);
495: l_position_first_reg NUMBER;
524: HR_LOCATIONS_ALL HrLoc,
525: FINANCIALS_SYSTEM_PARAMS_ALL FinSysParam,
526: AR_SYSTEM_PARAMETERS_ALL ArSysParam,
527: HR_ALL_ORGANIZATION_UNITS HrOrgUnits,
528: ZX_PARTY_TAX_PROFILE PTP
529: WHERE
530: nvl(ptp.Party_Type_code,p_party_type_code) = p_party_type_code
531: and ptp.party_id (+) = HrOrgInfo.organization_id
532: and decode(l_multi_org_flag,'N',l_org_id,HrOrgUnits.organization_id) =
628:
629: arp_util_tax.debug(' FIRST_PARTY_EXTRACT (+) ' );
630:
631: INSERT into
632: ZX_PARTY_TAX_PROFILE(
633: Party_Tax_Profile_Id,
634: Party_Id,
635: Party_Type_Code,
636: Customer_Flag,
656: Last_Update_Date,
657: Last_Update_Login,
658: OBJECT_VERSION_NUMBER)
659: (SELECT
660: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
661: ,XEP.Party_ID -- Party_Id
662: ,'FIRST_PARTY' -- Party Type
663: ,'N' -- Customer_Flag
664: ,'Y' -- First Party LE Flag
691: WHERE
692: HrOU.location_id = HrLoc.location_id (+)
693: AND decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id) = decode(l_multi_org_flag,'N',l_org_id,Fso.org_id(+))
694: AND decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id(+)) = XEP.legal_entity_id
695: AND not exists ( select 1 from zx_party_tax_profile
696: WHERE party_id = XEP.Party_ID and Party_Type_Code = 'FIRST_PARTY'));
697:
698: arp_util_tax.debug(' FIRST_PARTY_EXTRACT (-) ' );
699:
738: arp_util_tax.debug(' LEGAL_ESTABLISHMENT (+) ' );
739:
740: -- Following insert creates the associated establishments to the Operating units.
741:
742: INSERT INTO ZX_PARTY_TAX_PROFILE
743: (
744: Party_Tax_Profile_Id,
745: Party_Id,
746: Rep_Registration_Number,
769: Last_Update_Login,
770: OBJECT_VERSION_NUMBER
771: )
772: SELECT
773: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
774: ,XEP.Party_Id --Party_Id
775: --Bug 4361933
776: ,nvl((SELECT HrOrgInfo.ORG_INFORMATION2 FROM Hr_Organization_Information HrOrgInfo
777: WHERE HrOrgInfo.Org_Information_Context = 'Legal Entity Accounting'
819: ,Hr_Organization_Information HrOuInfo
820: ,Financials_System_Params_All Fso
821: ,Ap_System_Parameters_All ASP
822: ,Ar_System_Parameters_All ARP
823: ,Zx_party_tax_profile ptp
824: WHERE XEP.legal_entity_id = HrOuInfo.ORG_INFORMATION2
825: AND XEP.establishment_id = xa.subject_id
826: and atype.association_type_id = xa.association_type_id
827: and atype.object_type_id = ctype_obj.object_type_id
839: = decode(l_multi_org_flag,'N',l_org_id,ASP.org_id(+))
840: AND HrOU.location_id = HrLoc.location_id (+)
841: AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id)
842: = decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+))
843: AND NOT EXISTS (select 1 from zx_party_tax_profile where party_id = xep.party_id
844: and party_type_code = 'LEGAL_ESTABLISHMENT');
845:
846:
847: -- Following insert creates the remaining establishments.
845:
846:
847: -- Following insert creates the remaining establishments.
848:
849: INSERT INTO ZX_PARTY_TAX_PROFILE
850: (
851: Party_Tax_Profile_Id,
852: Party_Id,
853: Rep_Registration_Number,
876: Last_Update_Login,
877: OBJECT_VERSION_NUMBER
878: )
879: SELECT
880: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
881: ,XEP.Party_Id --Party_Id
882: --Bug 4361933
883: ,nvl(hrorginfo.org_information2,
884: nvl(decode(hrloc.GLOBAL_ATTRIBUTE_CATEGORY,
936: AND HrOU.location_id = HrLoc.location_id (+)
937: AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id) =
938: decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+))
939: AND NOT EXISTS
940: (select 1 from zx_party_tax_profile where party_id = xep.party_id
941: and party_type_code = 'LEGAL_ESTABLISHMENT');
942:
943:
944: -- Remove logic to create Associated Establishments' Business Organizations and locations
1011: ,SYSDATE
1012: ,FND_GLOBAL.CONC_LOGIN_ID
1013: ,1
1014: FROM
1015: zx_party_tax_profile PTP,
1016: Hr_Locations_All HrLoc,
1017: Hr_All_Organization_Units hrou,
1018: Hr_Organization_Information hroi
1019: WHERE
1113: FROM
1114: jl_br_company_infos jl
1115: ,gl_ledger_le_v gl
1116: ,xle_etb_profiles etb
1117: ,zx_party_tax_profile ptp
1118: WHERE
1119: jl.INACTIVE_DATE is null
1120: and jl.set_of_books_id = gl.ledger_id
1121: and etb.legal_entity_id = gl.legal_entity_id
1126: AND Registration_Type_Code = 'CNPJ'
1127: AND tax_regime_code = 'BR-IPI' );
1128:
1129: -- update rep_registation_number
1130: Update zx_party_tax_profile ptp
1131: Set rep_registration_number =
1132: (Select registration_number
1133: from zx_registrations reg
1134: where reg.party_tax_profile_id = ptp.party_tax_profile_id
1204: FROM
1205: jl_br_company_infos jl
1206: ,gl_ledger_le_v gl
1207: ,xle_etb_profiles etb
1208: ,zx_party_tax_profile ptp
1209: WHERE
1210: jl.INACTIVE_DATE is null
1211: and jl.set_of_books_id = gl.ledger_id
1212: and etb.legal_entity_id = gl.legal_entity_id
1279: FROM
1280: jl_br_company_infos jl
1281: ,gl_ledger_le_v gl
1282: ,xle_etb_profiles etb
1283: ,zx_party_tax_profile ptp
1284: WHERE
1285: jl.INACTIVE_DATE is null
1286: and jl.set_of_books_id = gl.ledger_id
1287: and etb.legal_entity_id = gl.legal_entity_id
1335: ---Bug 4054883
1336:
1337: /*CURSOR C_SUPPLIER_TYPE IS
1338: SELECT POV.VENDOR_ID
1339: FROM ap_suppliers POV , ZX_PARTY_TAX_PROFILE PTP
1340: WHERE POV.VENDOR_ID = PTP.PARTY_ID
1341: AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
1342: AND VENDOR_TYPE_LOOKUP_CODE is not null
1343: AND VENDOR_TYPE_LOOKUP_CODE <> 'TAX AUTHORITY';*/
1351: arp_util_tax.debug(' SUPPLIER_EXTRACT(+) ' );
1352:
1353: /*
1354: The logic to create PTPs for suppliers is to loop through po_vendors based on
1355: po_vendors.party_id. TRN will be stored in zx_party_tax_profile it self. In
1356: this case no records will be created in zx_registrations.
1357: */
1358: /*
1359: Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1359: Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1360: Separate scripts have been written to deal with this feature
1361: */
1362: INSERT INTO
1363: ZX_PARTY_TAX_PROFILE(
1364: Party_Tax_Profile_Id
1365: ,Party_Id
1366: ,Rep_Registration_Number
1367: ,Party_Type_code
1389: ,Last_Update_Date
1390: ,Last_Update_Login
1391: ,OBJECT_VERSION_NUMBER)
1392: (SELECT
1393: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1394: ,PARTY_ID -- Party ID
1395: ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1396: 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1397: 'JL.CL.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1423: ,FND_GLOBAL.CONC_LOGIN_ID -- Who Columns
1424: , 1
1425: FROM ap_suppliers PV
1426: WHERE VENDOR_ID = nvl(p_party_ID,VENDOR_ID)
1427: AND not exists (select 1 from zx_party_tax_profile
1428: where party_id = PV.party_id and Party_Type_Code = 'THIRD_PARTY'));
1429:
1430:
1431: /*
1495: ,SYSDATE
1496: ,FND_GLOBAL.CONC_LOGIN_ID
1497: ,1
1498: FROM ap_suppliers PV,
1499: zx_party_tax_profile PTP
1500: WHERE
1501: PV.Party_id = PTP.Party_ID
1502: AND PTP.Party_Type_code = 'SUPPLIER'
1503: and not exists (select 1 from zx_registrations
1666: INSERT ALL
1667: WHEN COUNT = 1
1668: THEN
1669: INTO
1670: ZX_PARTY_TAX_PROFILE(
1671: Party_Tax_Profile_Id
1672: ,Party_Id
1673: ,Rep_Registration_Number
1674: ,Party_Type_code
1697: ,Last_Update_Login
1698: ,Object_Version_Number)
1699:
1700: VALUES(
1701: ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
1702: PARTY_SITE_ID,
1703: VAT_REGISTRATION_NUM
1704: ,'THIRD_PARTY_SITE' -- Party Type
1705: ,'N' -- Customer_Flag
1743: WHERE
1744: PVS.VENDOR_SITE_ID = nvl(p_party_id,PVS.VENDOR_SITE_ID) --this condition is for the sync process
1745: AND PV.VENDOR_ID = PVS.VENDOR_ID
1746: AND NOT EXISTS
1747: ( select 1 from zx_party_tax_profile
1748: WHERE party_id = pvs.PARTY_SITE_ID and Party_Type_Code = 'THIRD_PARTY_SITE'));
1749:
1750:
1751: /*
1845:
1846: FROM
1847: ap_suppliers pv,
1848: ap_supplier_sites_all pvs,
1849: zx_party_tax_profile PTP,
1850: (select party_site_id,
1851: COUNT
1852: (DISTINCT(PARTY_SITE_ID||AMOUNT_INCLUDES_TAX_FLAG
1853: ||AP_TAX_ROUNDING_RULE||AUTO_TAX_CALC_FLAG||OFFSET_TAX_FLAG||VAT_CODE||VAT_REGISTRATION_NUM) ) Counter
1865: */
1866:
1867:
1868: /* INSERT INTO
1869: ZX_PARTY_TAX_PROFILE(
1870: Party_Tax_Profile_Id
1871: ,Party_Id
1872: ,Party_Type_code
1873: ,Customer_Flag
1894: ,Last_Update_Date
1895: ,Last_Update_Login
1896: ,OBJECT_VERSION_NUMBER)
1897: (SELECT
1898: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1899: ,pvs.VENDOR_SITE_ID -- Party ID
1900: ,'SUPPLIER_SITE' -- Party Type
1901: ,'N' -- Customer_Flag
1902: ,'N' -- First Party Flag
1926: FROM ap_supplier_sites_all Pvs,
1927: ap_suppliers Pv
1928: WHERE pvs.Vendor_Site_Id = nvl(P_Party_Id,pvs.Vendor_site_Id)
1929: AND pvs.vendor_id = pv.vendor_id
1930: AND not exists ( select 1 from zx_party_tax_profile
1931: WHERE party_id = pvs.VENDOR_SITE_ID and Party_Type_Code = 'SUPPLIER_SITE'));
1932:
1933:
1934: INSERT INTO
1991: ,FND_GLOBAL.CONC_LOGIN_ID
1992: ,1
1993: FROM ap_supplier_sites_all PVS,
1994: ap_suppliers PV,
1995: zx_party_tax_profile PTP
1996: WHERE
1997: PVS.vendor_site_id = PTP.Party_ID
1998: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
1999: AND PVS.Vendor_ID = PV.Vendor_ID
2085: ,FND_GLOBAL.CONC_LOGIN_ID
2086: ,1
2087: FROM ap_supplier_sites_all PVS,
2088: ap_suppliers PV,
2089: zx_party_tax_profile PTP
2090: WHERE
2091: PVS.vendor_site_id = PTP.Party_ID
2092: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2093: AND PVS.Vendor_ID = PV.Vendor_ID
2159: ,FND_GLOBAL.CONC_LOGIN_ID
2160: ,1
2161: FROM ap_supplier_sites_all PVS,
2162: ap_suppliers PV,
2163: zx_party_tax_profile PTP
2164: WHERE
2165: PVS.vendor_site_id = PTP.Party_ID
2166: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2167: AND PVS.Vendor_ID = PV.Vendor_ID
2233: ,FND_GLOBAL.CONC_LOGIN_ID
2234: ,1
2235: FROM ap_supplier_sites_all PVS,
2236: ap_suppliers PV,
2237: zx_party_tax_profile PTP
2238: WHERE
2239: PVS.vendor_site_id = PTP.Party_ID
2240: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2241: AND PVS.Vendor_ID = PV.Vendor_ID
2285: IF L_MULTI_ORG_FLAG = 'N'
2286: --Bug Fix 4460944
2287: THEN
2288: INSERT INTO
2289: ZX_PARTY_TAX_PROFILE(
2290: Party_Tax_Profile_Id,
2291: Party_Id,
2292: Party_Type_code,
2293: Customer_Flag,
2314: Last_Update_Date,
2315: Last_Update_Login,
2316: OBJECT_VERSION_NUMBER)
2317: (SELECT
2318: ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2319: ,L_ORG_ID -- Party_Id
2320: ,'OU' -- Party_Type_code
2321: ,'N' -- Customer Flag
2322: ,'N' -- First_Party_Le_Flag
2342: SYSDATE, -- Last_Update_Date
2343: FND_GLOBAL.CONC_LOGIN_ID, -- Last_Update_Login
2344: 1
2345: FROM DUAL
2346: WHERE not exists ( select 1 from zx_party_tax_profile
2347: WHERE party_id = l_org_id and Party_Type_Code = 'OU'));
2348:
2349: ELSE
2350:
2348:
2349: ELSE
2350:
2351: INSERT INTO
2352: ZX_PARTY_TAX_PROFILE(
2353: Party_Tax_Profile_Id,
2354: Party_Id,
2355: Party_Type_code,
2356: Customer_Flag,
2377: Last_Update_Date,
2378: Last_Update_Login,
2379: OBJECT_VERSION_NUMBER)
2380: (SELECT
2381: ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2382: ,Organization_id -- Party_Id
2383: ,'OU' -- Party_Type_code
2384: ,'N' -- Customer Flag
2385: ,'N' -- First_Party_Le_Flag
2407: 1
2408: FROM HR_OPERATING_UNITS
2409: WHERE HR_OPERATING_UNITS.ORGANIZATION_ID =
2410: nvl(p_party_ID,HR_OPERATING_UNITS.ORGANIZATION_ID) and
2411: not exists ( select 1 from zx_party_tax_profile
2412: WHERE party_id = organization_id and Party_Type_Code = 'OU'));
2413:
2414: ------Bugfix 4308003----------
2415: REG_REP_DRIVER_PROC_OU('OU');
2819: PROGRAM_ID)
2820:
2821: Select
2822: HZ_CODE_ASSIGNMENTS_S.nextval,
2823: 'ZX_PARTY_TAX_PROFILE',
2824: PTP.PARTY_TAX_PROFILE_ID party_tax_profile_id,
2825: 'VENDOR TYPE',
2826: POV.VENDOR_TYPE_LOOKUP_CODE fiscal_classification_code,
2827: 'N',
2847: NULL,
2848: fnd_global.PROG_APPL_ID,
2849: fnd_global.CONC_PROGRAM_ID
2850: FROM ap_suppliers POV ,
2851: ZX_PARTY_TAX_PROFILE PTP
2852: WHERE POV.PARTY_ID = PTP.PARTY_ID
2853: AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
2854: AND POV.VENDOR_TYPE_LOOKUP_CODE is not null;
2855: */
2901:
2902:
2903: arp_util_tax.debug(' Party_Assoc_Extract .. (+) ' );
2904:
2905: IF p_party_source in ('ZX_PARTY_TAX_PROFILE' , 'PO_VENDOR' ,'PO_VENDOR_SITES') THEN
2906: l_table_owner := 'ZX_PARTY_TAX_PROFILE';
2907: ELSIF p_party_source = 'HR_ORGANIZATIONS' or p_party_source = 'AP_REPORTING_ENTITIES' THEN
2908: l_table_owner := 'HZ_PARTIES';
2909: END IF;
2902:
2903: arp_util_tax.debug(' Party_Assoc_Extract .. (+) ' );
2904:
2905: IF p_party_source in ('ZX_PARTY_TAX_PROFILE' , 'PO_VENDOR' ,'PO_VENDOR_SITES') THEN
2906: l_table_owner := 'ZX_PARTY_TAX_PROFILE';
2907: ELSIF p_party_source = 'HR_ORGANIZATIONS' or p_party_source = 'AP_REPORTING_ENTITIES' THEN
2908: l_table_owner := 'HZ_PARTIES';
2909: END IF;
2910: