[Home] [Help]
127: p_source_type IN VARCHAR2
128: )
129: IS
130: SELECT to_number(a.org_information2) legal_entity_id
131: FROM hr_organization_information a, gl_plcy_mst b
132: WHERE a.organization_id = b.org_id
133: AND b.co_code = p_co_code
134: AND p_source_type = 'O'
135: and a.org_information_context = 'Operating Unit Information'
134: AND p_source_type = 'O'
135: and a.org_information_context = 'Operating Unit Information'
136: UNION
137: SELECT a.legal_entity_id
138: FROM gl_plcy_mst a
139: WHERE a.co_code = p_co_code
140: AND p_source_type = 'N';
141:
142: BEGIN
1160: p_legal_entity_id IN NUMBER
1161: )
1162: IS
1163: SELECT rctta.name
1164: FROM ra_cust_trx_types_all rctta, ar_lookups al, gl_plcy_mst plcy
1165: WHERE sysdate between nvl(rctta.start_date, sysdate-1) and nvl(rctta.end_date, sysdate+1)
1166: AND al.lookup_type = 'INV/CM'
1167: AND al.lookup_code = rctta.type
1168: AND rctta.org_id = plcy.org_id
1529: p.length,
1530: p.segment_no segment_ref,
1531: pm.segment_delimiter
1532: FROM gl_plcy_seg p,
1533: gl_plcy_mst pm,
1534: fnd_id_flex_segments f,
1535: gl_sets_of_books s
1536: WHERE p.co_code = p_co_code
1537: AND p.delete_mark = 0
1550: glsob.chart_of_accounts_id
1551: FROM gl_sets_of_books glsob,
1552: fnd_id_flex_structures fifstr,
1553: fnd_application fa,
1554: gl_plcy_mst gpm
1555: WHERE glsob.chart_of_accounts_id = fifstr.id_flex_num
1556: AND fifstr.id_flex_code = 'GL#'
1557: AND fifstr.application_id = fa.application_id
1558: AND fa.application_short_name = 'SQLGL'
1734: * Migrate_Fiscal_policies_LE *
1735: * *
1736: * DESCRIPTION: *
1737: * This PL/SQL procedure is used to migrate the Fiscal Policies from *
1738: * GL_PLCY_MST to GMF_FISCAL_POLICIES table. *
1739: * *
1740: * PARAMETERS: *
1741: * P_migration_run_id - id to use to right to migration log *
1742: * x_exception_count - Number of exceptions occurred. *
1787: ****************/
1788:
1789: CURSOR cur_get_fiscal_policies IS
1790: SELECT *
1791: FROM gl_plcy_mst
1792: WHERE NVL(migrated_ind,'~') <> '1'
1793: ORDER BY legal_entity_id NULLS LAST;
1794:
1795: BEGIN
1962: i.attribute30,
1963: i.attribute_category
1964: );
1965:
1966: UPDATE gl_plcy_mst
1967: SET migrated_ind = '1',
1968: legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
1969: last_update_date = SYSDATE
1970: WHERE co_code = i.co_code;
2001: END;
2002:
2003: ELSIF L_legal_entity_id IS NOT NULL AND nvl(l_le_count,0) > 0 THEN
2004:
2005: UPDATE gl_plcy_mst
2006: SET migrated_ind = '1',
2007: legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
2008: last_update_date = SYSDATE
2009: WHERE co_code = i.co_code;
2035: **********************************************/
2036:
2037: SELECT count(*)
2038: INTO x_failure_count
2039: FROM gl_plcy_mst
2040: WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL);
2041:
2042: IF nvl(x_failure_count,0) > 0 THEN
2043:
2859: a.last_update_login,
2860: a.text_code,
2861: a.delete_mark
2862: FROM cm_cldr_hdr_b a,
2863: gl_plcy_mst b,
2864: cm_mthd_mst c
2865: WHERE a.cost_mthd_code IS NOT NULL
2866: AND a.co_code IS NOT NULL
2867: AND b.legal_entity_id IS NOT NULL
2948: g.text_code,
2949: 1
2950: FROM cm_cldr_hdr_b g,
2951: cm_mthd_mst h,
2952: gl_plcy_mst i
2953: WHERE g.co_code IS NOT NULL
2954: AND i.legal_entity_id IS NOT NULL
2955: AND g.cost_mthd_code <> h.cost_mthd_code
2956: AND i.co_code = g.co_code
3088: g.text_code,
3089: 1
3090: FROM cm_cldr_hdr_b g,
3091: cm_mthd_mst h,
3092: gl_plcy_mst i,
3093: sy_orgn_mst j,
3094: cm_rsrc_dtl k
3095: WHERE g.co_code IS NOT NULL
3096: AND j.orgn_code = k.orgn_code
3184: g.text_code,
3185: 1
3186: FROM cm_cldr_hdr_b g,
3187: cm_mthd_mst h,
3188: gl_plcy_mst i,
3189: sy_orgn_mst j,
3190: cm_brdn_dtl k
3191: WHERE g.co_code IS NOT NULL
3192: AND j.orgn_code = k.orgn_code
3280: g.text_code,
3281: 1
3282: FROM cm_cldr_hdr_b g,
3283: cm_mthd_mst h,
3284: gl_plcy_mst i,
3285: sy_orgn_mst j,
3286: cm_cmpt_dtl k,
3287: ic_whse_mst l
3288: WHERE g.co_code IS NOT NULL
3378: g.text_code,
3379: 1
3380: FROM cm_cldr_hdr_b g,
3381: cm_mthd_mst h,
3382: gl_plcy_mst i,
3383: sy_orgn_mst j,
3384: cm_adjs_dtl k,
3385: ic_whse_mst l
3386: WHERE g.co_code IS NOT NULL
3882: x.legal_entity_id
3883: FROM gmf_period_statuses x,
3884: cm_mthd_mst y,
3885: cm_cldr_hdr_b z,
3886: gl_plcy_mst w
3887: WHERE y.cost_mthd_code = a.cost_mthd_code
3888: AND x.cost_type_id = y.cost_type_id
3889: AND x.calendar_code = a.calendar_code
3890: AND x.period_code = a.period_code
4713: ),
4714: a.legal_entity_id
4715: = (
4716: SELECT x.legal_entity_id
4717: FROM gl_plcy_mst x
4718: WHERE x.co_code = a.co_code
4719: )
4720: WHERE (a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
4721: OR (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
4961: ),
4962: a.legal_entity_id
4963: = (
4964: SELECT x.legal_entity_id
4965: FROM gl_plcy_mst x
4966: WHERE x.co_code = a.co_code
4967: ),
4968: (
4969: a.organization_id,
6582: e.short_name,
6583: e.name,
6584: e.ledger_id
6585: FROM gmf_fiscal_policies a,
6586: gl_plcy_mst b,
6587: gmf_legal_entities c,
6588: mtl_parameters d,
6589: gl_ledgers e
6590: WHERE a.legal_entity_id = b.legal_entity_id
9446: SET a.organization_pri = nvl(a.whse_code_pri, a.orgn_code_pri),
9447: a.legal_entity_id
9448: = (
9449: SELECT x.legal_entity_id
9450: FROM gl_plcy_mst x
9451: WHERE x.co_code = a.co_code
9452: )
9453: WHERE ((a.whse_code_pri IS NOT NULL OR a.orgn_code_pri IS NOT null) AND a.organization_pri IS NULL)
9454: OR (a.co_code IS NOT NULL AND a.legal_entity_id IS NULL);
9685: UPDATE cm_cmpt_mtl a
9686: SET a.legal_entity_id
9687: = (
9688: SELECT x.legal_entity_id
9689: FROM gl_plcy_mst x
9690: WHERE x.co_code = a.co_code
9691: )
9692: WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
9693:
9970:
9971: UPDATE gl_aloc_mst a
9972: SET a.legal_entity_id = (
9973: SELECT x.legal_entity_id
9974: FROM gl_plcy_mst x
9975: WHERE x.co_code = a.co_code
9976: )
9977: WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
9978:
10206:
10207: UPDATE gl_evnt_plc a
10208: SET a.legal_entity_id = (
10209: SELECT x.legal_entity_id
10210: FROM gl_plcy_mst x
10211: WHERE x.co_code = a.co_code
10212: ),
10213: a.entity_code = decode(a.trans_source_type, 12, 'PURCHASING', NULL),
10214: a.event_class_code = decode(a.event_type, 110, 'DELIVER', NULL)
10396: a.delete_mark
10397: )
10398: = (
10399: SELECT w.organization_id, z.legal_entity_id, decode(a.delete_mark, 1, 1, decode(nvl(w.inventory_org_ind, 'N'), 'Y', 0, 1))
10400: FROM gl_plcy_mst z, sy_orgn_mst w
10401: WHERE w.orgn_code = a.orgn_code
10402: AND w.co_code = z.co_code
10403: ),
10404: a.source_organization_id = (
10860: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
10861: from gmf_period_statuses gps,
10862: cm_mthd_mst cmm,
10863: cm_cldr_hdr_b cch,
10864: gl_plcy_mst gpm
10865: where gps.calendar_code = cac.calendar_code
10866: and cch.calendar_code = cac.calendar_code
10867: and cch.co_code = gpm.co_code
10868: and gps.legal_entity_id = gpm.legal_entity_id
11054: gps.cost_type_id
11055: FROM gmf_period_statuses gps,
11056: cm_mthd_mst cmm,
11057: cm_cldr_hdr_b cch,
11058: gl_plcy_mst gpm
11059: WHERE gps.calendar_code = crc.calendar_code
11060: AND cch.calendar_code = crc.calendar_code
11061: AND cch.co_code = gpm.co_code
11062: AND gps.legal_entity_id = gpm.legal_entity_id
11364: (
11365: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
11366: from gmf_period_statuses gps,
11367: cm_mthd_mst cmm,
11368: gl_plcy_mst gpm
11369: where gps.calendar_code = ccc.calendar_code
11370: and gpm.co_code = ccc.co_code
11371: and gps.period_code = ccc.period_code
11372: and cmm.cost_mthd_code = ccc.cost_mthd_code
11570: cmm.default_lot_cost_type_id,
11571: gfp.cost_basis
11572: from cm_mthd_mst cmm,
11573: cm_mthd_mst dcmm,
11574: gl_plcy_mst gpm,
11575: gmf_fiscal_policies gfp,
11576: xle_entity_profiles xep
11577: where gpm.co_code = a.co_code
11578: and gfp.legal_entity_id = gpm.legal_entity_id