[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);
377: ,Hr_Organization_Information HrOrgInfo
378: ,Financials_System_Params_All Fso
379: ,Ap_System_Parameters_All ASP
380: ,Ar_System_Parameters_All ARP
381: ,Zx_party_tax_profile ptp
382: WHERE
383: XEP.legal_entity_id = HrOU.organization_id (+) --bug 4519314
384: AND XEP.party_id = PTP.party_Id
385: AND PTP.party_type_code = 'LEGAL_ESTABLISHMENT'
476: | |
477: |=========================================================================*/
478:
479:
480: PROCEDURE REG_REP_DRIVER_PROC_OU(p_party_type_code zx_party_tax_profile.party_type_code%type)
481: IS
482: ---------------------Local variable declarations-----------------
483: l_first_reg_val varchar2(160);
484: l_position_first_reg NUMBER;
513: HR_LOCATIONS_ALL HrLoc,
514: FINANCIALS_SYSTEM_PARAMS_ALL FinSysParam,
515: AR_SYSTEM_PARAMETERS_ALL ArSysParam,
516: HR_ALL_ORGANIZATION_UNITS HrOrgUnits,
517: ZX_PARTY_TAX_PROFILE PTP
518: WHERE
519: nvl(ptp.Party_Type_code,p_party_type_code) = p_party_type_code
520: and ptp.party_id (+) = HrOrgInfo.organization_id
521: and decode(l_multi_org_flag,'N',l_org_id,HrOrgUnits.organization_id) =
617:
618: arp_util_tax.debug(' FIRST_PARTY_EXTRACT (+) ' );
619:
620: INSERT into
621: ZX_PARTY_TAX_PROFILE(
622: Party_Tax_Profile_Id,
623: Party_Id,
624: Party_Type_Code,
625: Customer_Flag,
645: Last_Update_Date,
646: Last_Update_Login,
647: OBJECT_VERSION_NUMBER)
648: (SELECT
649: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
650: ,XEP.Party_ID -- Party_Id
651: ,'FIRST_PARTY' -- Party Type
652: ,'N' -- Customer_Flag
653: ,'Y' -- First Party LE Flag
680: WHERE
681: HrOU.location_id = HrLoc.location_id (+)
682: 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(+))
683: AND decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id(+)) = XEP.legal_entity_id
684: AND not exists ( select 1 from zx_party_tax_profile
685: WHERE party_id = XEP.Party_ID and Party_Type_Code = 'FIRST_PARTY'));
686:
687: arp_util_tax.debug(' FIRST_PARTY_EXTRACT (-) ' );
688:
725: BEGIN
726:
727: arp_util_tax.debug(' LEGAL_ESTABLISHMENT (+) ' );
728:
729: INSERT INTO ZX_PARTY_TAX_PROFILE
730: (
731: Party_Tax_Profile_Id,
732: Party_Id,
733: Rep_Registration_Number,
756: Last_Update_Login,
757: OBJECT_VERSION_NUMBER
758: )
759: SELECT
760: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
761: ,XEP.Party_Id --Party_Id
762: --Bug 4361933
763: ,nvl(hrorginfo.org_information2,
764: nvl(decode(hrloc.GLOBAL_ATTRIBUTE_CATEGORY,
816: AND HrOU.location_id = HrLoc.location_id (+)
817: AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id) =
818: decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+))
819: AND NOT EXISTS
820: (select 1 from zx_party_tax_profile where party_id = xep.party_id
821: and party_type_code = 'LEGAL_ESTABLISHMENT');
822:
823:
824: -- Remove logic to create Associated Establishments' Business Organizations and locations
891: ,SYSDATE
892: ,FND_GLOBAL.CONC_LOGIN_ID
893: ,1
894: FROM
895: zx_party_tax_profile PTP,
896: Hr_Locations_All HrLoc,
897: Hr_All_Organization_Units hrou,
898: Hr_Organization_Information hroi
899: WHERE
993: FROM
994: jl_br_company_infos jl
995: ,gl_ledger_le_v gl
996: ,xle_etb_profiles etb
997: ,zx_party_tax_profile ptp
998: WHERE
999: jl.INACTIVE_DATE is null
1000: and jl.set_of_books_id = gl.ledger_id
1001: and etb.legal_entity_id = gl.legal_entity_id
1006: AND Registration_Type_Code = 'CNPJ'
1007: AND tax_regime_code = 'BR-IPI' );
1008:
1009: -- update rep_registation_number
1010: Update zx_party_tax_profile ptp
1011: Set rep_registration_number =
1012: (Select registration_number
1013: from zx_registrations reg
1014: where reg.party_tax_profile_id = ptp.party_tax_profile_id
1084: FROM
1085: jl_br_company_infos jl
1086: ,gl_ledger_le_v gl
1087: ,xle_etb_profiles etb
1088: ,zx_party_tax_profile ptp
1089: WHERE
1090: jl.INACTIVE_DATE is null
1091: and jl.set_of_books_id = gl.ledger_id
1092: and etb.legal_entity_id = gl.legal_entity_id
1159: FROM
1160: jl_br_company_infos jl
1161: ,gl_ledger_le_v gl
1162: ,xle_etb_profiles etb
1163: ,zx_party_tax_profile ptp
1164: WHERE
1165: jl.INACTIVE_DATE is null
1166: and jl.set_of_books_id = gl.ledger_id
1167: and etb.legal_entity_id = gl.legal_entity_id
1215: ---Bug 4054883
1216:
1217: /*CURSOR C_SUPPLIER_TYPE IS
1218: SELECT POV.VENDOR_ID
1219: FROM ap_suppliers POV , ZX_PARTY_TAX_PROFILE PTP
1220: WHERE POV.VENDOR_ID = PTP.PARTY_ID
1221: AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
1222: AND VENDOR_TYPE_LOOKUP_CODE is not null
1223: AND VENDOR_TYPE_LOOKUP_CODE <> 'TAX AUTHORITY';*/
1231: arp_util_tax.debug(' SUPPLIER_EXTRACT(+) ' );
1232:
1233: /*
1234: The logic to create PTPs for suppliers is to loop through po_vendors based on
1235: po_vendors.party_id. TRN will be stored in zx_party_tax_profile it self. In
1236: this case no records will be created in zx_registrations.
1237: */
1238: /*
1239: Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1239: Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1240: Separate scripts have been written to deal with this feature
1241: */
1242: INSERT INTO
1243: ZX_PARTY_TAX_PROFILE(
1244: Party_Tax_Profile_Id
1245: ,Party_Id
1246: ,Rep_Registration_Number
1247: ,Party_Type_code
1269: ,Last_Update_Date
1270: ,Last_Update_Login
1271: ,OBJECT_VERSION_NUMBER)
1272: (SELECT
1273: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1274: ,PARTY_ID -- Party ID
1275: ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1276: 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1277: 'JL.CL.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1303: ,FND_GLOBAL.CONC_LOGIN_ID -- Who Columns
1304: , 1
1305: FROM ap_suppliers PV
1306: WHERE VENDOR_ID = nvl(p_party_ID,VENDOR_ID)
1307: AND not exists (select 1 from zx_party_tax_profile
1308: where party_id = PV.party_id and Party_Type_Code = 'THIRD_PARTY'));
1309:
1310:
1311: /*
1375: ,SYSDATE
1376: ,FND_GLOBAL.CONC_LOGIN_ID
1377: ,1
1378: FROM ap_suppliers PV,
1379: zx_party_tax_profile PTP
1380: WHERE
1381: PV.Party_id = PTP.Party_ID
1382: AND PTP.Party_Type_code = 'SUPPLIER'
1383: and not exists (select 1 from zx_registrations
1546: INSERT ALL
1547: WHEN COUNT = 1
1548: THEN
1549: INTO
1550: ZX_PARTY_TAX_PROFILE(
1551: Party_Tax_Profile_Id
1552: ,Party_Id
1553: ,Rep_Registration_Number
1554: ,Party_Type_code
1577: ,Last_Update_Login
1578: ,Object_Version_Number)
1579:
1580: VALUES(
1581: ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
1582: PARTY_SITE_ID,
1583: VAT_REGISTRATION_NUM
1584: ,'THIRD_PARTY_SITE' -- Party Type
1585: ,'N' -- Customer_Flag
1623: WHERE
1624: PVS.VENDOR_SITE_ID = nvl(p_party_id,PVS.VENDOR_SITE_ID) --this condition is for the sync process
1625: AND PV.VENDOR_ID = PVS.VENDOR_ID
1626: AND NOT EXISTS
1627: ( select 1 from zx_party_tax_profile
1628: WHERE party_id = pvs.PARTY_SITE_ID and Party_Type_Code = 'THIRD_PARTY_SITE'));
1629:
1630:
1631: /*
1725:
1726: FROM
1727: ap_suppliers pv,
1728: ap_supplier_sites_all pvs,
1729: zx_party_tax_profile PTP,
1730: (select party_site_id,
1731: COUNT
1732: (DISTINCT(PARTY_SITE_ID||AMOUNT_INCLUDES_TAX_FLAG
1733: ||AP_TAX_ROUNDING_RULE||AUTO_TAX_CALC_FLAG||OFFSET_TAX_FLAG||VAT_CODE||VAT_REGISTRATION_NUM) ) Counter
1745: */
1746:
1747:
1748: /* INSERT INTO
1749: ZX_PARTY_TAX_PROFILE(
1750: Party_Tax_Profile_Id
1751: ,Party_Id
1752: ,Party_Type_code
1753: ,Customer_Flag
1774: ,Last_Update_Date
1775: ,Last_Update_Login
1776: ,OBJECT_VERSION_NUMBER)
1777: (SELECT
1778: ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1779: ,pvs.VENDOR_SITE_ID -- Party ID
1780: ,'SUPPLIER_SITE' -- Party Type
1781: ,'N' -- Customer_Flag
1782: ,'N' -- First Party Flag
1806: FROM ap_supplier_sites_all Pvs,
1807: ap_suppliers Pv
1808: WHERE pvs.Vendor_Site_Id = nvl(P_Party_Id,pvs.Vendor_site_Id)
1809: AND pvs.vendor_id = pv.vendor_id
1810: AND not exists ( select 1 from zx_party_tax_profile
1811: WHERE party_id = pvs.VENDOR_SITE_ID and Party_Type_Code = 'SUPPLIER_SITE'));
1812:
1813:
1814: INSERT INTO
1871: ,FND_GLOBAL.CONC_LOGIN_ID
1872: ,1
1873: FROM ap_supplier_sites_all PVS,
1874: ap_suppliers PV,
1875: zx_party_tax_profile PTP
1876: WHERE
1877: PVS.vendor_site_id = PTP.Party_ID
1878: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
1879: AND PVS.Vendor_ID = PV.Vendor_ID
1965: ,FND_GLOBAL.CONC_LOGIN_ID
1966: ,1
1967: FROM ap_supplier_sites_all PVS,
1968: ap_suppliers PV,
1969: zx_party_tax_profile PTP
1970: WHERE
1971: PVS.vendor_site_id = PTP.Party_ID
1972: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
1973: AND PVS.Vendor_ID = PV.Vendor_ID
2039: ,FND_GLOBAL.CONC_LOGIN_ID
2040: ,1
2041: FROM ap_supplier_sites_all PVS,
2042: ap_suppliers PV,
2043: zx_party_tax_profile PTP
2044: WHERE
2045: PVS.vendor_site_id = PTP.Party_ID
2046: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2047: AND PVS.Vendor_ID = PV.Vendor_ID
2113: ,FND_GLOBAL.CONC_LOGIN_ID
2114: ,1
2115: FROM ap_supplier_sites_all PVS,
2116: ap_suppliers PV,
2117: zx_party_tax_profile PTP
2118: WHERE
2119: PVS.vendor_site_id = PTP.Party_ID
2120: AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2121: AND PVS.Vendor_ID = PV.Vendor_ID
2165: IF L_MULTI_ORG_FLAG = 'N'
2166: --Bug Fix 4460944
2167: THEN
2168: INSERT INTO
2169: ZX_PARTY_TAX_PROFILE(
2170: Party_Tax_Profile_Id,
2171: Party_Id,
2172: Party_Type_code,
2173: Customer_Flag,
2194: Last_Update_Date,
2195: Last_Update_Login,
2196: OBJECT_VERSION_NUMBER)
2197: (SELECT
2198: ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2199: ,L_ORG_ID -- Party_Id
2200: ,'OU' -- Party_Type_code
2201: ,'N' -- Customer Flag
2202: ,'N' -- First_Party_Le_Flag
2222: SYSDATE, -- Last_Update_Date
2223: FND_GLOBAL.CONC_LOGIN_ID, -- Last_Update_Login
2224: 1
2225: FROM DUAL
2226: WHERE not exists ( select 1 from zx_party_tax_profile
2227: WHERE party_id = l_org_id and Party_Type_Code = 'OU'));
2228:
2229: ELSE
2230:
2228:
2229: ELSE
2230:
2231: INSERT INTO
2232: ZX_PARTY_TAX_PROFILE(
2233: Party_Tax_Profile_Id,
2234: Party_Id,
2235: Party_Type_code,
2236: Customer_Flag,
2257: Last_Update_Date,
2258: Last_Update_Login,
2259: OBJECT_VERSION_NUMBER)
2260: (SELECT
2261: ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2262: ,Organization_id -- Party_Id
2263: ,'OU' -- Party_Type_code
2264: ,'N' -- Customer Flag
2265: ,'N' -- First_Party_Le_Flag
2287: 1
2288: FROM HR_OPERATING_UNITS
2289: WHERE HR_OPERATING_UNITS.ORGANIZATION_ID =
2290: nvl(p_party_ID,HR_OPERATING_UNITS.ORGANIZATION_ID) and
2291: not exists ( select 1 from zx_party_tax_profile
2292: WHERE party_id = organization_id and Party_Type_Code = 'OU'));
2293:
2294: ------Bugfix 4308003----------
2295: REG_REP_DRIVER_PROC_OU('OU');
2699: PROGRAM_ID)
2700:
2701: Select
2702: HZ_CODE_ASSIGNMENTS_S.nextval,
2703: 'ZX_PARTY_TAX_PROFILE',
2704: PTP.PARTY_TAX_PROFILE_ID party_tax_profile_id,
2705: 'VENDOR TYPE',
2706: POV.VENDOR_TYPE_LOOKUP_CODE fiscal_classification_code,
2707: 'N',
2727: NULL,
2728: fnd_global.PROG_APPL_ID,
2729: fnd_global.CONC_PROGRAM_ID
2730: FROM ap_suppliers POV ,
2731: ZX_PARTY_TAX_PROFILE PTP
2732: WHERE POV.PARTY_ID = PTP.PARTY_ID
2733: AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
2734: AND POV.VENDOR_TYPE_LOOKUP_CODE is not null;
2735: */
2781:
2782:
2783: arp_util_tax.debug(' Party_Assoc_Extract .. (+) ' );
2784:
2785: IF p_party_source in ('ZX_PARTY_TAX_PROFILE' , 'PO_VENDOR' ,'PO_VENDOR_SITES') THEN
2786: l_table_owner := 'ZX_PARTY_TAX_PROFILE';
2787: ELSIF p_party_source = 'HR_ORGANIZATIONS' or p_party_source = 'AP_REPORTING_ENTITIES' THEN
2788: l_table_owner := 'HZ_PARTIES';
2789: END IF;
2782:
2783: arp_util_tax.debug(' Party_Assoc_Extract .. (+) ' );
2784:
2785: IF p_party_source in ('ZX_PARTY_TAX_PROFILE' , 'PO_VENDOR' ,'PO_VENDOR_SITES') THEN
2786: l_table_owner := 'ZX_PARTY_TAX_PROFILE';
2787: ELSIF p_party_source = 'HR_ORGANIZATIONS' or p_party_source = 'AP_REPORTING_ENTITIES' THEN
2788: l_table_owner := 'HZ_PARTIES';
2789: END IF;
2790: